Parameterized Query Error

[jellybeannn]

I'm trying to insert a record from a DataGridView into a database, but I receive this error:
Parameterized Query '(@i1 int,@i2 varchar(50),@i3 varchar(50),@i4 varchar(50),@i5 var' expects parameter @i1, which was not supplied.

I have tested with a BreakPoint that c_id is not null, it does have a value.

*Note c_id is only a link to another table, it is not the invoice tables id.

string reference = "INV" + Random();

            string status = "0";

            SqlConnection cnn = new SqlConnection(connectionString);

            string sqlInsert =
                "INSERT INTO invoice(c_id, date_added, customer, status, items, description, quantity, unit_value, total, notes, notes_internal, ref, payment_ref) values(@i1,@i2,@i3,@i4,@i5,@i6,@i7,@i8,@i9,@i10,@i11,@i12,@i13)";
      
            SqlParameter[] pInsert = new SqlParameter[13];

            pInsert[0] = new SqlParameter("@i1", SqlDbType.Int, 5, customer_dd.SelectedValue.ToString());
            pInsert[1] = new SqlParameter("@i2", SqlDbType.VarChar, 50, DateTime.Today.ToShortDateString());
            pInsert[2] = new SqlParameter("@i3", SqlDbType.VarChar, 50, customer_dd.Text);
            pInsert[3] = new SqlParameter("@i4", SqlDbType.VarChar, 50, status);
            pInsert[4] = new SqlParameter("@i5", SqlDbType.VarChar, 50, "Item");
            pInsert[5] = new SqlParameter("@i6", SqlDbType.VarChar, 50, "Descrioption");
            pInsert[6] = new SqlParameter("@i7", SqlDbType.VarChar, 50, "Qty");
            pInsert[7] = new SqlParameter("@i8", SqlDbType.VarChar, 50, "Unit_Value");
            pInsert[8] = new SqlParameter("@i9", SqlDbType.VarChar, 50, "Line_Total");
            pInsert[9] = new SqlParameter("@i10", SqlDbType.VarChar, 50, notes_txt.Text);
            pInsert[10] = new SqlParameter("@i11", SqlDbType.VarChar, 50, internal_txt.Text);
            pInsert[11] = new SqlParameter("@i12", SqlDbType.VarChar, 50, reference);
            pInsert[12] = new SqlParameter("@i13", SqlDbType.VarChar, 50, null);

            SqlCommand cmdInsert = new SqlCommand(sqlInsert,cnn);
     
            cmdInsert.Parameters.AddRange(pInsert);
            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand  = cmdInsert;
            da.Update(ds, "invoice");
            ds.AcceptChanges();

[rohand]

I believe the error is from below statement you have written :

pInsert[0] = new SqlParameter("@i1", SqlDbType.Int, 5, customer_dd.SelectedValue.ToString());

Look the red highlighted portion, you have defined the datatype as "Int" and you are trying to store string value (see the green highlighte portion).

If you want to store Int then you must have to convert the SelectedValue to Integer.

Try it and let us know..

I'm trying to insert a record from a DataGridView into a database, but I receive this error:
Parameterized Query '(@i1 int,@i2 varchar(50),@i3 varchar(50),@i4 varchar(50),@i5 var' expects parameter @i1, which was not supplied.

I have tested with a BreakPoint that c_id is not null, it does have a value.

*Note c_id is only a link to another table, it is not the invoice tables id.

string reference = "INV" + Random();

            string status = "0";

            SqlConnection cnn = new SqlConnection(connectionString);

            string sqlInsert =
                "INSERT INTO invoice(c_id, date_added, customer, status, items, description, quantity, unit_value, total, notes, notes_internal, ref, payment_ref) values(@i1,@i2,@i3,@i4,@i5,@i6,@i7,@i8,@i9,@i10,@i11,@i12,@i13)";
      
            SqlParameter[] pInsert = new SqlParameter[13];

            pInsert[0] = new SqlParameter("@i1", SqlDbType.Int, 5, customer_dd.SelectedValue.ToString());
            pInsert[1] = new SqlParameter("@i2", SqlDbType.VarChar, 50, DateTime.Today.ToShortDateString());
            pInsert[2] = new SqlParameter("@i3", SqlDbType.VarChar, 50, customer_dd.Text);
            pInsert[3] = new SqlParameter("@i4", SqlDbType.VarChar, 50, status);
            pInsert[4] = new SqlParameter("@i5", SqlDbType.VarChar, 50, "Item");
            pInsert[5] = new SqlParameter("@i6", SqlDbType.VarChar, 50, "Descrioption");
            pInsert[6] = new SqlParameter("@i7", SqlDbType.VarChar, 50, "Qty");
            pInsert[7] = new SqlParameter("@i8", SqlDbType.VarChar, 50, "Unit_Value");
            pInsert[8] = new SqlParameter("@i9", SqlDbType.VarChar, 50, "Line_Total");
            pInsert[9] = new SqlParameter("@i10", SqlDbType.VarChar, 50, notes_txt.Text);
            pInsert[10] = new SqlParameter("@i11", SqlDbType.VarChar, 50, internal_txt.Text);
            pInsert[11] = new SqlParameter("@i12", SqlDbType.VarChar, 50, reference);
            pInsert[12] = new SqlParameter("@i13", SqlDbType.VarChar, 50, null);

            SqlCommand cmdInsert = new SqlCommand(sqlInsert,cnn);
     
            cmdInsert.Parameters.AddRange(pInsert);
            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand  = cmdInsert;
            da.Update(ds, "invoice");
            ds.AcceptChanges();

