sql update statement with dynamic values within for loop

[msdnpublic1234]

Hi,

I am trying to update a column in the table for every row from the adapter table as below,but i face an error "must declare the "@req"" .The intention is process every row from table,add/remove user from Active directory and update the table.Following is the code;

string connectionString;
OleDbConnection cnn;

connectionString = @"Data Source=xyz1234;Provider=SQLOLEDB;Initial Catalog=ADUsers;Integrated Security=SSPI";

cnn = new OleDbConnection(connectionString);

cnn.Open();
Console.Write("Connection Made");
var context = new PrincipalContext(ContextType.Domain, "xyz", "svcaccount", "abcd123");

var table = new DataTable();
var adapter = new OleDbDataAdapter();
adapter.Fill(table, Dts.Variables["User::Users"].Value);
foreach (DataRow row in table.Rows)
{
string Request = row["RequestType"].ToString();
string RequestFor = row["Submitted_For"].ToString();
string User = row["sAMAccountName"].ToString();
string AD = row["ADGroupName"].ToString();
string Status= row["ApprovalStatus"].ToString();
DateTime ReqDate = Convert.ToDateTime(row["RDate"]);

if (Request=="Add")
{
var groupname = GroupPrincipal.FindByIdentity(context, AD);
AddUserToGroup(context, groupname, User);
}
else
{
var groupname = GroupPrincipal.FindByIdentity(context, AD);
RemoveUserFromGroup(context, groupname, User);
}
OleDbCommand command;

OleDbDataAdapter adapter_u = new OleDbDataAdapter();
string sql = "";

sql = "update BI_UserPermissionList set ProcessedToAD='yes' where RequestType=@req and ADGroupName like '%'+@grp+'%' and Submitted_For=@reqFor and ApprovalStatus=@status" ;
command = new OleDbCommand(sql, cnn);

command.Parameters.Add("@req",OleDbType.VarChar).Value=Request;

command.Parameters.AddWithValue("@reqFor",RequestFor);
command.Parameters.AddWithValue("@status", Status);
command.Parameters.AddWithValue("@grp", "%" + AD + "%");
adapter.UpdateCommand = new OleDbCommand(sql,cnn);
adapter.UpdateCommand.ExecuteNonQuery();
command.Dispose();
}
cnn.Close();

I tried to fix it for @req by using command.Parameters.Add("@req",OleDbType.VarChar).Value=Request;

but it does not work.Please help me fix this so I can replicate the same from other @reqFor,@status,@grp

Continue reading...