Unknown column 'New' in 'field list' while using sql command in the asp.net

Question

I trying to update a GridView using asp.net while updating I am passing the text box value but I am getting the above error.

Label l1 = g1.Rows[e.RowIndex].FindControl("idlbl") as Label;
TextBox t1 = g1.Rows[e.RowIndex].FindControl("typeText") as TextBox;

string orderType = t1.Text;
string Query = @"update app_order_master set order_amt=" + orderType + " where order_id=" + l1.Text;
MySqlCommand cmd = new MySqlCommand(Query);            
cmd.Connection = sqlconn;
cmd.ExecuteNonQuery();

I think it is duplicated, check this one: stackoverflow.com/questions/1346209/… — O.O
– O.O, Commented Jul 29, 2019 at 15:50
You are building up your SQL very poorly here, if you had debugged your code, you would see where the problem is. Check the value of Query and notice you have missing quotes. Secondly, go immediately and look up "sql injection" to see why this code is very dangerous. — DavidG
– DavidG, Commented Jul 29, 2019 at 15:51
It is related to how you are creating the "Update" statement, something wrong with the quotations. In debug mode, check the "update" statement, how it looks. — O.O
– O.O, Commented Jul 29, 2019 at 15:51
Stop trying to assemble SQL using string concatenation and use parameters. That will fix your current problem, and many future problems. — Dour High Arch
– Dour High Arch, Commented Jul 29, 2019 at 15:53

Giddy Naya · Accepted Answer · 2019-07-29 16:06:48Z

5

Try using parameters instead

Label l1 = g1.Rows[e.RowIndex].FindControl("idlbl") as Label;
TextBox t1 = g1.Rows[e.RowIndex].FindControl("typeText") as TextBox;

string orderType = t1.Text;
string order_id = l1.Text;
string Query = "update app_order_master set order_amt = @orderType where order_id = @order_id";
MySqlCommand cmd = new MySqlCommand(Query);      
cmd.Parameters.Add("@orderType", orderType);      
cmd.Parameters.Add("@order_id", order_id);     
cmd.Connection = sqlconn;
cmd.ExecuteNonQuery();

answered Jul 29, 2019 at 16:06

Giddy Naya

4,7362 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Waheed Rafiq · Accepted Answer · 2019-07-29 16:25:17Z

Here is another example that might help you, a pointer that other developers have mentioned your original code is a probe to SQL injection if you bing search this, there are loads of examples that you can find of what SQL injection is. Here is my method that might assist you. A little code example to assist you.

  public void updateProductTbl(string prodBrand, string description, decimal weight, decimal unitwholesaleprice, decimal unitretailprice, string prodImage, string location, string qrcode,
        string barcode, string suppliercode, int unitinstock, int unitsonorder, int reorderlevel, bool discontinued, decimal unitofmeasure, string prodcategory, int OldValue)
    {
               query = @"update Product 
                SET 
                prod_band=@prodBrand
                ,prod_description=@description
                 ,prod_weight=@weight
                ,prod_perUnitwholesalePrice=@unitwholesaleprice
                ,prod_perUnitRetailPrice = @unitretailprice
                ,prod_Image=@prodImage
                ,prod_location=@location
                ,prod_QRcode=@qrcode
                ,prod_barcode=@barcode
                ,prod_supplierFKCode=@suppliercode
                ,prod_unitsinstock=@unitinstock
                ,prod_unitsonorder=@unitonorder
                ,prod_reorderlevel=@reorderlevel
                ,prod_discontinued=@discontinued
                ,prod_unitofmeasure=@unittofmeasure
                ,prod_category=@prodcategory
                where prod_rec_id=@OldValue";


        try
        {
            myConn.Open();
            SqlCommand myCommand = new SqlCommand(query, myConn);
            myCommand.Parameters.AddWithValue("@prodBrand", prodBrand);
            myCommand.Parameters.AddWithValue("@description", description);
            myCommand.Parameters.AddWithValue("@weight", weight);
            myCommand.Parameters.AddWithValue("@unitwholesaleprice", unitwholesaleprice);
            myCommand.Parameters.AddWithValue("@unitretailprice", unitretailprice);
            myCommand.Parameters.AddWithValue("@prodImage", prodImage);
            myCommand.Parameters.AddWithValue("@location", location);
            myCommand.Parameters.AddWithValue("@qrcode", qrcode);
            myCommand.Parameters.AddWithValue("@barcode", barcode);
            myCommand.Parameters.AddWithValue("@suppliercode", suppliercode);
            myCommand.Parameters.AddWithValue("@unitinstock", unitinstock);
            myCommand.Parameters.AddWithValue("@unitonorder", unitsonorder);
            myCommand.Parameters.AddWithValue("@reorderlevel", reorderlevel);
            myCommand.Parameters.AddWithValue("@discontinued", discontinued);
            myCommand.Parameters.AddWithValue("@unittofmeasure", unitofmeasure);
            myCommand.Parameters.AddWithValue("@prodcategory", prodcategory);
            myCommand.Parameters.AddWithValue("@OldValue", OldValue);

             status = myCommand.ExecuteNonQuery(); // when ExecuteNonQuery method return 1 or 0 if it have saved to sql db

            if (status > 0)
            {
                MessageBox.Show("Your Data has been updated", "Update Data", MessageBoxButton.OK, MessageBoxImage.Information);
            }


        }
        catch(Exception ex)
        {
            MessageBox.Show("SQL Error in Product Add method:"+ex.ToString(), "Warning Data not saved", MessageBoxButton.OK, MessageBoxImage.Error);
        }
        finally
        {
            myConn.Close(); 
        }

    }

Hope the abe gives you a good idea of how to go about SQl and passing params in a method.

Collectives™ on Stack Overflow

Unknown column 'New' in 'field list' while using sql command in the asp.net

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related