How to MySQL query with Visual Basic 6

Question

two things:

first: I have googled everywhere, including stackoverflow. Just about questions regarding sql vs vb6 are about connection string. I have this down pat. Any reference to mysql queries are for the query itself - but not in tangent with vb6 or any language

second: I am very proficient in PHP/MySQL so that aspect of help I am not seeking.

what I am stuck on, is how vb6 handles sql queries a little (lot) different than php. So once I get connected, how to I tell vb6 to look up a field. php version

$sql = "SELECT * FROM table field = data where something = that";
$query = mysql_query($sql) or die("bad query: <br>$sql<br>".mysql_error());

then either use a fetch array or work with this.

how is this accomplished in vb6?

I saw some source referring to rdoQry. Can someone shed some light on this with example code? I don ont need the connection part. have that set. my connection is this:

Dim cnMySql As New rdoConnection
cnMySql.CursorDriver = rdUseOdbc
cnMySql.Connect = "uid=root;pwd=root;" _
    & "server=127.0.0.1;" _
    & "driver={MySQL ODBC 3.51 Driver};" _
    & "database=mydatabase;dsn=;"
cnMySql.EstablishConnection

works perfect.

Andy Kershaw · Accepted Answer · 2011-07-30 20:33:49Z

2

ADO is the successor to RDO. I use code similar to this to query MySQL from Visual Basic using ADO.

Dim conn As New ADODB.Connection
conn.Open "connection string"

Dim cmd As New ADODB.Command
With cmd
    .ActiveConnection = conConnection
    .CommandText = "SELECT fields FROM table WHERE condition = ?"
    .CommandType = adCmdText
End With

Dim param As New ADODB.Parameter
Set param = cmd.CreateParameter("condition", adVarChar, adParamInput, 5, "value")
cmd.Parameters.Append p

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockOptimistic

Dim temp
Do While Not rs.EOF
    temp = rs("field")
    rs.MoveNext
Loop

rs.Close
conn.Close

answered Jul 30, 2011 at 20:33

Andy Kershaw

1817 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cain Over a year ago

wow, so vb6 is a little "code heavy" for MySQL projects LOL. OK I think i can make this work - and updating is the same correct, just change the commandtext (in this example)?

Andy Kershaw Over a year ago

Agreed - it does look code-heavy. It is using parameters rather than just building the SQL on the fly. To execute a query without returning data, use cmd.Execute rowCount, , (adCmdText Or adExecuteNoRecords) where rowCount is an integer which will contain the number of rows the query affected.

MarkJ Over a year ago

Building parameters on the fly protects you against SQL injection (accidental or deliberate) so it has some advantages over that PHP in the question.

Bob77 Over a year ago

The funny part is that PHP is arguably more at risk from SQL injection because it is typically publicly exposed. If anything that PHP should be reworked to use parameter queries.

Spudley · Accepted Answer · 2011-07-30 20:39:14Z

Typically, with VB6 (gosh, are people still using this??) you would connect to a database using ADO. ADO is a common database class which allows you to use the same syntax for any database.

The connection code you've provided is using RDO, which is a predecessor to ADO (and since VB6/ADO is pretty old now, that means RDO is historic). For your purposes, the two should work fairly similarly, but I'd suggest switching to ADO now if you have a chance, before you've written too much code.

This thread seems to be pointing someone else in the right direction in writing the connection code: http://www.vbforums.com/showthread.php?t=654819

Once you've got a connection, you need to run your queries. The process for this should make sense if you're used to querying from PHP; it's basically the same process, although you typically need to mess around with configuring a few more options than with PHP. It would look something like this:

Set rs = New ADODB.Recordset
rs.ActiveConnection = adoconn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open "SELECT blah blah blah"
While Not rs.EOF
    Text1.Text = rs("Name")
    rs.MoveNext
Wend
rs.Close

Hope that helps.

Collectives™ on Stack Overflow

How to MySQL query with Visual Basic 6

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related