[dnanetwork]

SqlCommand comm = new SqlCommand();
         comm.CommandType = CommandType.Text;
         comm.Connection = conn;

         comm.CommandText = "update users set 
user_name=@user_name,user_first=@user_first, user_last=@user_last 
where user_id=@user_id";

        comm.Parameters.AddWithValue
("@user_name",user_name.Text.ToString());

        comm.Parameters.AddWithValue
("@user_first",first_name.Text.ToString());

        comm.Parameters.AddWithValue
("@user_last",last_name.Text.ToString());

        comm.Parameters.AddWithValue("@user_id", int.Parse
(user_id.Text.ToString()));

        conn.Open();
         comm.ExecuteNonQuery();
        conn.Close();

         GridView1.EditIndex = -1;
        BindGridView();

Bad Advice but try to use your parameter using command object instead of using
Adapter.

let see if it works out..

[jellybeannn]

Thanks for all the replys.

rohand
I tried what you suggested but still get the error.
pInsert[0] = new SqlParameter("@i1", SqlDbType.VarChar, 5, customer_dd.SelectedValue.ToString());
pInsert[0] = new SqlParameter("@i1", SqlDbType.Int, 5, Int32.Parse(customer_dd.SelectedValue.ToString());

dnanetwork
In your code you are updating the record, I want to add a new record. Will this work for adding?

[jellybeannn]

SqlConnection cnn = new SqlConnection(connectionString);

            SqlCommand comm = new SqlCommand();
            comm.CommandType = CommandType.Text;
            comm.Connection = cnn;

            comm.CommandText = "INSERT INTO invoice (c_id=@c_id, date_added=@date_added, customer=@customer, status=@status, items=@items, description=@description, quantity=@quantity, unit_value=@unit_value, total=@total, notes=@notes, notes_internal=@notes_internal, ref=@ref, payment_ref=@payment_ref)";

            comm.Parameters.AddWithValue("@c_id", Int32.Parse(customer_dd.SelectedValue.ToString()));
            comm.Parameters.AddWithValue("@date_added", DateTime.Today.ToShortDateString());
            comm.Parameters.AddWithValue("@customer", customer_dd.Text);
            comm.Parameters.AddWithValue("@status", Int32.Parse(status));
            comm.Parameters.AddWithValue("@items", "Item");
            comm.Parameters.AddWithValue("@description", "Descrioption");
            comm.Parameters.AddWithValue("@quantity", "Qty");
            comm.Parameters.AddWithValue("@unit_value", "Unit_Value");
            comm.Parameters.AddWithValue("@total", "Line_Total");
            comm.Parameters.AddWithValue("@notes", notes_txt.Text);
            comm.Parameters.AddWithValue("@notes_internal", internal_txt.Text);
            comm.Parameters.AddWithValue("@ref", reference);
            comm.Parameters.AddWithValue("@payment_ref", null);

            cnn.Open();
            comm.ExecuteNonQuery();
            cnn.Close();

now I receive the error:
Incorrect syntax near '='.

[dnanetwork]

everything cool except i think your insert syntax is wrong..

it should be insert into yourtable (fields) values (field parameters);

only change the query, it will work.

[jellybeannn]

Cool thanks alot!

[crishjeny]

hi

Its a very good question but i have one example.you can refer it and i think it will help you.

With SQL Server:

With cmd.Parameters:
.Add(New SQLParameter("@Firstname", frmFname.text))
.Add(New SQLParameter("@LastName", frmLname.text))
.Add(New SQLParameter("@Address", frmAddress.text))
.Add(New SQLParameter("@City", frmCity.text))
.Add(New SQLParameter("@state", frmState.text))
.Add(New SQLParameter("@Postalcode", frmPostalCode.Text))
.Add(New SQLParameter("@Phone", frmPhone.text))
.Add(New SQLParameter("@email", frmemail.text))
end with

With OleDb:

With cmd.Parameters:
.Add(New OleDbParameter("@Firstname", frmFname.text))
.Add(New OleDbParameter("@LastName", frmLname.text))
.Add(New OleDbParameter("@Address", frmAddress.text))
.Add(New OleDbParameter("@City", frmCity.text))
.Add(New OleDbParameter("@state", frmState.text))
.Add(New OleDbParameter("@Postalcode", frmPostalCode.Text))
.Add(New OleDbParameter("@Phone", frmPhone.text))
.Add(New OleDbParameter("@email", frmemail.text))
end with