Error while Updating data in SQL

Question

0.00/5 (No votes)

See more:

I am getting error while updating data in SQL in vb.net. I am using VS2013.
Since query is long so I have separated using "&_" .
-----------------------------------------------------------------------------
Code:
-----------------------------------------------------------------------------

SQL

con.Open()
      ss = "UPDATE emp_master set empid=" & txtempid.Text & ",empname='" & txtename.Text & "',pfno='" & txtpfno.Text & "',dob=" & dtpdob.Value & ",gender='" & g & "'," & _
         "contact=" & txtcontact.Text & ",email='" & txtemail.Text & "',doj=" & dtpdoj.Value & ",address='" & txtaddress.Text & "',edu='" & txtedu.Text & "'," & _
         "nationality='" & txtNation.Text & "',bloodgroup=" & cmb_Bloodgrp.SelectedText & ", desig='" & txtdesig.Text & "',exp=" & txtexp.Text & ",salary=" & txtsalary.Text & "," & _
         "dept='" & txtdept.Text & "',pic=" & arrImg.ToString() & ",usertype=" & Cmb_utype.SelectedText & " WHERE empid=" & txtempid.Text & " "
      com = New SqlCommand(ss, con)
      com.ExecuteNonQuery()
      MsgBox("Data Updated Successfully !", MsgBoxStyle.Information, MsgBoxStyle.OkCancel)
con.Close()

----------------------------------------------------------------------------
Error:
---------------------------------------------------------------------------

SQL

Additional information: Incorrect syntax near ','.

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Posted 14-Feb-15 22:54pm

A94

Add a Solution

Comments

Tomas Takac 15-Feb-15 5:07am

This is a mess. Plus your code is vulnerable to SQL injection. Use parameters in your queries! To debug this just have a look on the content of ss it should be obvious what's wrong there. If not, update your question with the text.

2 solutions

Solution 2

EXP is a keyword - https://msdn.microsoft.com/en-us/library/ms179857.aspx[^].
Wrap the exp column name in a [] bracket e.g. [exp].

In general, you should use command parameters in a query.

Posted 14-Feb-15 23:22pm

Abhinav S

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)

OriginalGriff · Accepted Answer · 2015-02-14T23:16:00

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

The chances are that that would cure your problem at the same time as protecting your DB.

VB

ss = "UPDATE emp_master SET empid=@EID, empName=@ENM, ..."
com = new SqlCommand(ss, con)
com.Parameters.AddWithValue("@EID", txtempid.Text)
com.Parameters.AddWithValue("@ENM", txtename.Text)
...
com.ExecuteNonQuery()