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.

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.

[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.