How to connect to MySQL database from Visual Basic

问题:

I am using visual basic 6. I have a button created which when pressed should display all the entries of the table. I am using following code to connect to MySQL database. I have used the Microsoft Remote Data Services as my reference

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 am not able to connect to the database. How do I connect?

回答1:

Can you try it using ADO instead of RDO?

• Add a reference to the Microsoft ActiveX Data Objects 2.8 Library
• Set up an ODBC DSN 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

回答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