Incorrect syntax near 'SET' in asp.net c#[Answered] RSS

• • Reply
  akhterr

  Member

  116 Points

  274 Posts

  Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 03:45 PM|akhterr|LINK

  when i updating record then this error is coming that 

  Incorrect syntax near 'SET' 

  please guide here is my code ...for update button

   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();

• • Reply
  mgebhard

  All-Star

  53041 Points

  23611 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 04:26 PM|mgebhard|LINK

  Your dynamic SQL code has bugs.  Please take a few moments out of your day to use the Visual Studio debugger to step through your code.  You can get this error if none of the condition statements are entered but single stepping through your code will highlight bugs rather quickly.

• • Reply
  akhterr

  Member

  116 Points

  274 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 04:30 PM|akhterr|LINK

   Please take a few moments out of your day to use the Visual Studio debugger to step through your code.

  

• • Reply
  mgebhard

  All-Star

  53041 Points

  23611 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 04:58 PM|mgebhard|LINK

  akhterr

   Please take a few moments out of your day to use the Visual Studio debugger to step through your code.

  

  I'll give you a hint... what happens to "query" if more than one condition is entered?  What happens if none of the conditions are entered?

• • Reply
  akhterr

  Member

  116 Points

  274 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 05:02 PM|akhterr|LINK

  hi,

  mgebhard,i just did my debug code ,i did not find any empty record or null value

• • Reply
  mgebhard

  All-Star

  53041 Points

  23611 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 05:13 PM|mgebhard|LINK

  akhterr

  hi,

  mgebhard,i just did my debug code ,i did not find any empty record or null value

  You did not take a look at "query"?   If a condition is entered more than one then query becomes...

  UPDATE Customer SET UPDATE Customer SET CustomerName =@CustomerName, UPDATE Customer SET UPDATE Customer SET CustomerName =@CustomerName, Name = @Name WHERE CustomerId =@Id

  If the condition is entered only one the query becomes.

  UPDATE Customer SET UPDATE Customer SET CustomerName =@CustomerName WHERE CustomerId =@Id

  You can learn how to use the Visual Studio debugger at the following link.  Pay special attention to single stepping and viewing variable. 

  https://docs.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour?view=vs-2019

  https://docs.microsoft.com/en-us/visualstudio/debugger/navigating-through-code-with-the-debugger?view=vs-2019

• • Reply
  akhterr

  Member

  116 Points

  274 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 05:23 PM|akhterr|LINK

  so what else can be done...

• • Reply
  mgebhard

  All-Star

  53041 Points

  23611 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 28, 2019 05:31 PM|mgebhard|LINK

  akhterr

  so what else can be done...

  Well, first you should understand the problem.  Then solve the problem.

• • Reply
  oned_gk

  All-Star

  52673 Points

  15720 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 29, 2019 01:24 AM|oned_gk|LINK

  Not sure, maybe reserved word there, try like below

  query += "[Name]=@Name,";

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 29, 2019 02:11 AM|Yang Shen|LINK

  Hi akhterr,

  According to your code, your cmd.command is "UPDATE Customer SET " which is never changed in your whole event.

  In fact, you can't do 

  query += "CustomerName =@CustomerName,";
  cmd.Parameters.AddWithValue("@CustomerName", customername);

  AFTER you already bound the cmd.command at

  SqlCommand cmd = new SqlCommand(query, con);

  Please refer to below demo and modify your code:

  .ASPX:

  <html xmlns="http://www.w3.org/1999/xhtml">
  <head runat="server">
      <title></title>
  </head>
  <body>
      <form id="form1" runat="server">
          <div>
              <asp:Label ID="lbid" runat="server" Text="100"></asp:Label><br />
              <asp:TextBox ID="txtcus" runat="server"></asp:TextBox><br />
              <asp:FileUpload ID="FileUpload1" runat="server" /><br />
              <asp:Button ID="Button1" runat="server" Text="Update" OnClick="Button1_Click" />
          </div>
      </form>
  </body>
  </html>

  .CS:

  protected void UpdateData()
          {
              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["DConString"].ConnectionString;
              SqlConnection con = new SqlConnection(constr);
              string query = "UPDATE Customer SET ";
              SqlCommand cmd = new SqlCommand();
              cmd.Connection = con;
  
              if (!string.IsNullOrEmpty(customername))
              {
                  query += "CustomerName =@CustomerName,";
              }
              if (!string.IsNullOrEmpty(filename))
              {
                  query += "Name = @Name,";
              }
              if (bytes.Length > 0)
              {
                  query += "Data=convert(varbinary,@Data),";
              }
              if (!string.IsNullOrEmpty(contenttype))
              {
                  query += "ContentType=@ContentType,";
              }
              query = query.TrimEnd(',');
              query += " WHERE CustomerId =@Id";
              cmd.CommandText = query;
              cmd.Parameters.AddWithValue("@CustomerName", customername);
              cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
              cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
              cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contenttype;
              cmd.Parameters.AddWithValue("@Id", id);
              con.Open();
              cmd.ExecuteNonQuery();
              con.Close();
          }
  
          protected void Button1_Click(object sender, EventArgs e)
          {
              try
              {
                  UpdateData();
                  Response.Write("<script>alert('Success')</script>");
              }
              catch
              {
                  Response.Write("<script>alert('Fail')</script>");
              }
          }

  SQL:

  create table Customer
  (
  CustomerId int,
  CustomerName varchar(50),
  Name varchar(50),
  Data varbinary(1000),
  ContentType varchar(50),
  )
  
  insert into Customer(CustomerId) values(100)
  
  select * from Customer
  
  --drop table Customer

  Below is the result of this demo and how you can debug your code in VS:

  

  

  Best Regard,

  Yang Shen

• • Reply
  akhterr

  Member

  116 Points

  274 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 29, 2019 05:38 AM|akhterr|LINK

  Thanks for support ,Yang Shen,,,

  Now both value customer name and image is getting update ,but after updating image ,image is not getting display ,i am sharing image of table column status of data (last record ,i used your code to update ,in which data value is coming different as compare my second last record with same image,,)  please guide...

  https://ibb.co/Mh65PVt

  

• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: Incorrect syntax near 'SET' in asp.net c#

  Aug 29, 2019 05:56 AM|Yang Shen|LINK

  Hi akhterr,

  It seems it's a data length related issue, will you share us your table structure to check the data type of your [Data]?

  Or you can try change the "n" in below code to an appropriate number:

  query += "Data=convert(varbinary(n),@Data),";

  Best Regard,

  Yang Shen