?
Solved

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

Posted on 2004-09-08
7
Medium Priority
?
1,810 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
  • 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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
Suggested Courses
Course of the Month5 days, 10 hours left to enroll

589 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