IndexOutOfRange Exception on SQL Parameter 1

[MeonR]

Hello All:

I am trying to get a webcontrols DataGrid(NET2.0) to update/delete. I have this code in the DeleteCommand event in the code behind and its throwing this exception:

System.IndexOutOfRangeException: An SqlParameter with ParameterName '@RowID' is not contained by this SqlParameterCollection.



SqlConnection conn = new SqlConnection(connString);
SqlCommand mySqlCommand = conn.CreateCommand();
mySqlCommand.Parameters["@RowID"].Value = DataGrid1.DataKeys[e.Item.ItemIndex]; //Throws //IndexOutOfRange exception
mySqlCommand.CommandText = "DELETE * FROM CashierMain WHERE RowID= @RowID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.DeleteCommand = mySqlCommand;
.
conn.Open();
DataSet myDataSet = new DataSet();
mySqlDataAdapter.Fill(myDataSet, "CashierMain");

myDataSet.Tables["CashierMain"].Rows[e.Item.ItemIndex].Delete();
mySqlDataAdapter.Update(myDataSet, "CashierMain");
myDataSet.AcceptChanges();
GetCashierData(); //Rebind
Any ideas?
Thanks
MeonR

"The beatings will continue until morale improves

 

[jmeckley]

var id = DataGrid1.DataKeys[e.Item.ItemIndex];
mySqlCommand.Parameters["RowID"].Value = id;

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[MeonR]

Thanks:

I added this:

string rowID = (string) DataGrid1.DataKeys[e.Item.ItemIndex];
mySqlCommand.Parameters["@RowID"].Value = rowID;

Throws:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

What am I doing wrong?
MeonR

"The beatings will continue until morale improves

 

[jmeckley]

remove the @ symbol.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[MeonR]

Thanks:

Yes I tried that still does not work, throws the same exception.

MeonR

"The beatings will continue until morale improves

 

[MeonR]

Thanks

Yes I tried that. same exception.At this point I think this thing is good for displaying data only, for anything else, its worthless. DataKeys is the only way, as far as I can tell of getting at the row to delete/update etc. But it just does not work. I have been 3 days fooling with this thing and nothing.

I would love to hear from anybody who has actually gotten this to work.

Thanks
MeonR



"The beatings will continue until morale improves

 

[jmeckley]

I can't believe I missed this before. The problem is you are trying to set a value on a parameter that doesn't exist in parameter collection. you need to add the parameter. try this instead

var id = DataGrid1.DataKeys[e.Item.ItemIndex];
mySqlCommand.AddParameterWithValue("RowID",id);

or

var id = DataGrid1.DataKeys[e.Item.ItemIndex];
var parameter = mySqlCommand.CreateParameter("RowID");
parameter.Value = id;
mySqlCommand.Parameters.Add(parameter);

or something like that.
once the parameter is added to the command's parameter collection you can reference it like

mySqlCommand.Parameters["RowID"]

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[MeonR]

Jason

Thanks! Here is what I came up with, this sort of works but now it deletes everything in the table!

SqlConnection conn = new SqlConnection(connString);
System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
string id = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
dataCommand.Parameters.AddWithValue("RowID", id);
dataCommand.Connection = conn;
dataCommand.Parameters["RowID"].Value = id;
dataCommand.CommandText = "DELETE FROM CashierMain WHERE RowID= RowID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.DeleteCommand = dataCommand;
conn.Open();
dataCommand.ExecuteNonQuery();
GetCashierData();

"The beatings will continue until morale improves

 

[MeonR]

Ok got it this time the command text should be

dataCommand.CommandText = "DELETE FROM CashierMain WHERE RowID= '" + id + "'";

MeonR

"The beatings will continue until morale improves

 

[jmeckley]

that is injected sql and it's the worst way you can generate sql statements. stick with the parameters

dataCommand.Parameters.AddWithValue("RowID", id);
dataCommand.CommandText = "DELETE  FROM CashierMain WHERE RowID= [COLOR=blue]@[/color]RowID";

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[MeonR]

Thanks

Yes, previously that was not working, now it does???

MeonR

"The beatings will continue until morale improves

 

[whosrdaddy]

Yes, previously that was not working, now it does???

because now you added the RowId parameter

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!