How to connect to MySQL database from Visual Basic 6

Question

I use visual basic 6. I have a button that, when clicked, should display all the records in the table. I am using the following code to connect to a MySQL database. I used Microsoft Remote Data Services as my link

code:

Private Sub cmdConnectMySQL_Click() Dim cnMySql As New rdoConnection Dim rdoQry As New rdoQuery Dim rdoRS As rdoResultset cnMySql.CursorDriver = rdUseOdbc cnMySql.Connect = "uid=root;pwd=; server=localhost; driver={MySQL ODBC 3.51 Driver}; database=demo;dsn=;" cnMySql.EstablishConnection With rdoQry .Name = "selectUsers" .SQL = "select * from user" .RowsetSize = 1 Set .ActiveConnection = cnMySql Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer) End With Do Until rdoRS.EOF With rdoRS rdoRS.MoveNext End With Loop rdoRS.Close cnMySql.Close End Sub 

I can not connect to the database. How to connect?

Answer 1

Can you try using ADO instead of RDO?

• Add link to Microsoft ActiveX Data Objects 2.8 Library
• Configure DSN ODBC to connect to the database

Then use code something like this

 Dim cnConnection As ADODB.Connection Dim adorsRecordSet As ADODB.Recordset Dim sDatabase As String Dim sSQL As String sDatabase = "NameOfTheMysqlDSN" sSQL= "Select * From user" Set cnConnection = New ADODB.Connection cnConnection.Open sDatabase Set adorsRecordSet = New ADODB.Recordset adorsRecordSet.Open sSQL, cnConnection Do Until (adorsRecordSet.EOF) adorsRecordSet.MoveNext Loop 

Answer 2

 ' the follwoing code inside module and use adodc Public Myconn As New ADODB.Connection Public Recset As New ADODB.Recordset Public SqlStr As String Public Function Connectdb() Set Myconn = New ADODB.Connection Set Recset = New ADODB.Recordset Myconn.Open "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties='DRIVER=SQL Server Native Client 10.0;SERVER=.\sqlexpress;Trusted_Connection=Yes;APP=Visual Basic;WSID=YOUNGPROGRAMA;DATABASE=StdB;';Initial Catalog=StdB" End Function ' the following code inside ur form Private Sub Command1_Click() Connectdb SqlStr = "Insert into Logintb values('" + Text1.Text + "', '" + Text2.Text + "' )" Recset.Open SqlStr, Myconn, adOpenKeyset, adLockOptimistic MsgBox "New User Added" Myconn.Close End Sub Private Sub Form_Load() Connectdb With Form1 .Top = (Screen.Height - .Height) / 2 .Left = (Screen.Width - .Width) / 2 End With End Sub 'it works 'for verification call Mr. Raji on 08067455933