[RESOLVED] mySQL INSERT Command

[mjenkinson05]

Ok.

Im trying to insert a row of data into a table in a mySQL Database.

I finally got the syntax right, but now when I try and run it i get the error:

Error: Unknwon Column textID.text in field list

I am using the following code:

Try
            cmd = New MySqlCommand
            cmd.CommandText = "INSERT into STAFF (ID, Name, Address1, Address2, Address3, Post_Code, DOB, Phone1, Phone2, CRB) VALUES (txtID.text, txtName.text, txtAdd1.text, txtAdd2.text, txtAdd3.txt, txtPostCode.text, txtDOB.text, txtPhone1.text, txtPhone2.txt, CRB)"
            cmd.CommandType = CommandType.Text
            cmd.Connection = cn
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Step 4 - Connect and Insert
        cn = New MySqlConnection()
        cn.ConnectionString = "server=" & My.Settings.HostIP & ";" & "user id=" & My.Settings.User & ";" & "password=" & My.Settings.Password & ";" & "database=platpos"
        Try
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Successfully Added")
        Catch ex As Exception

            MsgBox(ex.Message)
        End Try

I did find a solution, that uses $ signs, which does run successfully, but instead of inserting the text in each of the text boxes, it actually rights txtID.text in the ID cell. Close, but not quite.


Many thanks in advance!


For those that want, the $ solution is this:

Try
            cmd = New MySqlCommand
            cmd.CommandText = "INSERT into STAFF (ID, Name, Address1, Address2, Address3, Post_Code, DOB, Phone1, Phone2, CRB) VALUES ('$txtID.text', '$txtName.text', '$txtAdd1.text', '$txtAdd2.text', '$txtAdd3.txt', '$txtPostCode.text', '$txtDOB.text', '$txtPhone1.text', '$txtPhone2.txt', '$CRB')"
            cmd.CommandType = CommandType.Text
            cmd.Connection = cn
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Step 4 - Connect and Insert
        cn = New MySqlConnection()
        cn.ConnectionString = "server=" & My.Settings.HostIP & ";" & "user id=" & My.Settings.User & ";" & "password=" & My.Settings.Password & ";" & "database=platpos"
        Try
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Successfully Added")
        Catch ex As Exception

            MsgBox(ex.Message)
        End Try

[keystone_paul]

Your current code doesn't know that text1.text is a control on your form as it is enclosed within the string.

You can either break the reference out, ie

cmd.CommandText = "INSERT into STAFF (ID, Name, Address1, Address2, Address3, Post_Code, DOB, Phone1, Phone2, CRB) VALUES (" & txtID.text & ", " & txtName.text & ", " & txtAdd1.text & ", " & txtAdd2.text & ", " & txtAdd3.txt & ", " &  txtPostCode.text & ", " & txtDOB.text & ", " & txtPhone1.text & ", " & txtPhone2.txt & ", " & CRB)"

Or better, use parameters in your query. I don't use MySQL so you'll have to check the exact syntax but you would basically replace txtAdd1 etc with placeholders and then use cmd.Parameters.AddWithValue("@Param1", txtAdd1.text) etc

[mjenkinson05]

Parameters, how do you mean?

[techgnome]

This is what he's talking about:
http://www.vbforums.com/showthread.php?t=548787

-tg

[keystone_paul]

Here is an example using OLEDB, you may need to change it for MySQL as I'm not sure whether it uses a different special character to denote parameters, but this should point you in the right direction :

