Incorrect syntax near ‘SET’ in asp.net c#

Akhter HUssain

7y1.7kLearn iOS Programming

When i updating record then this error is coming...please guide

below mentioned behind update button code

int id = int.Parse(lbid.Text);
string filePath = FileUpload1.PostedFile.FileName;
string customername = txtcus.Text;
string filename = Path.GetFileName(filePath);
string ext = Path.GetExtension(filename);
string contenttype = String.Empty;
switch (ext) {
case ".doc":
contenttype = "application/vnd.ms-word";
break;
case ".docx":
contenttype = "application/vnd.ms-word";
break;
case ".xls":
contenttype = "application/vnd.ms-excel";
break;
case ".xlsx":
contenttype = "application/vnd.ms-excel";
break;
case ".jpg":
contenttype = "image/jpg";
break;
case ".png":
contenttype = "image/png";
break;
case ".gif":
contenttype = "image/gif";
break;
case ".pdf":
contenttype = "application/pdf";
break;
}
Stream fs = FileUpload1.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32) fs.Length);
//string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
//SqlConnection con = new SqlConnection(constr);
string query = "UPDATE Customer SET ";
SqlCommand cmd = new SqlCommand(query, con);
if (!string.IsNullOrEmpty(customername)) {
query += "CustomerName =@CustomerName,";
cmd.Parameters.AddWithValue("@CustomerName", customername);
}
if (!string.IsNullOrEmpty(filename)) {
query += "Name = @Name,";
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
}
if (bytes.Length > 0) {
query += "Data=@Data,";
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
}
if (!string.IsNullOrEmpty(contenttype)) {
query += "ContentType=@ContentType,";
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contenttype;
}
query = query.TrimEnd(',');
query += " WHERE CustomerId =@Id";
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain7y ago
Accepted answer
The problem in your code is you have defined a query in SqlCommand object before a complete query is formed

you first need to make SqlParameter object and add parameters based on conditions
Replace your code with below code
string query = "UPDATE Customer SET ";
SqlCommand cmd = new SqlCommand("", con);
if (!string.IsNullOrEmpty(customername)) {
query += "CustomerName =@CustomerName,";
cmd.Parameters.AddWithValue("@CustomerName", customername);
}
if (!string.IsNullOrEmpty(filename)) {
query += "Name = @Name,";
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
}
if (bytes.Length > 0) {
query += "Data=@Data,";
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
}
if (!string.IsNullOrEmpty(contenttype)) {
query += "ContentType=@ContentType,";
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contenttype;
}
query = query.TrimEnd(',');
query += " WHERE CustomerId =@Id";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
It may resolve your issue update your query text in last
Sonu Gupta7y ago
Hi Akhter,

@Priyanka.. is right...Change the code according that.
Akhter HUssain7y ago
Hi Priyanka Jain,

getting below error when i updating....
Sonu Gupta7y ago
Hi,

Debug the code and read the final query and try this query to execute in SQL Server. Also check the if condition true or false...
Priyanka Jain7y ago
can you please share what error are you getting?
Akhter HUssain7y ago
hi amit,

just share customer name
Amit Gupta7y ago
Have you checked by debugging, what value is coming in the query? Can you share that
Akhter HUssain7y ago
i didn't find any empty record during debug
Akhter HUssain7y ago
Hi Amit,
i updating image in database with customer name,
Jaish Mathews7y ago
Easiest way to debug is to check the value of "query" before excuting.
Amit Gupta7y ago
There might be blank values in your query after set keyword. Debug your code and check the query value at line no 61.