Variable Name already declared. Variable names must be unique within a query batch or stored procedure

I am trying to loop through and write a series of records to an SQL database.  I successfully write the first record and then it comes back and gives the following error message.

@eadmDate The variable name is already delcared.  Variable names must be unique within a query batch or stored procedure.

Here is the code


Try
                'Write ELA Objective Info record
                subject = "Reading ELA"
                Dim objConnection As SqlConnection = New SqlConnection("server=(local);database=FBISD-TAKS;user id=sa;password=pa55word")
                Dim objCommand As SqlCommand = New SqlCommand
                objCommand.Connection = objConnection

                Dim r4 As Int32

                For r4 = 1 To 51
                    objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentCorrectResp, subject) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@ereadingElaobj,@ereadingElaItemCorrectRespArray,@ereadingElaStudentRespArray, @esubject)"
                    objCommand.Parameters.Add("@eadmDate", SqlDbType.DateTime, 8).Value = madmDate
                    objCommand.Parameters.Add("@estudentId", SqlDbType.Int, 4).Value = studentId
                    objCommand.Parameters.Add("@eteacherId", SqlDbType.Int, 4).Value = teacher1
                    objCommand.Parameters.Add("@esubject", SqlDbType.VarChar, 50).Value = subject
                    objCommand.Parameters.Add("@ereadingElaobj", SqlDbType.Int, 4).Value = readingElaobj(r4)
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = readingElaItemCorrectRespArray(r4)
                    objCommand.Parameters.Add("@ereadingElaStudentRespArray", SqlDbType.VarChar, 50).Value = readingElaStudentRespArray(r4)
                    objConnection.Open()
                    Dim test As String
                    test = objCommand.CommandText.ToString()
                    MsgBox(test)
                    objCommand.ExecuteNonQuery().ToString()
                    objConnection.Close()
                Next
            Catch ex As Exception
                MsgBox(ex.ToString())

            End Try



Thanks
Kenny
kwh3856Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gregoryyoungConnect With a Mentor Commented:
yeah I copy/pasted the wrong thing ...

Dim objCommand As SqlCommand = New SqlCommand
would need to be in the loop as well.
0
 
gregoryyoungCommented:
move the new ...

                For r4 = 1 To 51
*                     objCommand.Connection = objConnection *

that will take care of it.

0
 
gregoryyoungCommented:
it would be better to update the parameters in the command everytime but the above will work.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
gregoryyoungCommented:
also it would be better to open the connection, issue your 50 or so statements then close it.
0
 
kwh3856Author Commented:
Thanks...I will try that
0
 
kwh3856Author Commented:
Gregg, I still get the same error message.  Here is the corrected code.  Did I miss anything?

Thanks
Kenny

Try
                'Write ELA Objective Info record
                subject = "Reading ELA"
                Dim objConnection As SqlConnection = New SqlConnection("server=(local);database=FBISD-TAKS;user id=sa;password=pa55word")
                Dim objCommand As SqlCommand = New SqlCommand
                Dim r4 As Int32
                For r4 = 1 To 51
                    objCommand.Connection = objConnection
                    objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentCorrectResp, subject) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@ereadingElaobj,@ereadingElaItemCorrectRespArray,@ereadingElaStudentRespArray, @esubject)"
                    objCommand.Parameters.Add("@eadmDate", SqlDbType.DateTime, 8).Value = madmDate
                    objCommand.Parameters.Add("@estudentId", SqlDbType.Int, 4).Value = studentId
                    objCommand.Parameters.Add("@eteacherId", SqlDbType.Int, 4).Value = teacher1
                    objCommand.Parameters.Add("@esubject", SqlDbType.VarChar, 50).Value = subject
                    objCommand.Parameters.Add("@ereadingElaobj", SqlDbType.Int, 4).Value = readingElaobj(r4)
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = readingElaItemCorrectRespArray(r4)
                    objCommand.Parameters.Add("@ereadingElaStudentRespArray", SqlDbType.VarChar, 50).Value = readingElaStudentRespArray(r4)
                    objConnection.Open()
                    Dim test As String
                    test = objCommand.CommandText.ToString()
                    MsgBox(test)
                    objCommand.ExecuteNonQuery().ToString()
                    objConnection.Close()
                Next
            Catch ex As Exception
                MsgBox(ex.ToString())

            End Try
0
 
kwh3856Author Commented:
You ROCK!!!!!!!!!!!!!

Thanks.....Here is the final code

Try
                'Write ELA Objective Info record
                subject = "Reading ELA"
                Dim objConnection As SqlConnection = New SqlConnection("server=(local);database=FBISD-TAKS;user id=sa;password=pa55word")
                Dim r4 As Int32
                objConnection.Open()
                For r4 = 1 To 51
                    Dim objCommand As SqlCommand = New SqlCommand
                    objCommand.Connection = objConnection
                    objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentCorrectResp, subject) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@ereadingElaobj,@ereadingElaItemCorrectRespArray,@ereadingElaStudentRespArray, @esubject)"
                    objCommand.Parameters.Add("@eadmDate", SqlDbType.DateTime, 8).Value = madmDate
                    objCommand.Parameters.Add("@estudentId", SqlDbType.Int, 4).Value = studentId
                    objCommand.Parameters.Add("@eteacherId", SqlDbType.Int, 4).Value = teacher1
                    objCommand.Parameters.Add("@esubject", SqlDbType.VarChar, 50).Value = subject
                    objCommand.Parameters.Add("@ereadingElaobj", SqlDbType.Int, 4).Value = readingElaobj(r4)
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = readingElaItemCorrectRespArray(r4)
                    objCommand.Parameters.Add("@ereadingElaStudentRespArray", SqlDbType.VarChar, 50).Value = readingElaStudentRespArray(r4)
                    Dim test As String
                    test = objCommand.CommandText.ToString()
                    MsgBox(test)
                    objCommand.ExecuteNonQuery().ToString()
                Next
                objConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString())

            End Try
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.