Batch query exception
Batch query exception
When I execute the following code in C#, I can insert one record without issues. When I have two objects in my collection, I get the following error:
The variable name '@scoreboardId' has already been declared. Variable names must be unique within a query batch or stored procedure
Is there a way to work around this batch exception?
public void insertActiveMonitorsForScoreboard(SqlConnection dbConn, SqlTransaction dbTrans, int scoreboardId,
ObservableCollection<AvailableMonitorBo> availableMonitorsForAddOC)
using (SqlCommand dbCommand = new SqlCommand(CreateAndDisplaySQLStrings.INSERT_SCOREBOARD_MONITORS, dbConn))
dbCommand.Transaction = dbTrans;
foreach (AvailableMonitorBo bo in availableMonitorsForAddOC)
if (bo.IsActive)
dbCommand.Parameters.Add("scoreboardId", SqlDbType.Int).Value = scoreboardId;
dbCommand.Parameters.Add("availableMonitorId", SqlDbType.Int).Value = bo.AvailableMonitorId;
dbCommand.ExecuteNonQuery();
Move the parameter adds out of the
foreach loop.– 500 - Internal Server Error
Sep 18 '18 at 16:09
foreach
2 Answers
2
Try to add the parameters only once and subsequently only change their values.
public void insertActiveMonitorsForScoreboard(SqlConnection dbConn, SqlTransaction dbTrans, int scoreboardId,
ObservableCollection<AvailableMonitorBo> availableMonitorsForAddOC)
using (SqlCommand dbCommand = new SqlCommand(CreateAndDisplaySQLStrings.INSERT_SCOREBOARD_MONITORS, dbConn))
dbCommand.Transaction = dbTrans;
dbCommand.Parameters.Add("scoreboardId", SqlDbType.Int);
dbCommand.Parameters.Add("availableMonitorId", SqlDbType.Int);
foreach (AvailableMonitorBo bo in availableMonitorsForAddOC)
if (bo.IsActive)
dbCommand.Parameters["scoreboardId"].Value = scoreboardId;
dbCommand.Parameters["availableMonitorId"].Value = bo.AvailableMonitorId;
dbCommand.ExecuteNonQuery();
I think there is a typo in your syntax: "dbCommand.Parameters.["scoreboardId"]" should be "dbCommand.Parameters["scoreboardId"]".
– Polyfun
Sep 18 '18 at 16:13
@Polyfun: You're right. I corrected it, thanks!
– sticky bit
Sep 18 '18 at 16:14
This works. I'll accept it when the time limit is up. I didn't use this though. I only had to clear the parameters after the execute like so: myCommand.Parameters.Clear();
– nikotromus
Sep 18 '18 at 16:14
Another approach is to put the SqlCommand inside your loop. This has the advantage that the SqlCommand is completely new for each loop, so nothing is carried over between iterations. This does not matter in this example, but in other cases it might.
public void insertActiveMonitorsForScoreboard(SqlConnection dbConn, SqlTransaction dbTrans, int scoreboardId,
ObservableCollection<AvailableMonitorBo> availableMonitorsForAddOC)
foreach (AvailableMonitorBo bo in availableMonitorsForAddOC)
if (bo.IsActive)
using (SqlCommand dbCommand = new SqlCommand(CreateAndDisplaySQLStrings.INSERT_SCOREBOARD_MONITORS, dbConn))
dbCommand.Transaction = dbTrans;
dbCommand.Parameters.Add("scoreboardId", SqlDbType.Int).Value = scoreboardId;
dbCommand.Parameters.Add("availableMonitorId", SqlDbType.Int).Value = bo.AvailableMonitorId;
dbCommand.ExecuteNonQuery();
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy
See stackoverflow.com/questions/670407/reusing-sqlcommand
– Rup
Sep 18 '18 at 16:08