Solved

sql server variable names already declared in vb.net

Posted on 2016-07-19
16
52 Views
Last Modified: 2016-07-19
I have made this for loop that works ok and grabs the data i need.

within the loop it wont update the table as it says variables already declared, it says this whatever I rename them too:

not sure what to do about that error.

 For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record

                    '********************************************************************************************************

                    cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "

                    cmdInsertSmmt.Parameters.AddWithValue("@ClientCode", drAccessRecord("MVRIS CODE"))
                    cmdInsertSmmt.Parameters.AddWithValue("@VehCategory", drAccessRecord("Vehicle Category Code"))
                    cmdInsertSmmt.ExecuteNonQuery()



                Next

Open in new window

0
Comment
Question by:PeterBaileyUk
[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
  • 11
  • 4
16 Comments
 
LVL 84
ID: 41719063
Try this syntax instead:

 cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "

cmdInsertSmmt.Parameters.Add("@ClientCode", SqlDBType.VarChar, 50).Value =  drAccessRecord("MVRIS CODE")
cmdInsertSmmt.Parameters.Add("@VehCategory",SqlDBType.VarChar, 50).value = drAccessRecord("Vehicle Category Code")

Obviously you'd change the "SqlDBType.VarChar" to reflect the actual datatypes.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 500 total points
ID: 41719070
Do you do :

cmdInsertSmmt = New SqlCommand()

within the loop, so that parameters are new in every loop?
0
 

Author Comment

by:PeterBaileyUk
ID: 41719078
@ Valliappan

I dont believe i have here is the part above the for loop, so does that need to go within the loop?

 Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")

            Using cmdInsertSmmt As New SqlClient.SqlCommand
                cmdInsertSmmt.Connection = cnSqlSmmt
                cnSqlSmmt.Open()
                Dim y As Long

                y = dtRecordsFromAccessSmmt.Rows.Count
                For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:PeterBaileyUk
ID: 41719094
its still giving the error despite those amendments

    Public Sub UpdateVehicleCat()
        Dim dtRecordsFromAccessSmmt As DataTable

        Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\SMMT\smmt master.mdb")
            Using cmdSelect As New OleDb.OleDbCommand
                cmdSelect.Connection = cnAccess
                cnAccess.Open()
                cmdSelect.CommandText = "SELECT [MVRIS CODE], [Vehicle Category Code] FROM smmt;"
                Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                dtRecordsFromAccessSmmt = New DataTable
                dtAdpt.Fill(dtRecordsFromAccessSmmt)
            End Using
        End Using


        Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")

            Using cmdInsertSmmt As New SqlClient.SqlCommand
                cmdInsertSmmt.Connection = cnSqlSmmt
                cnSqlSmmt.Open()
                Dim y As Long

                y = dtRecordsFromAccessSmmt.Rows.Count
                For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record

                    cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
                    cmdInsertSmmt.Parameters.Add("@ClientCode", SqlDbType.NVarChar, 50).Value = drAccessRecord("MVRIS CODE")
                    cmdInsertSmmt.Parameters.Add("@VehCategory", SqlDbType.NVarChar, 50).Value = drAccessRecord("Vehicle Category Code")
                    cmdInsertSmmt.ExecuteNonQuery()
                Next

            End Using
            cnSqlSmmt.Close()
        End Using

    End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41719110
I stepped through and the error was on the 2nd record, so maybe thats because of what  Valliappan said
0
 

Author Comment

by:PeterBaileyUk
ID: 41719117
I believe its now working
        Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")



            Dim y As Long

                y = dtRecordsFromAccessSmmt.Rows.Count
                For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
                Using cmdInsertSmmt As New SqlClient.SqlCommand
                    cmdInsertSmmt.Connection = cnSqlSmmt
                    cnSqlSmmt.Open()
                    cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "

                    cmdInsertSmmt.Parameters.Add("@ClientCode", SqlDbType.NVarChar, 50).Value = drAccessRecord("MVRIS CODE")
                    cmdInsertSmmt.Parameters.Add("@VehCategory", SqlDbType.NVarChar, 50).Value = drAccessRecord("Vehicle Category Code")
                    cmdInsertSmmt.ExecuteNonQuery()
                    cnSqlSmmt.Close()
                End Using
            Next

        End Using



    End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41719148
I checked and in some cases it populates in others it doesnt, the for loop has the right count.
0
 

Author Comment

by:PeterBaileyUk
ID: 41719155
I debugged with a smaller set of access records just using one record that failed to update previously, that record updated.

it must be the sequence in the for loop with the command objects.

I just do not know what.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 41719252
Is the issue resolved ?
0
 

Author Comment

by:PeterBaileyUk
ID: 41719284
i am just running it again but it does not appear to get to the end statement I had it running for 40 minutes.

Public Sub UpdateVehicleCat()
        Dim dtRecordsFromAccessSmmt As DataTable

        Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\SMMT\smmt master.mdb")
            Using cmdSelect As New OleDb.OleDbCommand
                cmdSelect.Connection = cnAccess
                cnAccess.Open()
                cmdSelect.CommandText = "SELECT [MVRIS CODE], [Vehicle Category Code] FROM smmt where [Vehicle Category Code] is not null ORDER BY [MVRIS CODE];"
                Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                dtRecordsFromAccessSmmt = New DataTable
                dtAdpt.Fill(dtRecordsFromAccessSmmt)
            End Using
        End Using


        Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")



            Dim y As Long



            y = dtRecordsFromAccessSmmt.Rows.Count

                For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
                Using cmdInsertSmmt As New SqlClient.SqlCommand
                    cmdInsertSmmt.Connection = cnSqlSmmt
                    cnSqlSmmt.Open()
                    cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
                    cmdInsertSmmt.Parameters.Add("@ClientCode", SqlDbType.NVarChar, 50).Value = drAccessRecord("MVRIS CODE")
                    cmdInsertSmmt.Parameters.Add("@VehCategory", SqlDbType.NVarChar, 50).Value = drAccessRecord("Vehicle Category Code")
                    cmdInsertSmmt.ExecuteNonQuery()


                    cnSqlSmmt.Close()




                End Using
            Next



        End Using
        MessageBox.Show("Finished Update Vehicle Category.")
    End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 41719313
How many records are you trying to update, and how many is updated so far?
0
 

Author Comment

by:PeterBaileyUk
ID: 41719320
There are just under 700000 rows so i think i will have to do this a different way as its poiible its working but its just slow.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 41719346
If its one time, you may import the Access table to SQL using SQL Import/Export tool, to a table and then use joins to update it in one sql statement, will be very fast.

Another option is to use, OpenDataSource, or OpenQuery, or Linked Servers. But i would suggest above option.

Thanks.
0
 

Author Comment

by:PeterBaileyUk
ID: 41719384
I will append the data to a smaller table, I think its almost there its the syntax thats causing an exception

  cmdInsertSmmt.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory) VALUES ('" & drAccessRecord("MVRIS CODE") & "', '" & drAccessRecord("Vehicle Category Code") & "','" & " )"


                    cmdInsertSmmt.ExecuteNonQuery()

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41719419
finally got it with
 cmdInsertSmmt.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory) VALUES ('" & drAccessRecord("MVRIS CODE") & "', '" & drAccessRecord("Vehicle Category Code") & "'" & " )"

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41719422
thank you that was the exact cause.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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