Solved

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

Posted on 2004-09-08
7
1,773 Views
Last Modified: 2009-02-17
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
0
Comment
Question by:kwh3856
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 12009827
move the new ...

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

that will take care of it.

0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 12009829
it would be better to update the parameters in the command everytime but the above will work.
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 12009831
also it would be better to open the connection, issue your 50 or so statements then close it.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kwh3856
ID: 12009872
Thanks...I will try that
0
 

Author Comment

by:kwh3856
ID: 12010085
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
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 12010108
yeah I copy/pasted the wrong thing ...

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

Author Comment

by:kwh3856
ID: 12010198
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question