SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySqlParameters In Query

    Hi Guys and Galls

    I'm trying to update a table in mysql database using the following code

    Public Sub InsertUsers(ByVal sender As Object, ByVal e As EventArgs)
    Dim sqlInsert As String
    sqlInsert = "INSERT INTO tblTestComp(strUserName, strPassword, DOB) "
    sqlInsert += "VALUES(?, ?, ?)"

    Dim objCmd As MySqlCommand
    objCmd = New MySqlCommand(sqlInsert, objConnMySQL)
    Dim i As Integer = 0

    objConnMySQL.Open()
    For i = 0 To 2
    objCmd.Parameters.Add(New MySqlParameter())
    Next

    objCmd.Parameters(0).Value = "BillWordsWorth"
    objCmd.Parameters(1).Value = "Kipper88"
    objCmd.Parameters(2).Value = "1985-05-21"

    objCmd.ExecuteNonQuery()
    objConnMySQL.Close()

    End Sub


    The error I get is Object reference not set to an instance of an object
    on the line objCmd.Parameters.Add(New MySqlParameter())

    When I change the connection, command and parameters to oledb objects and run against MS Access databse it works fine. I have been trying to amend it for 2 days now to work with MySQL objects but nothing seems to work

    I would be very grateful for any ideas
    Thanks

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Parameterized query with MySQL

    Hi Guys not sure this post belongs here or in MySQL forum but I'm trying to insert a row into MySQL database table with the insert statement

    sqlInsert = "INSERT INTO tblTestComp(strUserName, strPassword, DOB) "
    sqlInsert += "VALUES(?, ?, ?)"

    Setting the parameters with:
    objCmd.Parameters(0).Value = What Ever
    objCmd.Parameters(1).Value = What Ever
    objCmd.Parameters(2).Value = What Ever

    Using MySql.Data.MySqlClient data client

    Can anyone tell me if this is even possible or not
    Many Thanks

  3. #3
    SitePoint Guru Chroniclemaster1's Avatar
    Join Date
    Jun 2007
    Location
    San Diego, CA
    Posts
    784
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use basically the same technique very happily with a couple modifications. One, make sure the tblTestComp is the exact name of the table in your MySQL database and that strUserName, strPassword, and DOB are the exact column names in that table. Pretty straightforward.

    Also, the VALUES(?, ?, ?) will cause problems because that's specifically a SQL syntax for the Jet engine with powers Access. MySQL takes a syntax like...

    workingSqlModificationText += "VALUES ( @pages_title, @pages_filename, @pages_websiteid, @pages_urlpathid ) "

    Finally, the .Value property of the Parameters collection is something I'd had problems with, though I can't remember exactly what. I found several blogs and forum threads which raised the same issues and recommended AddWithValue instead. I haven't had problems since I switched. I haven't programmed VB in ages, but I think this is basically the changes you need to make.


    Public Sub InsertUsers(string inputUserName, string inputPassword, string inputDOB)
    Dim sqlInsert As String
    sqlInsert = "INSERT INTO tblTestComp(strUserName, strPassword, DOB) "
    sqlInsert += "VALUES(@UserName, @Password, @DOB)"

    Dim objCmd As MySqlCommand
    objCmd = New MySqlCommand(sqlInsert, objConnMySQL)

    objConnMySQL.Open()

    objCmd.Parameters.AddWithValue("@UserName", inputUserName);
    objCmd.Parameters.AddWithValue("@Password", inputPassword);
    objCmd.Parameters.AddWithValue("@DOB", inputDOB);

    objCmd.ExecuteNonQuery()
    objConnMySQL.Close()

    End Sub
    Whatever you can do or dream you can, begin it.
    Boldness has genius, power and magic in it. Begin it now.

    Chroniclemaster1, Founder of Earth Chronicle
    A Growing History of our Planet, by our Planet, for our Planet.

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK thanks Chroniclemaster1
    I knew I could add the parameters by name as you suggested above which I have already done I was just wondering why I couldn't use VALUES(?, ?, ?) as I have done in Access and SQL Server which you have explained as well

    Thanks very much


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •