Add data to column in MySQL row

[mrbungle]

So I have a text box in a form. The test box needs to take the value entered and insert it into a column within a row in MySQL.

I know it's connecting to the MySQL online, I have a message box telling me so upon a successful connection. I'm just having problems adding the value. Here's my code:

Public Class frmOptions
    Dim ServerString As String = "server=db4free.net; Port =3306; user id=dailylogmain; password=XXXXXXX; database=dailylogmain;"
    Dim SQLConnection As MySqlConnection = New MySqlConnection

   
 Private Sub frmOptions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        SQLConnection.ConnectionString = ServerString
        Try
            If SQLConnection.State = ConnectionState.Closed Then
                SQLConnection.Open()
                MsgBox("Connected...")
            Else
                SQLConnection.Close()
                MsgBox("Connection failed")
            End If
        Catch ex As Exception
        End Try
    End Sub


Private Sub btnSaveStats_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim SQLStatement As String = "INSERT INTO users(stat1) VALUES('" & Me.txtStat1.Text & "')"

        SaveStats(SQLStatement)


    End Sub
    Public Sub SaveStats(ByRef SQLStatement As String)
        Dim cmd As MySqlCommand = New MySqlCommand
        With cmd
            .CommandText = SQLStatement
            .CommandType = CommandType.Text
            .Connection = SQLConnection
            .ExecuteNonQuery()
        End With
        SQLConnection.Close()
        MsgBox("Record Added")
        SQLConnection.Dispose()

    End Sub

When I try to submit, I'm not getting the messagebox saying "record added", so something is not right. I'm not getting any errors, just nothing is happening.

Does anyone have any ideas?

[mrbungle]

Hmm, just thinking out loud here-

I have a table called "users", and each row will have a few different users. It will include their name, ID number and some other stuff, and also each "user" will have some stats they need to track. So as mentioned above, the text box they enter a "stat" into will need to be saved in their "users" row.

So I guess I opened up another problem-

Dim SQLStatement As String = "INSERT INTO users(stat1) VALUES('" & Me.txtStat1.Text & "')"

I guess if each user needs to log into the app to ensure their stuff is getting added to their row, then this statement above may also be wrong, because I don't see in there how it adds to any one persons specific row... Also, there is a login box at the start of the app that connects to the MySQL database and confirms their login then lets them in under their ID number (which works fine)...

The users ID number is set as a Primary Key in the database.

Take a look at your btnSaveStats_Click. It has no Handles btnSaveStats.click. Unless your are adding the handler with add handler somewhere else then nothing is happening when you click the button. You must have deleted the button then added another one and renamed it. Add Handles btnSaveStats.click to the end of it and you will get a little activity when you click the button.

[mrbungle]

Thanks- I forgot the Handles. I got some activity, but it just closed the form- my confirmation message box didn't appear.

I still can't figure out this part either- I don't think my data is being put where it needs to. How do I add the data from the text box to a column based on an ID in a users table?

Dim SQLStatement As String = "INSERT INTO users(stat1) VALUES('" & Me.txtStat1.Text & "')"

You need to make some changes.

Get rid of all the code in the load event. All of it. Get rid of the Save Stats sub. All of it.

Leave your Global Connection String. Thats good.

Your button event code should take care of the rest.

If the user is already entered into the table then you need to use an update statement.

This is what the code should look like in the button save stats sub.

Dim Connection As New SqlConnection(ServerString)
 Dim Command As SqlCommand = Connection.CreateCommand

      Command.CommandText = "Update" & vbCrLf & _
                            "Users" & vbCrLf & _
                            "Set Stats = @Stats" & vbCrLf & _
                            "Where UserId = @UserId"

      Command.Parameters.AddWithValue("@Stats", TextBoxStats.Text)
      Command.Parameters.AddWithValue("@UserId", UserId)

 Try
      Connection.Open()
      Command.ExecuteNonQuery()
 Catch ex As Exception
      MsgBox(ex.Message)
 Finally
      Connection.Dispose()
      Command.Dispose()
 End Try

Put your variables in parameters like the above example. DO NOT stick values in the command statement like you did in your example. You hear that. DO NOT stick values in the command statement like you did in your example. Your asking for trouble. You will either crash or get an sql injection. An apostrophe in the textbox.text would cause your program to crash for 1 example.

Use the Try Catch Finally layout for your db ops like above.

Open your connection and close it as needed. Do not keep a global connection open. Your not saving anytime. Open the connection and close it when done.

Run the above example. If there is a problem you will receive an error message of what went wrong.

I used SQL as an example MySQL will be the same.

[adam_k]

Do you mean update data with the ID for criteria ?

PS: Although it's not the cause of your problem, you need to change this part as well

Try
            If SQLConnection.State = ConnectionState.Closed Then
                SQLConnection.Open()
                MsgBox("Connected...")
            Else
                SQLConnection.Close()
                MsgBox("Connection failed")
            End If
        Catch ex As Exception
        End Try

After you open the connection, verify that it's open and then msgbox if the connection is open or not. After you've got ex, why don't you msgbox it? How will the user know what's wrong? How will you know what's wrong when the users call you?

Try
            If SQLConnection.State = ConnectionState.Closed Then
                SQLConnection.Open()
            If SQLConnection.State = ConnectionState.Closed Then
                MsgBox("Connected...")
            Else
                SQLConnection.Close()
                MsgBox("Connection failed")
            End If
end if 
        Catch ex As Exception
msgbox(ex.message)
        End Try

[mrbungle]

Do you mean update data with the ID for criteria ?

Yea, exactly- I'm using an ID because it's always unique, unlike name.

I'm troubleshooting the connection now- it keeps failing to connect.

[mrbungle]

Yea, exactly- I'm using an ID because it's always unique, unlike name.

I'm troubleshooting the connection now- it keeps failing to connect.

Connection issue resolved. Checking the database, my data isn't being put in there.

So I have one table called users. In that table, when I click on Structures, I have my rows of stuff I need to collect. So in those rows, I have stat1, stat2, stat3, etc. because in the app, I have some text boxes labeled txtStat1, txtStat2, and so on to 14.

Now, I'm learning and figuring out the best way to lay out my database. I'll have 5 or 6 users that use this app, and each one has their own ID number. So when they log into the app, they need their ID number and password before they get in, which is stored in the users table under each person (just me so far). And so far, I have the login working fine. And in the app there is a field for their ID, called txtAddID, and other stuff like name, address, supervisor, area, phone, etc.

Later on, I'll learn and figure out that part where the app calls the data from the database to populate the text boxes when the app runs. But in phase 1, I'm working on using the app to get the data into the database.