Writing to MySQL problem-VBForums
Results 1 to 12 of 12

Thread: Writing to MySQL problem

  1. #1

    Thread Starter
    New Member GrantK's Avatar
    Join Date
    Mar 2015
    Location
    Stratford New Zealand
    Posts
    12

    Question 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 02:02 AM. Reason: added MySQL version
    Spooning it with style


  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,134

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    New Member GrantK's Avatar
    Join Date
    Mar 2015
    Location
    Stratford New Zealand
    Posts
    12

    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 03:34 AM. Reason: update content
    Spooning it with style


  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,134

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    New Member GrantK's Avatar
    Join Date
    Mar 2015
    Location
    Stratford New Zealand
    Posts
    12

    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.
    Spooning it with style


  6. #6
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,616

    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

  7. #7

    Thread Starter
    New Member GrantK's Avatar
    Join Date
    Mar 2015
    Location
    Stratford New Zealand
    Posts
    12

    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.
    Spooning it with style


  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,134

    Re: Writing to MySQL problem

    Quote Originally Posted by GrantK View Post
    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?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9

    Thread Starter
    New Member GrantK's Avatar
    Join Date
    Mar 2015
    Location
    Stratford New Zealand
    Posts
    12

    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.
    Spooning it with style


  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,134

    Re: Writing to MySQL problem

    Quote Originally Posted by GrantK View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    New Member GrantK's Avatar
    Join Date
    Mar 2015
    Location
    Stratford New Zealand
    Posts
    12

    Re: Writing to MySQL problem

    Quote Originally Posted by jmcilhinney View Post
    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.
    Spooning it with style


  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,513

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - 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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.