Results 1 to 10 of 10

Thread: [RESOLVED] Getting Mysql syntax error where there wasn't one before...

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    57

    Resolved [RESOLVED] Getting Mysql syntax error where there wasn't one before...

    Name:  vbForum.jpg
Views: 607
Size:  28.1 KB

    I need to establish an order to my photos. I designed this table to rearrange the order then update them one by one.

    I started out with a combobox, had it bound and sourced to a datatable. The thing is I changed my mind on how I wanted to tackle this so I changed my algorithm. I went with a listbox instead. I also went from reading it into my control, to reading it into an array because I can't reorder a bound source. I used the same update code from the combobox that was working perfect and all I changed was the query string, value for value the nearly same stuff. I changed the column name in MySql from isPrimary type CHAR to order type VarChar(3). I changed the right stuff I know, so why the sudden syntax error with my MySql query?

    Here is my update code behind the update button:

    Code:
     Private Sub Button17_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button17.Click
            'Update photos with which is the primary image.
    
            'UPDATE part_storage.PNL idPNL, LinkPNL, PNL, PNLPrimary
            QueryUpdate = "UPDATE part_storage.photos SET order='" & ListBoxImgURL.SelectedIndex.ToString & "' WHERE imgURL='" & ListBoxImgURL.Text & "'"
            MySqlConn = New MySqlConnection
            MySqlConn.ConnectionString = "server=10.1.1.10;userid=enkel;password=e032187;database=part_storage"
            Dim SDA As New MySqlDataAdapter
            Dim READER As MySqlDataReader
            Dim bSource As New BindingSource
            Try
                MySqlConn.Open()
                COMMAND = New MySqlCommand(QueryUpdate, MySqlConn)
                READER = COMMAND.ExecuteReader
                MySqlConn.Close()
                ProcessSuccessful3.Visible = True
            Catch ex As MySqlException
                MessageBox.Show(ex.Message)
            Finally
                MySqlConn.Dispose()
            End Try
        End Sub
    This is the error message I get...
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order='0' WHERE imgURL='2015_07_08-10_39_07am3.jpg'' at line 1

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Getting Mysql syntax error where there wasn't one before...

    Well maybe someone else will see it but I can't personally guess what is in & ListBoxImgURL.SelectedIndex.ToString and ListBoxImgURL.Text which may be the issue. Perhaps there is a single quote in there or something like that. Post what is actually being set to SQL. You can put ?QuryUpdate in the immeadiate window to get the value or Debug.Print QueryUpdateDate before the execute.

    The point is you are showing us what the code looks like, not what is being sent to SQL where the error is occurring. That will probably help us resolve your issue.

    Thanks!
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    57

    Re: Getting Mysql syntax error where there wasn't one before...

    I thought the error did that. I took both values and sent them to a textbox and they are clean strings. At first I was thinking it was because it is signed integers taking up more than 3 character, but then the 0 is only 1 character. I concatenated an asterisk to each side and got these: *2015_07_08-10_39_07am3.jpg* and *0*.

  4. #4

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    57

    Re: Getting Mysql syntax error where there wasn't one before...

    The only thing is changed was the word isPrimary= to order= and then the two variables ListBoxImgURL.SelectedIndex.ToString and ListBoxImgURL.Text and it started giving that error. I spent hours staring at this thing and I am drawing a blank.

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Getting Mysql syntax error where there wasn't one before...

    Quote Originally Posted by teamster View Post
    I thought the error did that. I took both values and sent them to a textbox and they are clean strings. At first I was thinking it was because it is signed integers taking up more than 3 character, but then the 0 is only 1 character. I concatenated an asterisk to each side and got these: *2015_07_08-10_39_07am3.jpg* and *0*.
    I apologize...I didn't see that. I give that little lecture so much I didn't look for it there

    Can you try it in a query window instead of the program? It might seem more obvious there.
    Please remember next time...elections matter!

  6. #6

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    57

    Re: Getting Mysql syntax error where there wasn't one before...

    The actual Query String looks like this when fed to a textbox:

    UPDATE part_storage.photos SET order='0' WHERE imgURL='2015_07_08-10_39_07am3.jpg'

    Here's a thought. Is ORDER a key word?

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Getting Mysql syntax error where there wasn't one before...

    I think you just nailed it. In MS SQL you would enclose it in brackets. I don't know MySQL.
    Please remember next time...elections matter!

  8. #8

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    57

    Re: Getting Mysql syntax error where there wasn't one before...

    Well I guess it is a Key word. I plugged the query into MySql Workbench and it highlighted the work with the message: "Syntax Error: unexpected 'order' (order)"

  9. #9

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    57

    Re: Getting Mysql syntax error where there wasn't one before...

    Those dang keywords. I wasn't even sure SQL had keywords. Well, I do now. Thanks for your help.

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: [RESOLVED] Getting Mysql syntax error where there wasn't one before...

    Yes order is a key word: ORDER BY ... so you need to tell MySql that your order is the field object order. And in MySql that's done by using the back ticks....
    UPDATE part_storage.photos SET `order`='0' WHERE imgURL='2015_07_08-10_39_07am3.jpg'

    see the ` around order, as opposed to ' around the data?

    Upper helft hand on the keyboard, next to the 1 key...

    That said... why is order a string? Should be a number. Here's why: as long as you have 9 or less to order you'll be fine because it will go 1,2,3,4,5,6,7,8,9 ... but once you cross over in to 10... you'll get 1,10,2,3,4,5,6,7,8,9 and then 1,10,11,2,3,4,5,6,7,8,9 strings don't sort numbers ... it's a string and will be treated as such. It just happens to look like a number. But if you change it to a numerical datatype... then you can order by `order` and it will sort properly.

    Suggestion - change the field name to Sequence and the datatype to integer, drop the ' marks around the data for Sequence and if you're truly feeling saucey, use parameters, although the odds of a sql injection or mal-formed sql in this case is quite minimal.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

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
  •  



Click Here to Expand Forum to Full Width