Private Sub DisplayPersonData(ByVal first_name As String, _
    ByVal last_name As String)
    ' Open the connection.
    connUsers.Open()

    ' Make a Command for this connection
    ' and this transaction.
    Dim cmd As New OleDb.OleDbCommand( _
        "SELECT * FROM People WHERE FirstName=? AND " & _
            "LastName=?", _
        connUsers)

    ' Create parameters for the query.
    cmd.Parameters.Add(New _
        OleDb.OleDbParameter("FirstName", first_name))
    cmd.Parameters.Add(New OleDb.OleDbParameter("LastName", _
        last_name))

    ' Execute the query.
    Dim db_reader As OleDbDataReader = _
        cmd.ExecuteReader(CommandBehavior.SingleRow)

    ' Display the results.
    If db_reader.HasRows Then
        db_reader.Read()
        txtFirstName.Text = _
            db_reader.Item("FirstName").ToString
        txtLastName.Text = _
            db_reader.Item("LastName").ToString
        txtStreet.Text = db_reader.Item("Street").ToString
        txtCity.Text = db_reader.Item("City").ToString
        txtState.Text = db_reader.Item("State").ToString
        txtZip.Text = db_reader.Item("Zip").ToString
    Else
        For Each ctl As Control In Me.Controls
            If TypeOf ctl Is TextBox Then ctl.Text = ""
        Next ctl
    End If

    ' Close the connection.
    connUsers.Close()
End Sub

[EDIT] - Thanks techgnome - saves me from linking to that thread which was my next task!

[mjenkinson05]

Guys ure responces were great, except i now get "There was an error in your SQL syntax".

This is really starting to hurt my head now.

The command im using is:

SID = txtID.Text
        SNAME = txtName.Text
        SAdd1 = txtAdd1.Text
        SAdd2 = txtAdd2.Text
        Sadd3 = txtAdd3.Text
        SPostcode = txtPostcode.Text
        SDOB = txtDOB.Text
        SPhone1 = txtPhone1.Text
        SPhone2 = txtPhone2.Text

Try
            cmd = New MySqlCommand

            cmd.CommandText = "INSERT into STAFF (ID, Name, Address1, Address2, Address3, Postcode, DOB, Phone1, Phone2, CRB) VALUES (" & SID & ", " & SNAME & ", " & SAdd1 & ", " & SAdd2 & ", " & Sadd3 & ", " & SPostcode & ", " & SDOB & ", " & SPhone1 & ", " & SPhone2 & ", " & SCRB & ")"
            cmd.CommandType = CommandType.Text
            cmd.Connection = cn
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        'Step 4 - Connect and Insert
        cn = New MySqlConnection()
        cn.ConnectionString = "server=" & My.Settings.HostIP & ";" & "user id=" & My.Settings.User & ";" & "password=" & My.Settings.Password & ";" & "database=platpos"
        Try
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Successfully Added")
        Catch ex As Exception

            MsgBox(ex.Message)
        End Try

The column names are EXACTLY the same. even down to the spelling (just incase) and i just cant see what its problem is.

Thanks In Advance

[Campion]

This is how I would put it using parameters:

Dim cmd as new mySQLCommand( "INSERT into STAFF (Name, Address1, " & _
"Address2, Address3, Postcode, DOB, Phone1, Phone2, CRB) VALUES ( " & _
"?Name, ?Add1, ?Add2, ?Add3, ?Post, ?DOB, ?P1, ?P2)", cn)

With cmd.parameters
     .AddWithValue("?Name", txtName.Text)
     .AddWithValue("?Add1", txtAdd1.Text)
     .AddWithValue("?Add2", txtAdd2.Text)
     .AddWithValue("?Add3", txtAdd3.Text)
     .AddWithValue("?Post", txtPostcode.Text)
     .AddWithValue("?DOB", txtDOB.Text)
     .AddWithValue("?P1", txtPhone1.Text)
     .AddWithValue("?P2", txtPhone2.Text)

End With

cmd.Prepare

cmd.ExecutenonQuery

[keystone_paul]

The reason for the error is that you need to enclose string values in quotes.

Sorry thats probably my fault as I didn't look closely at the data types in your example.

The better way of doing it is to use parameters though like Campion's example, for the reasons given in techgnome's link above.

[mjenkinson05]

Guys thats great, it works

Thanks so much