Hello everybody,

I deceided to use SQL-Injection as I heard of the security issues that arise.

My problem is that I want to select multiple items of an ListBox and use those as paramaters in a SQL INSERT statement. The thing is, that you can use a paramameter-name only one time in a select statement. I tricked the thing creating a 'new' command object everytime I retrieve another item of the ListBox.

This is not the finest method, I guess. Does anybody know how to solve this issue?

My code looks like this:
...
currentMessageId = ....
int id = 0;

for ( i = 0; i< listBoxUsers.SelectedItems.Count; i+)
{
sqlStr = "";
row = ((DataRowView)this.listBoxUsers.SelectedItems[i]).Row;
id = Convert.ToInt16(row[listBoxUsers.ValueMember]);
sqlStr = "INSERT INTO user_messages (M_Id, User_Id) VALUES (@messageId, @userId)";

command = new SqlCommand (sqlStr, sqlConnectionString);
command.Parameters.AddWithValue("messageId", currentMessageId);
command.Parameters.AddWithValue("userId", id);

command.CommandText = sqlStr;
command.ExecuteNonQuery();
}

is there a way to do an successful insert to the n:m related table without creating a new instance of the command class every time?

Best regards

Firstly, you are not 'using SQL Injection' but avoiding it...

If you create the SqlCommand outside of your loop, and then use SqlCommand.Parameter.Add to create a new SqlParameter object, then simply set its Value and execute the command inside the loop.

__________________
/- Sam Judson : Wrox Technical Editor -/

Ok,

It worked.

I placed the command object outside the loop and used the clear method of the command.parameters object to clear the variable-name.

Thank you.

or u can use Dataset & DataAdapter
u'll add ur items in dataSet\DataTable\DataRow(s) & call Update() method of it
DataAdapter will all make its inserts & ....

__________________
Always[:)],
Hovik Melkomian.

I actually meant this:

__________________
/- Sam Judson : Wrox Technical Editor -/