locked
Insert values from textboxs into MySQL Database RRS feed

  • Question

  • I figured it out without useing parameters, but that error message tip did help me figure it out.

    Ok so ive been trying to just insert a string into a MySQL database, heres the code for it its very simple. Its just not working need help to get this to work before i can move on textboxes. I get the cannot connect erroer each time i do it.

        Private Sub Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Add.Click
            Try

                conn = New MySqlConnection("Server=localhost;User Id=root;password=password;database=test")

                Dim SQLCmd As New MySqlCommand
                SQLCmd.CommandType = CommandType.Text
                SQLCmd.CommandText = "INSERT INTO testtable (ID) VALUES ('Hiy')"

                'SQLCmd.Parameters.Add(AAssTB.Text)

                conn.Open()
                SQLCmd.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox("Insert Failed: cannot connect to database.")
            Finally
                conn.Close()
            End Try
        End Sub

    Currently i have this code for my button, i have connected to teh database already to do another thing in my program were all teh records are read off into a listbox. Here im trying to insert the vales of these text boxes into a database but it isnt working, and i do get a failed to connect to DB for some reason, not sure why. Please help

        Private Sub Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Add.Click
            Try


                conn1 = New MySqlConnection("Server=localhost;User Id=root;password=password;database=test")

                Dim SQLCmd1 As New System.Data.SqlClient.SqlCommand
                SQLCmd1.CommandType = CommandType.Text
                SQLCmd1.CommandText = "INSERT INTO test.vbprogram (assignment, class, due_date, type) VALUES (" & AAssTB.Text & ", " & AClassTB.Text & ", " & ADueDateTB.Text & ", " & ATypeTB.Text & ")"

                conn1.Open()
                MsgBox("hi")
                SQLCmd1.ExecuteNonQuery()

                conn1.Close()


            Catch ex As Exception
                MsgBox("Insert Failed: cannot connect to database.")
            End Try
        End Sub



    • Edited by Lemmy Winks Friday, February 10, 2012 3:38 AM
    Thursday, February 9, 2012 9:08 PM

Answers

  • Hi Lemmy.  If you could show us the error text it would be very helpful.  Try changing the insert method's Catch block code to read the error message, like this:

            Catch ex As Exception
                 MsgBox("Insert Failed: cannot connect to database.  " & ex.Message)
     

    Also, you are supplying a parameter to the command of your first example but you are not using the parameter anywhere in the statement.  You should be using the "?" character for parameters in your statement, which is essentially what Armin was trying to suggest by comparing it to OleDb.  See this link for more information:   http://www.devart.com/dotconnect/mysql/docs/Parameters.html

    In your second example you are not wrapping your string values in single quotes - that could lead to an error, usually where it thinks you are trying to reference a column name.  I do not use MySQL so I cannot say for sure.

    • Marked as answer by Lemmy Winks Friday, February 10, 2012 3:37 AM
    Friday, February 10, 2012 1:40 AM

All replies

  • I suggest using the SQLCmd1.Parameters property to exclude some errors in advance. Then try again.

    The exception handling, or let's say the message text, is not correct as connecting may have succeeded and the failure is afterwards, probably at ExecuteNonQuery.


    Armin

    Thursday, February 9, 2012 9:21 PM
  • Not sure how to use the parameters property. Also i have tried to adding a a word to a simple single column table and nothing, which makes me believe that my code is completly wrong.
    • Edited by Lemmy Winks Thursday, February 9, 2012 11:16 PM
    Thursday, February 9, 2012 10:59 PM
  • I don't have MySql but I think it's similar to using OleDb:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx

    Or just google or bing, for example:

    http://www.devart.com/dotconnect/mysql/docs/Parameters.html


    Armin

    Thursday, February 9, 2012 11:17 PM
  • Hi Lemmy.  If you could show us the error text it would be very helpful.  Try changing the insert method's Catch block code to read the error message, like this:

            Catch ex As Exception
                 MsgBox("Insert Failed: cannot connect to database.  " & ex.Message)
     

    Also, you are supplying a parameter to the command of your first example but you are not using the parameter anywhere in the statement.  You should be using the "?" character for parameters in your statement, which is essentially what Armin was trying to suggest by comparing it to OleDb.  See this link for more information:   http://www.devart.com/dotconnect/mysql/docs/Parameters.html

    In your second example you are not wrapping your string values in single quotes - that could lead to an error, usually where it thinks you are trying to reference a column name.  I do not use MySQL so I cannot say for sure.

    • Marked as answer by Lemmy Winks Friday, February 10, 2012 3:37 AM
    Friday, February 10, 2012 1:40 AM