-
Jul 17th, 2015, 02:21 AM
#1
Thread Starter
Member
[RESOLVED] Getting Mysql syntax error where there wasn't one before...
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
-
Jul 17th, 2015, 05:13 AM
#2
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!
-
Jul 17th, 2015, 06:43 AM
#3
Thread Starter
Member
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*.
-
Jul 17th, 2015, 06:46 AM
#4
Thread Starter
Member
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.
-
Jul 17th, 2015, 06:49 AM
#5
Re: Getting Mysql syntax error where there wasn't one before...
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
Can you try it in a query window instead of the program? It might seem more obvious there.
Please remember next time...elections matter!
-
Jul 17th, 2015, 06:50 AM
#6
Thread Starter
Member
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?
-
Jul 17th, 2015, 06:51 AM
#7
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!
-
Jul 17th, 2015, 06:56 AM
#8
Thread Starter
Member
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)"
-
Jul 17th, 2015, 06:58 AM
#9
Thread Starter
Member
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.
-
Jul 17th, 2015, 07:02 AM
#10
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
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
|