Insert data into an Access DB using VB.NET. 1

[paulcy82]

I am trying to insert a new row of data into an Access database. Here is what I am using:

Dim fNm As String
Dim lNm As String
Dim rowsAffected As Integer

fNm = Me.tbFirstNm.Text()
lNm = Me.tblastNm.Text()

Dim cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Auto_Mate.mdb;"

Dim cn As New OleDbConnection(cnString)

'try to open connection
Try
cn.Open()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

Dim sql As String = "Insert into DRIVER (DRIVER_FIRST_NM, DRIVER_LAST_NM)"
sql = sql & "VALUES (" & fNm & ", " & lNm & ")"
Dim cmd As New OleDbCommand(sql, cn)

rowsAffected = cmd.ExecuteNonQuery()

Debug.Write(fNm & lNm)
End Sub


Everything works until I get to 'rowsAffected = cmd.ExecuteNonQuery()' I get the following error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

I do not want to have to use a dataset to insert, but I will if that is the only way. Am I on the right track or should I be using something like SQLCommand?

 

[Korach]

What is the exception exactly?

Try to change this row:

sql = sql & "VALUES (" & fNm & ", " & lNm & ")"

To:

sql = sql & "VALUES ('" & fNm & "', '" & lNm & "')"

 

[paulcy82]

THANK YOU Korach, that is exactly what was wrong.

 

[paulcy82]

I got so wrapped up in it working that I forgot to ask, is there an easier way to do this, one without all the ' and "?

 

[Korach]

1. You can write a function that combines the sql statement.
2. There is an application block for sql server that simplifies your work, called "Data access application block". Try to find if there is a translation of it to access.
3. In sql server you can exec a procedure with parameters, and you don't need to write sql strings in your code. I don't know if you can do it with access. Try seeking a class named OleDbParameter.
4. You probably thought what I would say now, use sql server. MSDE is free and you can manage it with visual studio or download the web admin tool.
5. If all the above doesn't suit you, welcome to database development! This is frustrating!! and as you develop more, you'll find it more frustrating. Every day you''l find a new problem and every tomorrow you'll find a solution to it and a new problem...

good luck

 

[Davenz]

I think you need a space after the "LAST_NM)" part before "VALUES".

either:

...LAST_NM) "

or

sql = sql & " VALUES.....

Also, put a TRY..CATCH...END CATCH loop around your code:

TRY
(Your code)
CATCH (ex as exception)
messagebox.show(ex.tostring)
END CATCH

Then if your SQL is dodgy you will get the full SQL error message which will tell you what is wrong with it.

Dave