Insert into Linked Access Using ADO.Net

[Criki]

OK, I keep getting a syntax error on insert. But the statement is fine, (I think). Can anybody help???




Dim myCommand As New OleDb.OleDbCommand()
Dim myTrans As OleDb.OleDbTransaction

Dim myConnection As OleDb.OleDbConnection
Dim sConnString As String
Dim sSQL as String

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\City1.mdb;"


sSQL = "insert into Real_Estate (real_estate, subdivision, land_district,Block, Lot,range,section,township) values ('" & rsEDI.Fields(0).Value & "','" & rsEDI.Fields(1).Value & "','" & rsEDI.Fields(2).Value & "','" & rsEDI.Fields(3).Value & "','" & rsEDI.Fields(4).Value & "','" & rsEDI.Fields(5).Value & "','" & rsEDI.Fields(6).Value & "','" & rsEDI.Fields(7).Value & "')"


myConnection = New OleDb.OleDbConnection(sConnString)
myConnection.Open()


myCommand.Connection = myConnection

myTrans = myConnection.BeginTransaction()

myCommand.Transaction = myTrans

Try

myCommand.CommandText = sSQL
myCommand.ExecuteNonQuery()
myTrans.Commit()

Catch i As Exception
MsgBox(Err.Description)
Finally
myConnection.Close()
End Try

[wolfofthenorth]

Try something like this...

Dim SQL as string = "INSERT INTO myTable (MyField) VALUES (@myValue)"

Dim dc As SqlConnection
Dim sc As SqlCommand

dc = New SqlConnection(myConnectionString)
sc = New SqlCommand(SQL, dc)
sc.Parameters.Add(New SqlParameter("@myValue", myValue))
dc.Open()
Return sc.ExecuteNonQuery