MYSQL / VB.net

Hello All,

I'm fairly new to MYSQL and VB.net and i'm trying to master the concept. I'd appreciate any help that could be provided.

I have developed the following code which should connect to a MYSQL server and then take from it every value in a column called 'Username'. When running, it connects to the database and then disconnects.

What am I doing wrong? I know that the actual connection is okay so it must be something to do with the way in which I am trying to retrieve data. Perhaps I have misunderstood a fundamental concept here?

Code:

---

'initialise appropriate variables
    Dim conn As MySqlConnection
    Dim sqlreader As MySqlDataReader
    Dim sqlcomm As New MySqlCommand("SELECT Username, Password FROM user_data", conn)

Try
            'attempt connection
            conn = New MySqlConnection()
            'set connection string
            conn.ConnectionString = "server=server.com" & ";" _
        & "user id=" & "userid" & ";" _
        & "password=" & "password" & ";" _
        & "database=databasename"
            'attempt to open connection
            conn.Open()
            'notify of successful connection
            MsgBox("Successfully connected to database server.")

            Using sqlreader As MySqlDataReader = sqlcomm.ExecuteReader()
                While sqlreader.Read()
                    'Iterate through usernames
                    MsgBox(sqlreader("Username"))
                End While
            End Using

            'if connection is not successful then catch
        Catch
            'notify of unsuccessful connection
            MsgBox("There was an error connecting to the database server.")
            'close connection
            conn.Close()
        End Try

---

take that freaking password out of the code please =)

i assume you are getting to this point:

MsgBox("Successfully connected to database server.")

what happens if you break on the Using part and step thru it? that will show you where your issues are.

id recommend dumping your results to a datatable vs just iterating them.

also, dont just catch, but catch ex as exception.

put a stop in the while and look at the returned values

this code exposes a real server

you should not do that

change something to make it harder to break into

"server=mdcpinpointresearch.com"

I have left out other values

remember when doing course its always "sales at mr pies shop" or northwind db

be safe

Stay employed

here to help

yes... it's going to connect, run the select, then disconnect... that's actually good behaviour and expected. I'm guessing that the problem is when you then to access it from somewhere else in the system, you can't. ADO.NET works in a disconnected state... the best practice is to connect, do what you need to do, and then get out. In your case, it's hard to tell, but I'd suspect it's a variable scope issue. Your connection variable is only scoped to the block where it is defined... so you can't use it outside of that block.

couple things... you're using conn before you create it...
change this:
Dim conn As MySqlConnection
to this
Dim conn As New MySqlConnection
and remove this:
conn = New MySqlConnection()
(and after I've looked it over a few more times... I think that will take care of the problem for you... the problem is that your command isn't associated with the connection properly)

Also, if you are trying to validate the user to see if they are in the table... change this:
Dim sqlcomm As New MySqlCommand("SELECT Username, Password FROM user_data", conn)
to this:
Dim sqlcomm As New MySqlCommand("SELECT Username, Password FROM user_data where UserName = ?", conn)
and then add a call to .AddWithValue (I'll leave it up to you to look up the exact syntax in MSDN) to pass in the user's name... then you can check to see if they are a valid user or not.

-tg

I appreciate the advice guys. The server is not critical and contains no critical data. The password was going to be changed. It seems that breaking through the loop displays no values at all. It only iterates once despite there being three rows in the user_data table. Frustrating.

did you follow my advice about moving the connection around? you're associating the command with a null connection, then creating the connection afterweards... but the command isn't using the connection... if you make the appropriate changes (ie, New Connection, BEFORE the passing it to the command objecT) then you should be fine.

-tg

Thanks for all of your suggestions. I went along the lines Techgnome suggested and, with a couple of modifications, everything worked rather wonderfully. Many Thanks.