Batch query exception

Question

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();
            }                   
        }               
    }
}

See https://stackoverflow.com/questions/670407/reusing-sqlcommand — Rup, Sep 18 '18 at 16:08

sticky bit · Accepted Answer · 2018-09-18T16:13:58.617

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
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

Polyfun · Answer 2 · 2018-09-19T09:24:52.380

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();
          }                   
       }               
    }
}

Batch query exception

2 Answers2