how to connect vb 6 with mysql

dzol · #1 (**permalink**) 07-26-06, 05:01 AM

i'm working on a vb project that need to has a database with mysql.
how do i want to connect it.

digioz · #2 (**permalink**) 07-26-06, 01:00 PM

Download MySQL ODBC Driver:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

Here is a sample code:

Code:

Private Sub cmdConnect_Click()
Dim cn As New rdoConnection
Dim qry As New rdoQuery
Dim rs As rdoResultset
 
cn.CursorDriver = rdUseOdbc
cn.Connect = "uid=UserName;pwd=Password;server=localhost;driver={MySQL ODBC 3.51 Driver};database=DatabaseName;dsn=;"

cn.EstablishConnection
 
With qry
	.Name = "queryName"
	.SQL = "select * from tblName"
	.RowsetSize = 1
	Set .ActiveConnection = cn
	Set rs = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
End With
	
 loop through the record set processing the
 records and fields.
Do Until rs.EOF
	With rs
	  ' your code using rs!fieldname
	  rs.MoveNext
	End With
Loop
rs.Close
cn.Close
End Sub

dzol · #3 (**permalink**) 07-28-06, 12:14 AM

thanks for ur help.
this is my interface:

this is the table using mysql:
mysql> select * from a;
+-------------+---------------+-------------------------------------+
| englishword | malayword | explanation |
+-------------+---------------+-------------------------------------+
| mouse | tetikus | benda yang digunakan untuk mengelik |
| keyboard | papan kekunci | benda yang digunakan untuk menaip |
+-------------+---------------+-------------------------------------+

and this far, this is the command:

Option Explicit

Private Sub Command1_Click()

Dim cnMySql As New rdoConnection
Dim rdoQry As New rdoQuery
Dim rdoRS As rdoResultset
' set up remote data connection using the
' MySQL ODBC driver

cnMySql.CursorDriver = rdUseOdbc
cnMySql.Connect = "uid=root;pwd=1234;server=localhost;" & _
"driver={MySQL ODBC 3.51 Driver};database=kamus;dsn='';"
cnMySql.EstablishConnection

With rdoQry
.Name = "malayword"
.SQL = "select * from a"
.RowsetSize = 1
Set .ActiveConnection = cnMySql
Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
End With

Text2.Text = ""
Text3.Text = ""

With rdoRS
Text2.Text = Text2.Text & !malayword
Text3.Text = Text3.Text & !explanation
End With

rdoRS.Close
cnMySql.Close

End Sub

how do i want make if user type as example;
he type mouse in the first textbox, when he click the commandbutton then the output will be tetikus(2nd textbox) and explanation(3rd textbox) as in the table a.

duesi · #4 (**permalink**) 07-28-06, 02:02 AM

At the moment, your code selects all entries from the table, but thats not what you want.

The event handler of your button mus change the query like this:

Code:

sql = "SELECT malayword, explanation FROM a WHERE englishword = '" & Text1.Text & "'"

Maybe you rename your controls, to make it more obvious what tehy mean (Text1 could be txt_english...), also your table could use a different name "a" is not really self-descriptive

Happy Coding!

dzol · #5 (**permalink**) 07-28-06, 03:49 AM

thanks for the code, i'll try it out.

dzol · #6 (**permalink**) 07-30-06, 10:37 PM

i have two error in my coding, please someone tell me
what's wrong.

here the code:

Code:

Option Explicit
Dim TextMessage1 As String
Dim TextMessage2 As String
Dim cnMySql As New rdoConnection
Dim rdoQry As New rdoQuery
Dim rdoRS As rdoResultset

Private Sub ConvertMalay_Click()

'if user not type anything
If EnglishWordTxt.Text = "" Then
TextMessage1 = MsgBox("Please type the word first", vbOKOnly, "Caution")

'if user type a word that's in the database
Else
If EnglishWordTxt.Text = EnglishWordTxt.Text Then

'set up remote data connection using the MySQL ODBC driver
cnMySql.CursorDriver = rdUseOdbc
cnMySql.Connect = "uid=root;pwd=1234;server=localhost;" & _
    "driver={MySQL ODBC 3.51 Driver};database=kamus;dsn='';"
cnMySql.EstablishConnection

With rdoQry
    .Name = "malayword"
    .SQL = "SELECT malayword, explanation FROM a WHERE englishword = '" & EnglishWordTxt.Text & "'"
    .RowsetSize = 1
    Set .ActiveConnection = cnMySql
    Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
End With
    
MalayWordTxt.Text = ""
ExplanationTxt.Text = ""

    With rdoRS
    MalayWordTxt.Text = MalayWordTxt.Text & !malayword
    ExplanationTxt.Text = ExplanationTxt.Text & !explanation
    End With
    
rdoRS.Close
cnMySql.Close

'if user type a word that's not in the database
Else
TextMessage2 = MsgBox("Sorry, your word is not in this dictionary", vbOKOnly, "Caution")
End If
End If
End Sub

Private Sub Quit_Click()
End
End Sub

dzol · #7 (**permalink**) 07-30-06, 10:47 PM

when i type another word that's in the mysql database
error at

Code:

.Name = "malayword"

when i type anyword that's not in the database
error at

Code:

MalayWordTxt.Text = MalayWordTxt.Text & !malayword

duesi · #8 (**permalink**) 07-31-06, 02:46 AM

