I have a time sheet app where users enter their time in/out for different days of the week. The form processes the in/out from each day, stuff them as parameters into a stored procedure and add them to the database. How would I accomplish this most efficiently? I don't have access to the DB, just the stored procedures.
This is the bare code behind, I've stripped out some unnecessary codes.
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("insertINOUT", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserName", user));
for (int j = 0; j < weekDays.Length; j++)
{
cmd.Parameters.Add(new SqlParameter("@In", in));
cmd.Parameters.Add(new SqlParameter("@Out", out));
cmd.ExecuteReader();
}
conn.Close();
The code works if there's only 1 day of in/out. If the users fill out multiple days, I'll get this error: Parameter '@In' was supplied multiple times.
Thanks for your help.
A procedure cannot be executed more than once. 4. ALOGO procedure has two parts.
They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.
Setting up multiple parameters is very easy to do. You just need to list each parameter and the data type separated by a comma as shown below.
Click on your database and expand “Programmability” and right click on “Stored Procedures” or press CTRL+N to get new query window. You can write the SELECT query in between BEGIN and END to get select records from the table.
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("insertINOUT", conn);
cmd.CommandType = CommandType.StoredProcedure;
for (int j = 0; j < weekDays.Length; j++)
{
**cmd.Parameters.Clear();**
cmd.Parameters.Add(new SqlParameter("@UserName", user));
cmd.Parameters.Add(new SqlParameter("@In", in));
cmd.Parameters.Add(new SqlParameter("@Out", out));
cmd.ExecuteReader();
}
conn.Close();
(You have to clear the parameters each iteration.)
Another alternative, you could change the scope of the SqlCommand so that it is recreated each time.
SqlConnection conn = new SqlConnection(connString);
conn.Open();
for (int j = 0; j < weekDays.Length; j++)
{
SqlCommand cmd = new SqlCommand("insertINOUT", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserName", user));
cmd.Parameters.Add(new SqlParameter("@In", in));
cmd.Parameters.Add(new SqlParameter("@Out", out));
cmd.ExecuteReader();
}
conn.Close();
Seems a bit wasteful, but there are some libraries that work this way (the Enterprise Library DAAB comes to mind).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With