locked
Incorrect syntax near 'SET' in asp.net c# RRS feed

  • Question

  • User-367318540 posted

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

    Wednesday, August 28, 2019 3:45 PM

Answers

  • User-719153870 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 29, 2019 2:11 AM

All replies

  • User475983607 posted

    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.

    Wednesday, August 28, 2019 4:26 PM
  • User-367318540 posted

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

    innocent

    Wednesday, August 28, 2019 4:30 PM
  • User475983607 posted

    akhterr

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

    innocent

    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?

    Wednesday, August 28, 2019 4:58 PM
  • User-367318540 posted

    hi,

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

    Wednesday, August 28, 2019 5:02 PM
  • User475983607 posted

    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

    Wednesday, August 28, 2019 5:13 PM
  • User-367318540 posted

    so what else can be done...

    Wednesday, August 28, 2019 5:23 PM
  • User475983607 posted

    so what else can be done...

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

    Wednesday, August 28, 2019 5:31 PM
  • User-1716253493 posted

    Not sure, maybe reserved word there, try like below

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

    Thursday, August 29, 2019 1:24 AM
  • User-719153870 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 29, 2019 2:11 AM
  • User-367318540 posted

    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

    https://ibb.co/Mh65PVt

    Thursday, August 29, 2019 5:38 AM
  • User-719153870 posted

    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

    Thursday, August 29, 2019 5:56 AM