Writing to MySQL problem

Hi I don't post often although I am here a lot learning from other posts.

Details:
VB.NET 2010
MySQL v5.5.42-cll

With the current setup I can fetch MYSQL data from the remote server to display in forms.
Using same rules I am trying to submit data back to database and fail with MYSQL Syntax Error.

Here's the code:

Code:

---

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim READER As MySqlDataReader
        Dim cmd As New MySqlCommand
        Dim con As New MySqlConnection
        Dim DatabaseName As String = "server-recognized-db-name"
        Dim server As String = "ip-number"
        Dim userName As String = "db-user"
        Dim password As String = "db-pass"
        If Not con Is Nothing Then con.Close()
        con.ConnectionString = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false", server, userName, password, DatabaseName)

        Try
            con.Open()
            Dim Query As String
            Query = "insert into DatabaseName.TabelName (ColumnName) values ('" & TextBox1.Text & "') WHERE (AnotherColumnName) = ('" & TextBox2.Text & "')"
            cmd = New MySqlCommand(Query, con)
            READER = cmd.ExecuteReader

            MessageBox.Show("Data Saved")
            con.Close()

        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()

        End Try

        con.Close()

---

The way it should work is the user enters some information into TextBox1
Then enters their account ID into TextBox2
The value of TextBox2 determines where the data of TextBox1 should be saved in the database.

Example:

Table Name
Table Column 1 - Account ID (TextBox2 user entered value)
Table Column 2 - Some other content
Table Column 3 - Where I want to save the content of TextBox1

The MYSQL error I keep getting regardless of how many different ways I write this is:

Quote:

---

MySQL Syntax Error (On Line 1) WHERE AnotherColumnName = SomeNumbers

---

Any help on this is much appreciated.
Thanks

First things first, why would you be calling ExecuteReader to save data? As the name suggests, it's for reading data.

As for the issue, you need to brush up on your SQL syntax. INSERT statements don't have a WHERE clause. How could they? A WHERE clause is to identify the existing row to act on but INSERT statements don't act on existing rows. INSERT is for new records and UPDATE is for existing records, so UPDATE does accept a WHERE clause.

You also ought not to ever use string concatenation like that. Always use parameters to insert values into SQL code. To learn how and why, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.

Thanks

Why ExecuteReader I thought I needed the program to (peek) at the table first.

Update I tried a half dozen times, even to the extent of using the format as written in the MySQL guides, never got that working so trimmed the update part off to try that. See following;

Code:

---

"UPDATE `DatabaseName`.`TableName` SET `ColumnName` = \'1234567\' WHERE `DatabaseName`.`AnotherColumnName` = 1111;";

---

I have no idea what ADO.NET is :blush:

I will look at your posts as you suggested, thanks again.

**** Edit ****

Haha, I have actually already read these posts! I couldn't get them to work for me.
Maybe you can give my code an edit so I can see where I am going wrong because I have been walking around in circles gOOgling answers all day now.

Thanks again.

Quote:

---

"UPDATE `DatabaseName`.`TableName` SET `ColumnName` = \'1234567\' WHERE `DatabaseName`.`AnotherColumnName` = 1111;";

---

Why would that work?

If you want help with the parameters then write what you think is the correct code and, if it doesn't work, show us what you did and tell us what happened.

That is the point, it didn't work, but this is what I got from going to the MySQL website and reading half a dozen of their really long guides.

Look I am exhausted hence why I posted here seeking assistance.
The problem is not my ability to understand nor is it about my coding, but rather I don't get the syntax needed by MySQL to get my 'write' request accepted.

Thanks for your help anyways.

Code:

---

Dim cmdString as string = "UPDATE `DatabaseName`.`TableName` SET `ColumnName` = @columnName WHERE `DatabaseName`.`AnotherColumnName` = @anotherName;"
Using cmd As New MySql.Data.MySqlClient.MySqlCommand(cmdString)
   
    cmd.Connection = myConnection
    cmd.Parameters.AddWithValue("@columnName, "1234567")
    cmd.Parameters.AddWithValue("@anotherName, "11111")
    cmd.ExecuteNonQuery()

end Using

---

The is freehand code, so it may not be quite spot on, but the gist is that rather than include the data in the query string itself, you use placeholders and then set parameters that use the placeholder.

Thanks kebo
I have seen this approach (Parameters.AddWithValue("@..) in other forums and thought this was for older VB versions.

Will give this approach a try today and see how I get along.

Thanks again.

Quote:

---

Originally Posted by GrantK

Thanks kebo
I have seen this approach (Parameters.AddWithValue("@..) in other forums and thought this was for older VB versions.

Will give this approach a try today and see how I get along.

Thanks again.

---

You've got to be kidding. That is exactly what my blog post on Parameters In ADO.NET shows how to use. You previously said that you tried it and it didn't work and now you're saying that you thought it was older VB versions and you'll give it go. Which is it?

Ok to say again so this doesn't get nasty.

I went to your website as I did many hundreds of others. OMG exciting!
Then I looked at your guides. Nice html formatting!
Then I saw references to ADO.NET to which (as I said earlier in this thread) I know nothing about, so did not go further into ADO.NET. Shock horror!
Then I looked at your other examples and confused between what is ADO.NET and parameters which I had seen earlier on a strictly VB forum my adventure through your guides didn't get me past where I was stuck.
So if you were able to answer the issue as kebo did.....Then low and behold I would not feel the need to tell you how many sugars I take in my tea.

Thanks have a great whatever it is you do.

Quote:

---

Originally Posted by GrantK

Then I saw references to ADO.NET to which (as I said earlier in this thread) I know nothing about, so did not go further into ADO.NET. Shock horror!

---

So, when you said:

Quote:

---

Haha, I have actually already read these posts! I couldn't get them to work for me.

---

what you actually meant was you started reading it but didn't bother finishing it or trying to get it to work. Thank you for clearing that up.

Quote:

---

Originally Posted by jmcilhinney

So, when you said:what you actually meant was you started reading it but didn't bother finishing it or trying to get it to work. Thank you for clearing that up.

---

What I actually meant is what I actually said "I couldn't get it to work for me"
And as per sugars in my tea, I take half a teaspoon. Not 1 teaspoon, nor one and a half teaspoons.

You're welcome.

Different strokes for different folks and we all have different ways of learning. JM, I think your tutorials are excellent but, if you're a new developer who's not even familiar with the term ADO.Net then the parameters one probably does assume a body of knowledge you just don't have. For a start it assumes you know what ADO.Net means. It also gives alot more information than Grant was looking for and I can believe it might have been overwhelming, particularly if he discovered it after a bunch of prior blind alleys. Don't take it so personally.

Grant, JM has a habit of giving just the information you need and no more. The thing is, it's pretty much always the exact information you need. In this case it would have been worth working through that blog post because it would have got you where you needed to be.

BTW JM, it actually took me a little while to find the entry in your blog. You've got enough stuff in there to merit a search function or a subject index rather than needing the user to browse by month.