7 replies to this topic

[so1i]

Hi there,

Just trying to get a simple MySql database connection working from VB.NET 2008.

I have MySql running on XAMPP, so it should be possible for me to connect to it using VB.NET right?

It's a very simple "enter username and pass and check details" program, written purely as a simple example with which to incorporate a dBase connection. There is no username/pass and like I said, I'm trying to connect via my localhost.

Here is what I have, and basically, it's just not finding the server. Is the software I have installed (XAMPP + MySql) enough? Or is there something I'm missing?

Imports System.Data.Sql

Imports System.Data.SqlClient


Public Class Form1


    Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click

        Dim myConnection As SqlConnection

        Dim myCommand As SqlCommand

        Dim loginSuccess As Boolean


        myConnection = New SqlConnection("Server=localhost;Database=users;")

        myCommand = New SqlCommand("SELECT password FROM details WHERE user=" & txtUser.Text, myConnection)

        myConnection.Open()


        If txtPass.Text = myCommand.ToString Then

            loginSuccess = True

            lblStatus.Text = "Login Successful"

        Else

            loginSuccess = False

            lblStatus.Text = "Login Failed"

        End If


    End Sub

End Class

Thanks in advance,

so1i :)

[debtboy]

That's close, but you can't use a
MS SQL Server native client to connect to MySQL.

Try this:
Imports System.Data.Odbc

It works pretty much the same way except
you you create an ODBC w/ MySQL driver,
or reference a MySQL DSN you create

[so1i]

Thanks debtboy. :D

That gets me a lot further, however now it is telling me I need to set a default driver. Not quite sure where to go with this.

Here is the updated code with error string below.

Imports System.Data.Odbc


Public Class Form1


    Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click

        Dim myConnection As OdbcConnection

        Dim myCommand As OdbcCommand

        Dim loginSuccess As Boolean


        myConnection = New OdbcConnection("Server=localhost;Database=users;")

        myCommand = New OdbcCommand("SELECT password FROM details WHERE user=" & txtUser.Text, myConnection)

        myConnection.Open()


        If txtPass.Text = myCommand.ToString Then

            loginSuccess = True

            lblStatus.Text = "Login Successful"

        Else

            loginSuccess = False

            lblStatus.Text = "Login Failed"

        End If


    End Sub

End Class

Error:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Sorry if it's a bit basic. But I'm quite new to this, having only connected to and manipulated MySql dBases using PHP/SQL.

Thanks again for your help. :)

[debtboy]

You need to download a MySQL ODBC driver like this:
MySQL :: MySQL Connector/ODBC 5.1

Once you download/install the driver, go into control panel, adminstrative tools
and create a simple ODBC connection selecting the new MySQL driver
to test it.
You only need to call out that ODBC connection in your program or
dynamically create a new one in code with that driver.

[so1i]

Awesome, thanks so much mate. :D

[so1i]

Just one more Question... I'm so close yet so far! ;)

How do I return the 'password' value I've found using sql into a string to then compare with the input string of the user? I assumed it was myCommand.ToString.

[debtboy]

Here is a link for connection strings that would apply:
MySQL Connection String Samples - ConnectionStrings.com

So it's just a matter of plugging the user and password into the string,
Let's try this string:
Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3;


Many ways to do it, but here is the basics to do it in code:

Imports System.Data.Odbc


Dim var_odbc_connection as New OdbcConnection

Dim var_odbc_dataadapter As New OdbcDataAdapter

Dim var_odbc_dataset As New DataSet

Dim var_odbc_selectcommand as New OdbcCommand

Dim var_odbc_insertcommand as New OdbcCommand

Dim var_odbc_updatecommand as New OdbcCommand

Dim var_odbc_deletecommand as New OdbcCommand

Dim var_odbc_commandBuilder As New OdbcCommandBuilder


Dim var_username As String

Dim var_password As String


var_username = TextBox1.Text

var_password = TextBox2.Text


var_connection_string = "Driver={MySQL ODBC 5.1 Driver};" & _

"Server=localhost;" & _

"Database=myDataBase; " & _

"User=" & var_username & "; " & _

"Password=" & var_password & ";Option=3;"



var_odbc_connection.ConnectionString = var_connection_string

var_odbc_selectcommand.CommandType = CommandType.Text

var_odbc_selectcommand.CommandText = "SELECT * FROM YOUR TABLE"

var_odbc_dataadapter.SelectCommand = var_odbc_selectcommand

var_odbc_dataadapter.SelectCommand.Connection = var_odbc_connection

var_odbc_dataadapter.SelectCommand.Connection.Open()

var_odbc_dataset.Clear()

var_odbc_dataadapter.Fill(var_odbc_dataset)

Var_odbc_dataadapter.SelectCommand.Connection.Close()


Dim var_xml as string

var_xml = var_odbc_dataset.GetXml


TextBox3.Text = var_xml

This is out of my head, so there may be a typo or two,
but you get the idea.
In addition the connection string could be broken up
and put back together if you prefer rather than a long
string over multiple lines.

For reference: Inserts, deletes and updates use ExecuteNonQuery

Good Luck

[so1i]

Thanks mate, got it fixed! Really appreciate your quick and thorough responses.