• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

sql server variable names already declared in vb.net

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
PeterBaileyUk
Asked:
PeterBaileyUk
  • 11
  • 4
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
Do you do :

cmdInsertSmmt = New SqlCommand()

within the loop, so that parameters are new in every loop?
0
 
PeterBaileyUkAuthor Commented:
@ 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
I stepped through and the error was on the 2nd record, so maybe thats because of what  Valliappan said
0
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
I checked and in some cases it populates in others it doesnt, the for loop has the right count.
0
 
PeterBaileyUkAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
Is the issue resolved ?
0
 
PeterBaileyUkAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
How many records are you trying to update, and how many is updated so far?
0
 
PeterBaileyUkAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
finally got it with
 cmdInsertSmmt.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory) VALUES ('" & drAccessRecord("MVRIS CODE") & "', '" & drAccessRecord("Vehicle Category Code") & "'" & " )"

Open in new window

0
 
PeterBaileyUkAuthor Commented:
thank you that was the exact cause.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 11
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now