-
Apr 27th, 2015, 11:50 PM
#1
Thread Starter
New Member
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:
MySQL Syntax Error (On Line 1) WHERE AnotherColumnName = SomeNumbers
Any help on this is much appreciated.
Thanks
Last edited by GrantK; Apr 28th, 2015 at 01:02 AM.
Reason: added MySQL version
-
Apr 28th, 2015, 02:18 AM
#2
Re: Writing to MySQL problem
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.
-
Apr 28th, 2015, 02:30 AM
#3
Thread Starter
New Member
Re: Writing to MySQL problem
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
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.
Last edited by GrantK; Apr 28th, 2015 at 02:34 AM.
Reason: update content
-
Apr 28th, 2015, 02:42 AM
#4
Re: Writing to MySQL problem
"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.
-
Apr 28th, 2015, 02:49 AM
#5
Thread Starter
New Member
Re: Writing to MySQL problem
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.
-
Apr 28th, 2015, 06:41 AM
#6
Re: Writing to MySQL problem
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.
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Apr 28th, 2015, 04:07 PM
#7
Thread Starter
New Member
Re: Writing to MySQL problem
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.
-
Apr 28th, 2015, 06:09 PM
#8
Re: Writing to MySQL problem
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?
-
Apr 28th, 2015, 07:57 PM
#9
Thread Starter
New Member
Re: Writing to MySQL problem
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.
-
Apr 28th, 2015, 08:53 PM
#10
Re: Writing to MySQL problem
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:
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.
-
Apr 28th, 2015, 09:40 PM
#11
Thread Starter
New Member
Re: Writing to MySQL problem
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.
-
Apr 29th, 2015, 08:24 AM
#12
Re: Writing to MySQL problem
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|