INSERT statement

[mrbungle]

Quick question...

Say I have a login form with a user ID, then a text box on the main form.

When the main form closes, it takes that value from the text box and inserts it into the column based on that users ID

Here's my code-

'// Save mileage to DB
                MySqlConnection.ConnectionString = x.urlMySQLDatabase
                Try
                    MySqlConnection.Open()
                Catch ex As Exception
                    MsgBox("It looks like your internet is taking a second to get squared away... ")
                End Try
                MySqlCommand.Connection = MySqlConnection
                MySqlDataAdapter = New MySqlDataAdapter _
                ("INSERT INTO Users.id =  " & lblID.Text & " mileage VALUES ?" & txtEndMiles.Text & ";", MySqlConnection)
                MySqlCommand.Parameters.AddWithValue("?mileage", txtEndMiles.Text)

                Try
                    MySqlCommand.ExecuteNonQuery()
                Catch ex As Exception
                    ' no error message yet- pending
                Finally
                    MySqlConnection.Close()
                End Try
                '// End save mileage to DB

So I guess my question is, can some take a look at my INSERT statement for me?

Each row in the database has an id column and mileage column.

lblID.Text is the users ID number, it shows on the main form, so I figured I could use that value,
txtEndMiles.Text is the text box on the main form.
mileage is the column in the database to store that value.

("INSERT INTO Users.id =  " & lblID.Text & " mileage VALUES ?" & txtEndMiles.Text & ";", MySqlConnection)
                MySqlCommand.Parameters.AddWithValue("?mileage", txtEndMiles.Text)

[Mitja Bonca]

INSERT statement should go like:
INSERT INTO DataBaseTable VALUES (value1, value2, ...)

So, yours statement is incorrect.

[adam_k]

The insert statement should be like:

"INSERT INTO table 
(userID, mileage)
values 
('" &lblID.Text &"','"& txtEndMiles.Text &"')"

You didn't provide the table name and I'm guessing you have to change the userID to whatever you've named it.

[mrbungle]

Thanks for the reply Adam- my data isn't saving so let me use a similar example- it looks right to my uneducated eye. The table is Users.

MySqlCommand.Connection = MySqlConnection
        MySqlDataAdapter = New MySqlDataAdapter _
        ("INSERT INTO Users (officer_id, area, supervisor) VALUES ('" & frmOptions.txtAddOfficer.Text & "','" & frmOptions.txtAddArea.Text & ",'" & frmOptions.txtAddSupervisor.Text & "')", MySqlConnection)

        MySqlCommand.Parameters.AddWithValue("officer_id", frmOptions.txtAddOfficer.Text)
        MySqlCommand.Parameters.AddWithValue("area", frmOptions.txtAddArea.Text)
        MySqlCommand.Parameters.AddWithValue("supervisor", frmOptions.txtAddSupervisor.Text)
        MsgBox("Information Saved. ")
        Try
            MySqlCommand.ExecuteNonQuery()

Are these commands ultra finicky- like even if there's a space in there somewhere it won't work? When I run the app, it's error and warning free.

[mrbungle]

Crap, I just realized something- I'm using the WRONG COMMAND! I need an UPDATE statement... :( There is already a value in the column, so I need to overright it.

[mrbungle]

Anyone see any problems here?

MySqlCommand.Connection = MySqlConnection
        MySqlDataAdapter = New MySqlDataAdapter _
        ("UPDATE INTO Users SET (officer_id, area, supervisor) VALUES ('" & frmOptions.txtAddID.Text & "','" & frmOptions.txtAddArea.Text & ",'" & frmOptions.txtAddSupervisor.Text & "')", MySqlConnection)


        MySqlCommand.Parameters.AddWithValue("officer_id", frmOptions.txtAddID.Text)
        MySqlCommand.Parameters.AddWithValue("area", frmOptions.txtAddArea.Text)
        MySqlCommand.Parameters.AddWithValue("supervisor", frmOptions.txtAddSupervisor.Text)
        'MsgBox("Information Saved. ")
        Try
            MySqlCommand.ExecuteNonQuery()

[Mitja Bonca]

This is getting pointeless mate!
For UPDATE statement you do:
UPDATE tableName SET Field1 = @param1, Fields2 = @param2 WHERE idFields = @paramID

[adam_k]

Anyone see any problems here?

MySqlCommand.Connection = MySqlConnection
        MySqlDataAdapter = New MySqlDataAdapter _
        ("UPDATE INTO Users SET (officer_id, area, supervisor) VALUES ('" & frmOptions.txtAddID.Text & "','" & frmOptions.txtAddArea.Text & ",'" & frmOptions.txtAddSupervisor.Text & "')", MySqlConnection)


        MySqlCommand.Parameters.AddWithValue("officer_id", frmOptions.txtAddID.Text)
        MySqlCommand.Parameters.AddWithValue("area", frmOptions.txtAddArea.Text)
        MySqlCommand.Parameters.AddWithValue("supervisor", frmOptions.txtAddSupervisor.Text)
        'MsgBox("Information Saved. ")
        Try
            MySqlCommand.ExecuteNonQuery()

I hate to break this to you, but you need to read a book or at least a tutorial on SQL.
Try this link for start: http://www.w3schools.com/sql/default.asp

Also when you are supplying the values in the command - by using the "'" & textbox.text &"'" method, you are not supposed to use the parameters.

PS: The msgbox shouldn't go before the Try.
PS2: Your last insert statement was missing a single quote (')

[mrbungle]

Thanks everyone for your patience in dealing with me- I know it can be hard. The W3 page helped a lot, I got it figured out what I need and got my value to update. It's hard sometimes trying to filter through all the information on the net, it seems like there's always 5 different ways to do one task.

[mrbungle]

While I'm here, here's the code I used to update my column in my database. This is it's most basic form without the message boxes and stuff.

Public Sub SaveOfficerInfo()

        MySqlConnection.ConnectionString = x.urlMySQLDatabase
        Dim myCommand As New MySqlCommand
        Dim MyAdapter As New MySqlDataAdapter
        Dim SQL As String
        MySqlConnection.Open()
        myCommand.Connection = MySqlConnection
        MyAdapter.SelectCommand = myCommand
        SQL = "UPDATE Users SET area = '" & frmOptions.txtAddArea.Text & "' WHERE officer_id = '" & frmOptions.txtAddID.Text & "'"
        myCommand.CommandText = SQL
        myCommand.ExecuteNonQuery()
        SQL = "UPDATE Users SET supervisor = '" & frmOptions.txtAddSupervisor.Text & "' WHERE officer_id = '" & frmOptions.txtAddID.Text & "'"
        myCommand.CommandText = SQL
        myCommand.ExecuteNonQuery()
        MySqlConnection.Close()


    End Sub

Thanks again everyone for your help.I appreciate it.