Hello, I need to call a stored procedure repeatedly in a loop. most likely under 10 loop iterations.
What do I need to do to clear or refresh/clear the SqlCommand object named cmdUploadDoc? I need to do that in order
to store different parameter values each loop iteration.
SqlCommand cmd = new SqlCommand(strQuery, conn);
string connStringEr = ConfigurationManager.ConnectionStrings["EMRConnectionString"].ConnectionString;
string strDocType;
SqlCommand cmdUploadDoc;
SqlConnection theConn = new SqlConnection(connStringEr);
// rewrite everthing to the database that is in the grid
System.Guid g = new Guid(HiddenTextBox.Text.ToString());
for (int i = 0; i < rowCount; i++)
{
cmdUploadDoc = new SqlCommand("UpdateEditor", theConn);
cmdUploadDoc.CommandType = CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Add("@tblUserIdParam", SqlDbType.VarChar, 50);
cmdUploadDoc.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier);
cmdUploadDoc.Parameters[0].Value = strParameterName[i, 0].ToString();
Before you start to add the parameters, use "cmdUploadDoc.Parameters.Clear()". This will clear out all of the parameters so that you can add some new ones.
SqlCommand cmd = new SqlCommand(strQuery, conn);
string connStringEr = ConfigurationManager.ConnectionStrings["EMRConnectionString"].ConnectionString;
string strDocType;
SqlCommand cmdUploadDoc;
SqlConnection theConn = new SqlConnection(connStringEr);
// rewrite everthing to the database that is in the grid
System.Guid g = new Guid(HiddenTextBox.Text.ToString());
for (int i = 0; i < rowCount; i++)
{
cmdUploadDoc = new SqlCommand("UpdateEditor", theConn);
cmdUploadDoc.CommandType = CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Clear();
cmdUploadDoc.Parameters.Add("@tblUserIdParam", SqlDbType.VarChar, 50);
cmdUploadDoc.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier);
cmdUploadDoc.Parameters[0].Value = strParameterName[i, 0].ToString();
cmdUploadDoc.Parameters[1].Value = g;
theConn.Open();
cmdUploadDoc.ExecuteNonQuery();
theConn.Close();
}
Please mark anything that helped you out as an answer.
If you needed VB.NET and I gave you see C#, convert your code here.
brgdotnet
Participant
1202 Points
724 Posts
How do I clear/refresh my sqlCommand object for each loop iteration
Jul 16, 2009 03:03 PM|LINK
Hello, I need to call a stored procedure repeatedly in a loop. most likely under 10 loop iterations.
What do I need to do to clear or refresh/clear the SqlCommand object named cmdUploadDoc? I need to do that in order
to store different parameter values each loop iteration.
SqlCommand cmd = new SqlCommand(strQuery, conn);
string connStringEr = ConfigurationManager.ConnectionStrings["EMRConnectionString"].ConnectionString;
string strDocType;
SqlCommand cmdUploadDoc;
SqlConnection theConn = new SqlConnection(connStringEr);
// rewrite everthing to the database that is in the grid
System.Guid g = new Guid(HiddenTextBox.Text.ToString());
for (int i = 0; i < rowCount; i++)
{
cmdUploadDoc = new SqlCommand("UpdateEditor", theConn);
cmdUploadDoc.CommandType = CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Add("@tblUserIdParam", SqlDbType.VarChar, 50);
cmdUploadDoc.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier);
cmdUploadDoc.Parameters[0].Value = strParameterName[i, 0].ToString();
cmdUploadDoc.Parameters[1].Value = g;
theConn.Open();
cmdUploadDoc.ExecuteNonQuery();
theConn.Close();
}
DrivenTooFar
Participant
1548 Points
256 Posts
Re: How do I clear/refresh my sqlCommand object for each loop iteration
Jul 16, 2009 03:07 PM|LINK
Before you start to add the parameters, use "cmdUploadDoc.Parameters.Clear()". This will clear out all of the parameters so that you can add some new ones.
SqlCommand cmd = new SqlCommand(strQuery, conn); string connStringEr = ConfigurationManager.ConnectionStrings["EMRConnectionString"].ConnectionString; string strDocType; SqlCommand cmdUploadDoc; SqlConnection theConn = new SqlConnection(connStringEr); // rewrite everthing to the database that is in the grid System.Guid g = new Guid(HiddenTextBox.Text.ToString()); for (int i = 0; i < rowCount; i++) { cmdUploadDoc = new SqlCommand("UpdateEditor", theConn); cmdUploadDoc.CommandType = CommandType.StoredProcedure; cmdUploadDoc.Parameters.Clear(); cmdUploadDoc.Parameters.Add("@tblUserIdParam", SqlDbType.VarChar, 50); cmdUploadDoc.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier); cmdUploadDoc.Parameters[0].Value = strParameterName[i, 0].ToString(); cmdUploadDoc.Parameters[1].Value = g; theConn.Open(); cmdUploadDoc.ExecuteNonQuery(); theConn.Close(); }If you needed VB.NET and I gave you see C#, convert your code here.