Syntax Error in Update Command

Question

1.57/5 (4 votes)

See more:

This is my code in Update Command

VB

cmdupdate.CommandText = ("update personinfo set SurName = ['" & DataGridView1.Item("SurName", i).Value.ToString & "'],FirstName = ['" & DataGridView1.Item("Firstname", i).Value.ToString & "'],MiddleName = ['" & DataGridView1.Item("Middlename", i).Value.ToString & "'],Present_CurrentAddress = ['" & DataGridView1.Item("Present_CurrentAddress", i).Value.ToString & "'],ProvincialAddress = ['" & DataGridView1.Item("ProvincialAddress", i).Value.ToString & "'],Noofchildren = ['" & DataGridView1.Item("Noofchildren", i).Value.ToString & "'],Height = ['" & DataGridView1.Item("Height", i).Value.ToString & "'],weight = ['" & DataGridView1.Item("Weight", i).Value.ToString & "'],birthdate = ['" & DataGridView1.Item("birthdate", i).Value.ToString & "'],age = ['" & DataGridView1.Item("age", i).Value.ToString & "'],birthplace = ['" & DataGridView1.Item("birthplace", i).Value.ToString & "'],gender = ['" & DataGridView1.Item("gender", i).Value.ToString & "'],civilstatus = ['" & DataGridView1.Item("civilstatus", i).Value.ToString & "'],educationalattain = ['" & DataGridView1.Item("educationalattain", i).Value.ToString & "'],schoolname = ['" & DataGridView1.Item("schoolname", i).Value.ToString & "'],course = ['" & DataGridView1.Item("course", i).Value.ToString & "'],yearsofstudy = ['" & DataGridView1.Item("yearsofstudy", i).Value.ToString & "'],collegestatus = ['" & DataGridView1.Item("collegestatus", i).Value.ToString & "'],trainingschoolname = ['" & DataGridView1.Item("trainingschoolname", i).Value.ToString & "'],positiontrained = ['" & DataGridView1.Item("positiontrained", i).Value.ToString & "'],yearstrained = ['" & DataGridView1.Item("yearstrained", i).Value.ToString & "'],coursetrained = ['" & DataGridView1.Item("coursetrained", i).Value.ToString & "'],detachment = ['" & DataGridView1.Item("detachment", i).Value.ToString & "'],Region = ['" & DataGridView1.Item("Region", i).Value.ToString & "'],effdate = = ['" & DataGridView1.Item("effdate", i).Value.ToString & "'],workstatus = ['" & DataGridView1.Item("workstatus", i).Value.ToString & "'],empnumber = ['" & DataGridView1.Item("empnumber", i).Value.ToString & "'],positionemployed = ['" & DataGridView1.Item("positionemployed", i).Value.ToString & "'],yearsofemployed = ['" & DataGridView1.Item("yearsofemployed", i).Value.ToString & "'],sssnumber = ['" & DataGridView1.Item("sssnumber", i).Value.ToString & "'],tinnumber = ['" & DataGridView1.Item("tinnumber", i).Value.ToString & "'],nbi = ['" & DataGridView1.Item("nbi", i).Value.ToString & "'],nbidate = ['" & DataGridView1.Item("nbidate", i).Value.ToString & "'],pnp = ['" & DataGridView1.Item("pnp", i).Value.ToString & "'],pnpdate = ['" & DataGridView1.Item("pnpdate", i).Value.ToString & "'],priorexp = ['" & DataGridView1.Item("priorexp", i).Value.ToString & "'],gdexp = ['" & DataGridView1.Item("gdexp", i).Value.ToString & "'],guardposition = ['" & DataGridView1.Item("guardposition", i).Value.ToString & "'],uniformdate = ['" & DataGridView1.Item("uniformdate", i).Value.ToString & "'],millitary = ['" & DataGridView1.Item("millitary", i).Value.ToString & "'],LOS = ['" & DataGridView1.Item("LOS", i).Value.ToString & "'],previousemp = ['" & DataGridView1.Item("previousemp", i).Value.ToString & "'],licno = ['" & DataGridView1.Item("licno", i).Value.ToString & "'],licdate = ['" & DataGridView1.Item("licdate", i).Value.ToString & "'],licexp = ['" & DataGridView1.Item("licexp", i).Value.ToString & "'],dateofemp = ['" & DataGridView1.Item("dateofemp", i).Value.ToString & "'],badgeno = ['" & DataGridView1.Item("badgeno", i).Value.ToString & "'],neurocenter ['" & DataGridView1.Item("neurocenter", i).Value.ToString & "'],neuroresult = ['" & DataGridView1.Item("neuroresult", i).Value.ToString & "'],neurodate = ['" & DataGridView1.Item("neurodate", i).Value.ToString & "'],drugcenter = ['" & DataGridView1.Item("drugcenter", i).Value.ToString & "'],marijuana = ['" & DataGridView1.Item("marijuana", i).Value.ToString & "'],shabu = ['" & DataGridView1.Item("shabu", i).Value.ToString & "'],drugresult = ['" & DataGridView1.Item("drugresult", i).Value.ToString & "'],histories = ['" & DataGridView1.Item("histories", i).Value.ToString & "'] where surname =  '" & DataGridView1.SelectedRows.ToString & "'")

im trying to update a data straight through the datagridview but sytax error in update command pls healp me.

Posted 5-Jun-13 0:09am

Crixalis Paul

Updated 5-Jun-13 0:18am

Prasad_Kulkarni

v3

Add a Solution

Comments

Pheonyx 5-Jun-13 6:14am

I suggest you start by looking at using Parameters to make that a lot more readable.
Secondly, spend some time formatting it so people can see what is going on, use the "Improve Question" facility to adjust the formatting.

[no name] 5-Jun-13 6:26am

Seriously? I am not even going to try and read this. You need to really read up on SQL injection attacks also.

Maciej Los 5-Jun-13 6:30am

What error???
Please, be more specific and provide more details. Use "Improve question" widget!

CHill60 5-Jun-13 7:19am

Are you sure this is where you get your syntax error ... nothing wrong with that line of code syntax-wise

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2013-06-05T01:32:00

Ok ... you're not getting the syntax error in vb.net, the error is being reported by your database.

It's because you are surrounding things with square brackets - you would only do this if you were using another column to update with, not a text value.

For example, if you have a last name of "Smith" then you are generating sql like this

SQL

update personinfo set SurName = ['Smith'], etc

and I doubt very much you have a column called Smith on your personinfo table. You need the final sql to look like ...

SQL

update personinfo set SurName = 'Smith', ...

without the [ ].

So your starting point to getting this to work is to get rid of the [ and ] in this string.

Next step - read the comment from ThePhantomUpvoter and keep yourself safe from SQL injection attacks by using either a parameterised query http://www.dotnetperls.com/sqlparameter[^] or a stored procedure (or even both if need be)

Lastly - rather than writing huge single line strings which are impossible to read consider using StringBuilder[^]

Dave Kreskowiak · Answer 2 · 2013-06-05T03:53:00

MY EYES!! MY EYES!! WOW! I haven't seen a SQL statement string concatentation that bad in a long time.

Wasn't there something in your head that had to look at that cluster *uck you just wrote and said "There has got to be a better way than this?"

Google for "VB.NET SQL parameterized query" for information on why that you're doing is so bad, beyond the obvious problem of being able to debug and support that code. You can also read more if you Google for "SQL Injection Attack".

Syntax Error in Update Command

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0