sql server variable names already declared in vb.net

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.

@ 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

Select all Open in new window

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

Select all Open in new window

I stepped through and the error was on the 2nd record, so maybe thats because of what  Valliappan said

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

Select all Open in new window

I checked and in some cases it populates in others it doesnt, the for loop has the right count.

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.

Is the issue resolved ?

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

Select all Open in new window

How many records are you trying to update, and how many is updated so far?

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.

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.

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()

Select all Open in new window

finally got it with

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

Select all Open in new window

thank you that was the exact cause.