Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this syntax instead:
cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
cmdInsertSmmt.Parameters.A
cmdInsertSmmt.Parameters.A
Obviously you'd change the "SqlDBType.VarChar" to reflect the actual datatypes.
cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
cmdInsertSmmt.Parameters.A
cmdInsertSmmt.Parameters.A
Obviously you'd change the "SqlDBType.VarChar" to reflect the actual datatypes.
Do you do :
cmdInsertSmmt = New SqlCommand()
within the loop, so that parameters are new in every loop?
cmdInsertSmmt = New SqlCommand()
within the loop, so that parameters are new in every loop?
@ Valliappan
I dont believe i have here is the part above the for loop, so does that need to go within the loop?
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
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
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
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.
it must be the sequence in the for loop with the command objects.
I just do not know what.
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
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.
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()
Premium Content
You need an Expert Office subscription to comment.Start Free Trial