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

Attachment 128463

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...

Quote:

---

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

---

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!

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*.

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.

Quote:

---

Originally Posted by teamster

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 :o

Can you try it in a query window instead of the program? It might seem more obvious there.

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?

I think you just nailed it. In MS SQL you would enclose it in brackets. I don't know MySQL.

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)"

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

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