-
Jan 9th, 2012, 12:17 PM
#1
Thread Starter
Hyperactive Member
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
Last edited by intraman; Jan 9th, 2012 at 12:53 PM.
-
Jan 9th, 2012, 12:25 PM
#2
Hyperactive Member
Re: MYSQL / VB.net
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.
-
Jan 9th, 2012, 12:33 PM
#3
Frenzied Member
Re: MYSQL / VB.net
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
-
Jan 9th, 2012, 01:13 PM
#4
Re: MYSQL / VB.net
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
-
Jan 9th, 2012, 01:21 PM
#5
Thread Starter
Hyperactive Member
Re: MYSQL / VB.net
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.
-
Jan 9th, 2012, 01:26 PM
#6
Re: MYSQL / VB.net
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
-
Jan 14th, 2012, 11:17 AM
#7
Thread Starter
Hyperactive Member
Re: MYSQL / VB.net
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|