VB6 --> mySQL connection

[parte23]

HI all,
For some reason it's very hard to find over the web a simple example for a connection to mySQL from VB6. There are lots of links to places, parts of code, .dlls etc ...

Does anyone know of a full code to connect to mySQL db, get some lines from a table, and show them on screen ?

Thx

[vb5prgrmr]

See...

http://www.connectionstrings.com

Good Luck

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

The link above is important, but will get you one part of the puzzle.

To actually make use of it you will need code to go with it. You can find that in the "Classic VB: ADO" section of our Database Development FAQs/Tutorials (at the top of this forum).

I recommend starting with the "ADO Tutorial", which explains the code needed (as well as providing the link that vb5prgrmr gave, and explaining how to use it), and gives you a full program to interact with a database.

[Fraps]

You need to put this on the beginning of the app

Dim db_name, db_server, db_port, db_user, db_pass, constr As String

And implement to the VB code couple of "things" like that:

Private Sub ConnServer()
  'connect to MySQL server using MySQL ODBC 3.51 Driver
  Set conn = New ADODB.Connection
  conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                        & "SERVER=localhost;" _
                        & " DATABASE=PERSONAL;" _
                        & "UID=root;PWD=root; OPTION=3"
conn.Open
End Sub

Private Sub OpenServer() 'Connect MySQL Server Without ODBC setup
    constr = "Provider=MSDASQL.1;Password=;Persist Security Info=True;User ID=;Extended Properties=" & Chr$(34) & "DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=" & db_name & ";SERVER=" & db_server & ";UID=" & db_user & ";PASSWORD=" & db_pass & ";PORT=" & db_port & ";OPTION=16387;STMT=;" & Chr$(34)
    Set conn = New ADODB.Connection
    conn.Open constr
End Sub

Public Sub OpenConnection1()

    On Error GoTo DBerror
    db_name = "personal"
    db_server = "localhost"
    db_port = "3306"    'default port is 3306
    db_user = "root"
    db_pass = "root"
    'ConnServer ' Open with ODBC in Control Panel
    OpenServer ' Open without ODBC in Control Panel
    Rx = 0
    ShowData
    ShowGrid
    Exit Sub
DBerror:

    ShowData
    ShowGrid

End Sub

And of course showdata an similar definitions.

[kxcntry99]

Take a look at beacon's ado tutorial...it was worth its weight in gold when I started with vb6 and mysql

[Hack]

Avoid using code like this

Dim db_name, db_server, db_port, db_user, db_pass, constr As String

In VB6, each variable MUST be explicity declared or it will be created as a Variant. In the above, only constr is actually a string. To be accurate, the line would have to be

Dim db_name As String, db_server As String, db_port As String, db_user As String, db_pass As String, constr As String

[parte23]

thx guys.
So with the ADO, i tried to connect to my sql server (5.1), after configuring a DSN for it, using ODBC 3.51 driver.

with this code -
With rs

.Open sqlStr, conn, adOpenKeyset, adLockPessimistic, adCmdTable

I got the famous, not really solved on Google, error -
odbc driver does not respond to requested properties

any ideas ?

[kxcntry99]

what does your connection string look like???

[parte23]

Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydatabase; User=mydatabase;Password=mydatabase;Option=3;

im connecting to mysql.
anyways, when i removed the 'adCmdTable' from the end of -
.Open sqlStr, conn, adOpenKeyset, adLockPessimistic, adCmdTable
so it all started working ...

[kxcntry99]

Glad to hear you got it working.

if you are passing a full sql statment to the connection you might need to replace adCmdTable with adCmdText.

If this issue is closed please mark the thread resolved

[techgnome]

adCmdTable should only be used when the "sql" string is jsut a table name. ex: tblCustomers
adCmdText should be used when the sql string is an actual statement. ex: SELECT * FROM tblCustomers
adCmdStoredProcedure should be used when the sql string is a sproc name. ex: prc_Customers_Get

-tg