I think you have 2 Problems.
First a Syntacical one:

Code:

Private Sub ConvertMalay_Click()

'if user not type anything
If EnglishWordTxt.Text = "" Then
	TextMessage1 = MsgBox("Please type the word first", vbOKOnly, "Caution")

	'if user type a word that's in the database
Else If EnglishWordTxt.Text = EnglishWordTxt.Text Then

	'set up remote data connection using the MySQL ODBC driver
	cnMySql.CursorDriver = rdUseOdbc
	cnMySql.Connect = "uid=root;pwd=1234;server=localhost;" & _
    	"driver={MySQL ODBC 3.51 Driver};database=kamus;dsn='';"
	cnMySql.EstablishConnection

	With rdoQry
	    .Name = "malayword"
	    .SQL = "SELECT malayword, explanation FROM a WHERE englishword = '" & EnglishWordTxt.Text & "'"
	    .RowsetSize = 1
	    Set .ActiveConnection = cnMySql
	    Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
	End With
    
	MalayWordTxt.Text = ""
	ExplanationTxt.Text = ""
	
	    With rdoRS
	    MalayWordTxt.Text = MalayWordTxt.Text & !malayword
	    ExplanationTxt.Text = ExplanationTxt.Text & !explanation
	    End With
	    
	rdoRS.Close
	cnMySql.Close

'if user type a word that's not in the database
Else
	TextMessage2 = MsgBox("Sorry, your word is not in this dictionary", vbOKOnly, "Caution")
End If
End If '<<<< To much
End Sub

There is a superfluous "End IF", if I am not mistaken.
notive how I reformateed your code, I find indentation very important to see what goes together.

Your second problem is a logical one: Your If statement does not do what you want it to do.
At the time when the if statement gets evaluated, you do not yet know if the word is in your dictionary or not, because you have not yet looked (that happens INSIDE your if statement).

Therefore, this might come closer:

Code:

Private Sub ConvertMalay_Click()

'if user not type anything
If EnglishWordTxt.Text = "" Then
	TextMessage1 = MsgBox("Please type the word first", vbOKOnly, "Caution")

'Lets see if user type a word that's in the database
Else If EnglishWordTxt.Text = EnglishWordTxt.Text Then

	'set up remote data connection using the MySQL ODBC driver
	cnMySql.CursorDriver = rdUseOdbc
	cnMySql.Connect = "uid=root;pwd=1234;server=localhost;" & _
    	"driver={MySQL ODBC 3.51 Driver};database=kamus;dsn='';"
	cnMySql.EstablishConnection

	With rdoQry
	    .Name = "malayword"
	    .SQL = "SELECT malayword, explanation FROM a WHERE englishword = '" & EnglishWordTxt.Text & "'"
	    .RowsetSize = 1
	    Set .ActiveConnection = cnMySql
	    Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
	End With
    
	MalayWordTxt.Text = ""
	ExplanationTxt.Text = ""
	
	    With rdoRS
	    MalayWordTxt.Text = MalayWordTxt.Text & !malayword
	    ExplanationTxt.Text = ExplanationTxt.Text & !explanation
	    End With
	    
	rdoRS.Close
	cnMySql.Close

	If ExplanationTxt.Text = "" Then
		TextMessage2 = MsgBox("Sorry, your word is not in this dictionary", vbOKOnly, "Caution")
	End If
	
End If
 
End Sub

dzol · #9 (**permalink**) 07-31-06, 10:17 PM

when i use the code that u give, error at

Code:

Else If EnglishWordTxt.Text = EnglishWordTxt.Text Then

when change to this

Code:

Private Sub ConvertMalay_Click()

'if user not type anything
If EnglishWordTxt.Text = "" Then
    TextMessage1 = MsgBox("Please type the word first", vbOKOnly, "Caution")

'Lets see if user type a word that's in the database
Else
If EnglishWordTxt.Text = EnglishWordTxt.Text Then

    'set up remote data connection using the MySQL ODBC driver
    cnMySql.CursorDriver = rdUseOdbc
    cnMySql.Connect = "uid=root;pwd=1234;server=localhost;" & _
        "driver={MySQL ODBC 3.51 Driver};database=kamus;dsn='';"
    cnMySql.EstablishConnection

    With rdoQry
        .Name = "malayword"
        .SQL = "SELECT malayword, explanation FROM a WHERE englishword = '" & EnglishWordTxt.Text & "'"
        .RowsetSize = 1
        Set .ActiveConnection = cnMySql
        Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
    End With
    
    MalayWordTxt.Text = ""
    ExplanationTxt.Text = ""
    
        With rdoRS
        MalayWordTxt.Text = MalayWordTxt.Text & !malayword
        ExplanationTxt.Text = ExplanationTxt.Text & !explanation
        End With
        
    rdoRS.Close
    cnMySql.Close

    If ExplanationTxt.Text = "" Then
        TextMessage2 = MsgBox("Sorry, your word is not in this dictionary", vbOKOnly, "Caution")
    End If
    
End If
End If
End Sub

error at,

Code:

cnMySql.EstablishConnection

can u tell me what's wrong

duesi · #10 (**permalink**) 08-01-06, 02:25 AM

Sorry, you can leave out the line

Code:

If EnglishWordTxt.Text = EnglishWordTxt.Text Then

Because it is no restriction (a variable is always the same as itself, insn't it?)
But what error did you get (I see no syntactical problem).

Happy Coding!