Error in Update query [Using String.Format]

Question

I am trying to write an update statement for inserting data from asp.net gridview to sql server 2005 database.but it is showing me an error, Please tell me how to solve.

cmdUpdate.CommandText = String.Format("Update Products SET ProductName=
{0},UnitsInStock={1},UnitsOnOrder={2},ReorderLevel={3} WHERE ProductID={4} AND 
SupplierID={5}", "productname.Text, unitsinstock.Text, unitsonorder.Text,  
recorderlevel.Text, employeeid.Text, supplierid.Text");

Error is- Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Where the quotes for the string? – leppie Sep 30 '11 at 08:07 — leppie, Sep 30 '11 at 08:07

codefrenzy · Answer 1 · 2011-09-30T08:24:41.103

Your syntax for string.Format is incorrect - each parameter after the string template should be on their own, without the double quotes surrounding them all...

This will work (notice I've removed the double quotes from just before 'productname.Text' and after 'supplierid.Text'):

String.Format("Update Products SET ProductName={0}, UnitsInStock={1}, UnitsOnOrder={2}, ReorderLevel={3} WHERE ProductID={4} AND SupplierID={5}", 
    productname.Text, unitsinstock.Text, unitsonorder.Text, 
    recorderlevel.Text, employeeid.Text, supplierid.Text);

score 0 · Accepted Answer · edited May 23 '17 at 09:59

You missed the arguments,

For instance,

str=String.Format("{0} {1}",arg1,arg2);

Do not use hard-coded sql strings. Try to learn/use parameterized queries.

EDIT:

string ConnectionString = "put_connection_string";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        string sql = "Update Products SET 
                ProductName=@ProductName,
                UnitsInStock=@UnitsInStock,
                UnitsOnOrder=@UnitsOnOrder,
                ReorderLevel=ReorderLevel 
                WHERE ProductID=ProductID AND SupplierID=@SupplierID";
        cmd.CommandText = sql;
        cmd.Connection = con;
        cmd.Parameters.Add("@ProductName", System.Data.SqlDbType.VarChar, 50).Value =productname.Text;
        cmd.Parameters.Add("@UnitsInStock", System.Data.SqlDbType.Int).Value =unitsinstock.Text;
        cmd.Parameters.Add("@UnitsOnOrder", System.Data.SqlDbType.Int).Value =unitsonorder.Text;
        cmd.Parameters.Add("@ReorderLevel ", System.Data.SqlDbType.Int).Value =recorderlevel.Text;
        cmd.Parameters.Add("@ProductID", System.Data.SqlDbType.Int).Value =producteid.Text;
        cmd.Parameters.Add("@SupplierID", System.Data.SqlDbType.Int).Value =supplierid.Text;

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

EDIT: What is C# using block?

Can u please show me an example for my above qyery
do i need to use stored procedure with input parameters or I can simply use text type of queries. — Ajit D, Sep 30 '11 at 08:33
>Thanks a lot for your comment.> I always use to thought this way is used with Stored Procedures only. like this one SqlCommand cmd = new SqlCommand("SearchResult",con); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = txtInputRollNo.Text; — Ajit D, Sep 30 '11 at 09:51
@AVD---I always used to write programs like SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["myConnectionString"]); con.Open(); SqlCommand cmd = new SqlCommand("SearchResult",con); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = txtInputRollNo.Text; SqlDataAdapter da = new SqlDataAdapter(cmd); But why, some people used to write using “using”.. Like using (SqlConnection con = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand()) { — Ajit D, Sep 30 '11 at 10:00

Error in Update query [Using String.Format]

2 Answers2