Results 1 to 7 of 7

Thread: MYSQL / VB.net

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2004
    Location
    Manchester
    Posts
    266

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

  2. #2
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Salt Lake City
    Posts
    313

    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.

  3. #3
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    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

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2004
    Location
    Manchester
    Posts
    266

    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.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2004
    Location
    Manchester
    Posts
    266

    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
  •  



Click Here to Expand Forum to Full Width