Connecting to mysql using VBExpress

[cincture]

I'm using Visual Basic 2008 Express. I want to make a small database editor, but I can't seem to gain access to the tables in my sql server. I seem to be connecting to the server ok, but I can't figure out how to access the tables so that I can edit the info there.

So far I have two forms, and a module where my global variables are declared.

Form1 is the login screen:

Imports MySql.Data.MySqlClient

Public Class Form1

    Public Function GetConnectionInfo() As String
        HostName = txtHost.Text
        UserName = txtUsername.Text
        PasswordName = txtPassword.Text
        ConnectionName = TxtWorld.Text
        Return ("Data Source=" + HostName + ";uid=" + UserName + ";pwd=" + PasswordName + ";database=" + ConnectionName + ";")
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Using conn As New MySqlConnection(GetConnectionInfo())
                conn.Open()

                If conn IsNot Nothing Then
                    If conn.State = ConnectionState.Open Then
                        conn.Close()
                    End If
                End If
            End Using
            'MessageBox.Show("Your credentials have been verified, transferring now!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

        Catch sqlerror As MySqlException
            MessageBox.Show(sqlerror.Message, "An error occured", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
            'Throw new Exception("An error occured while logging into the SQL server.", sqlerror); 
        End Try

        FrmMain.Show()
        Me.Hide()

    End Sub

End Class

FrmMain is where the editing will take place:

Imports DAO

Public Class FrmMain

    Private Sub FrmMain_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Form1.Close()
    End Sub

    Private Sub FrmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ws As Workspace
        Dim db As Database
        Dim rs As Recordset
        Dim strConnection As String

        strConnection = "ODBC;DSN=" & ConnectionName & ";UID=" & UserName & ";PWD=" & PasswordName
        ws = DBEngine.Workspaces(0)
        db = ws.OpenDatabase("", False, False, strConnection)
        rs = db.OpenRecordset("creature_loot_template")

    End Sub

End Class

The line that says "ws = DBEngine.Workspaces(0)" is giving me an error message that says "Reference to a non-shared member requires an object reference."

Does anyone know how I can either fix this error, or can I connect to my sql server in a completely different (easier) way?

[cincture]

Can someone help me connect to my sql server using either VB2008Express or VB6? I've been searching google and I haven't found anything that works.

[ReidE96]

Imports System.Data.SqlClient

Private Function SqlQuery(ByVal Server As String, ByVal Database As String, ByVal Query As String, Optional ByVal Username As String = "", Optional ByVal Password As String = "") As SqlDataReader
Dim Connection As New SqlConnection
Dim Command As New SqlCommand
Dim Reader As SqlDataReader
Connection.ConnectionString = "server=tcp:" & Server & ";User ID=" & Username & ";Pwd=" & Password & ";Database=" & Database & ";"
Command.Connection = Connection
Command.CommandText = Query
Try
Connection.Open()
Command.Prepare()
Reader = Command.ExecuteReader()
Catch ex As Exception
MsgBox("An error occured whilst working with the server. Please submit the following error message to help with debugging." & Environment.NewLine & ex.Message, , "Local Error")
Finally
Connection.Close()
End Try
Return Reader
End Function

If you're only making the occasional query, where leaving the connection open would be a waste of resources, that will do you just fine. Call the function where you need data from the SQL server (usage below). If you're planning to connect and send multiple queries, you should be able to figure it out from that.

Dim MyReader as SqlDataReader
MyReader = SqlQuery("website.com, port", "database", "query", "username", "password")

Edit: Coded in Visual Studio 2008 (VB.NET)

[cincture]

Thank you for responding to my post. I really appreciate the help.

I am now trying this code:

Imports System.Data.SqlClient

Public Class Form1

    Dim Server As String
    Dim Username As String
    Dim Password As String
    Dim Database As String

    Private Function SqlQuery(ByVal Server As String, ByVal Database As String, ByVal Query As String, Optional ByVal Username As String = "", Optional ByVal Password As String = "") As SqlDataReader
        Dim Connection As New SqlConnection
        Dim Command As New SqlCommand
        Dim Reader As SqlDataReader

        Connection.ConnectionString = "server=tcp:" & Server & ";User ID=" & Username & ";Pwd=" & Password & ";Database=" & Database & ";"
        Command.Connection = Connection
        Command.CommandText = Query

        Try
            Connection.Open()
            Command.Prepare()
            Reader = Command.ExecuteReader()
        Catch ex As Exception
            MsgBox("An error occured whilst working with the server. Please submit the following error message to help with debugging." & Environment.NewLine & ex.Message, , "Local Error")
        Finally
            Connection.Close()
        End Try
        Return Reader
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyReader As SqlDataReader
        'MyReader = SqlQuery("website.com, port", "database", "query", "username", "password")
        MyReader = SqlQuery("localhost, 3306", "mangos", "SELECT * FROM creature_loot_template WHERE entry < 100", "root", "root")
    End Sub
End Class

I am getting an error message that is coming from the line Command.Open() that says that it is returning a negative number (which I assume means that the connection failed to open). Is there something wrong with my connection string?

I also turned off my firewall temporarily to make sure that it wasn't what was causing the connection to not open.

[ReidE96]

I think the database name is case sensitive (and isn't the table name, though I don't think you made that mistake). So if your database is called MangOS, using mangos in the connection string MAY not work.

[cincture]

Unfortunately, using the proper capitals didn't seem to help

I'm using a mysql.bat connection that came with a mangos repack, and I suppose that could have something to do with it. Although I was certain that I should be able to connect to it with VB, since I can connect to it with Navicat, Glitchy, Quice, and the Mangos Database Manager. Maybe something went wrong when I was installing the connectors and whatever else. You've given me some great direction, though, and I really appreciate it. I'm going to double check my connectors and see what I can find out.

[ReidE96]

You're most welcome. I hope you get it working. Feel free to ask any questions you have.

[Apoc]

Lol. Just FYI; the SqlClient namespace is for MSSQL. NOT MySQL.

Go grab the MySQL ADO.NET connector library.

[ReidE96]

lol. Just fyi; the sqlclient namespace is for mssql. Not mysql.

apoc is a freaking god of .net!

[cincture]

Just wanted to let you all know that I finally got connected! I followed this tutorial: VBMySQL.com » The VB.NET-MySQL Tutorial - Part 3

I either had the wrong connectors, or I wasn't using them properly. Whichever it was, it was working after I worked through that tutorial.

Thank you all again!

[ReidE96]

Glad you've got it working, cincture