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: https://stackoverflow.com/questions/1346209/unknown-column-in-field-list-error-on-mysql-update-query — J.K, Jul 29 '19 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, Jul 29 '19 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. — J.K, Jul 29 '19 at 15:51
Stop trying to assemble SQL using string concatenation and [use parameters](https://stackoverflow.com/questions/7505808/). That will fix your current problem, and many future problems. — Dour High Arch, Jul 29 '19 at 15:53

score 5 · Accepted Answer · answered Jul 29 '19 at 16:06

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

score 2 · Answer 2 · answered Jul 29 '19 at 16:25

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.

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

2 Answers2