Access to mysql database from other computer(VB.net/Mysql)

[shohoku]

i'm using vb.net and mysql as database
and the linking btm them is using the myodbc
now my database setting up in computer A
and i have a login program in computer B
how can i link my login program from com B to comA's database
thanks and sorry for my lousy sentence...
hope u guys can understand what i means

 

[shohoku]

in database have a table name empdetail( Employee_ID, Empname, Epass)
1 user( 1001, Lee, 1111)

		Dim myData As New DataSet
		Dim SQL As String
		Dim myAdapter As OdbcDataAdapter
		Dim conn As OdbcConnection
		Dim acc As Data.DataRow
		Dim a As OdbcCommandBuilder

		conn = New OdbcConnection
		conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
					   "SERVER=localhost;" & _
					   "DATABASE=seas;" & _
					   "UID=root;" & _
					   "PASSWORD=;" & _
					   "OPTION=3;"
		conn.Open()

		Dim myCommand As New OdbcCommand("SELECT COUNT(*) FROM empdetail WHERE Employee_ID = @ID AND Epass = @Password", conn)

		myCommand.Parameters.Add("@ID", OdbcType.VarChar).Value = Me.TextBox1.Text
		myCommand.Parameters.Add("@Password", OdbcType.VarChar).Value = Me.TextBox2.Text

		If CInt(myCommand.ExecuteScalar()) = 0 Then
			MessageBox.Show("Login failed.")
		Else
			MessageBox.Show("Login successful.")
		End If
		conn.Close()

 

[jmcilhinney]

Are your parameters definitely using the correct types and the correct values? Let me know and I'll also do some testing and get back to you.

Also, I would suggest downloading an OleDb provider for MySql. There are several available, some paid-for and some free.

http://dev.mysql.com/downloads/connector/net/1.0.html
http://sourceforge.net/projects/mysqlnet/

 

[shohoku]

Employee_ID int len=4
Empname varchar len=30
Epass varchar len=20

i had install connector.net
but i know only use to login to mysql server
(T T)

 

[jmcilhinney]

There's your problem then. Your @ID parameter is the wrong type. My original suggestion assumed that you were using a user name that was a string. If you're using a numerical ID then you need to change the parameter type to OdbcType.Int and convert the TextBox contents to an Integer before assigning it.

 

[callraheel]

Hey why not you use .NET Connectior of MySQL to do all this task and also when using it change the following in your code
Change your query like the following

SELECT COUNT(*) FROM empdetail WHERE Employee_ID = ?ID AND Epass = ?Password

myCommand.Parameters.Add("?ID", Integer.Parse(Me.TextBox1.Text))
myCommand.Parameters.Add("?Password", Me.TextBox2.Text)

And also take care of data types you are using , i mean the data types of fields in your table.
All should match the data you are assigning to the parameters.

Best of Luck
Cheers!!!!

 

[shohoku]

There's your problem then. Your @ID parameter is the wrong type. My original suggestion assumed that you were using a user name that was a string. If you're using a numerical ID then you need to change the parameter type to OdbcType.Int and convert the TextBox contents to an Integer before assigning it.

is it this is what u means??
still login fail with correct id and password woh...

		Dim myCommand As New OdbcCommand("SELECT COUNT(*) FROM empdetail WHERE Employee_ID = @ID AND Epass = @Password", conn)

		myCommand.Parameters.Add("@ID", OdbcType.Int).Value = CInt(Me.TextBox1.Text)
		myCommand.Parameters.Add("@Password", OdbcType.VarChar).Value = Me.TextBox2.Text

 

[callraheel]

You are still using ODBC.NET to do the task...

Anyways in MySQL .NET Connector the Parameter name should not be @PNAME
it should be like ?PNAME

Try following the links to download connection specified in above posts...
CHEERS!!!

 

[jmcilhinney]

Hmmm... Yes that is what I meant, so I'm not sure why it's not working. I think I'm going to have to do some testing before recommending this method again. It's a marginally less robust method, but try this instead:

Dim myCommand As New OdbcCommand(String.Format("SELECT COUNT(*) FROM empdetail WHERE Employee_ID = {0} AND Epass = '{1}'", Me.TextBox1.Text, Me.TextBox2.Text), conn)

If CInt(myCommand.ExecuteScalar()) = 0 Then
	MessageBox.Show("Login failed.")
Else
	MessageBox.Show("Login successful.")
End If

Note that the second place-holder has single quotes around it, which denote it as a string, while the first does not. Also, you are sure that you're getting the values from the correct TextBoxes, right?

 

[jmcilhinney]

You are still using ODBC.NET to do the task...

Anyways in MySQL .NET Connector the Parameter name should not be @PNAME
it should be like ?PNAME

Try following the links to download connection specified in above posts...
CHEERS!!!

I think you'll find that those parameter prefixes are only conventions. I could be wrong but I believe you can name a parameter whatever you want, without using reserved words or illegal characters of course.

 

[shohoku]

thanks ya....
the below one is work...
but i quite no understand how it work..
keke...
i will try to understand it~~

Hmmm... Yes that is what I meant, so I'm not sure why it's not working. I think I'm going to have to do some testing before recommending this method again. It's a marginally less robust method, but try this instead:

Dim myCommand As New OdbcCommand(String.Format("SELECT COUNT(*) FROM empdetail WHERE Employee_ID = {0} AND Epass = '{1}'", Me.TextBox1.Text, Me.TextBox2.Text), conn)

If CInt(myCommand.ExecuteScalar()) = 0 Then
	MessageBox.Show("Login failed.")
Else
	MessageBox.Show("Login successful.")
End If

Note that the second place-holder has single quotes around it, which denote it as a string, while the first does not. Also, you are sure that you're getting the values from the correct TextBoxes, right?