Problem with INSERT INTO MS Access using VB.NET

[http://www.visual-basic-data-mining.net/forum]

All,
I am attempting to insert a new row into an MS Access database using
VB.NET. This is an existing access database that I did not create, so I do
not know all of the field properties. I have used VB.NET in the past, but
never for database applications. When I run the following code I recieve the
error:

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

This error occurs at the line:

"temp_num = myOleDbCommand.ExecuteNonQuery"




Here is the code and thank you for your help in advance:


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim myOleDbConnection As OleDb.OleDbConnection

Dim insertcommand As String

Dim myConnectionString As String = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=T:\project_tracking\project database\Copy of projects.mdb;" & _

"User ID=Admin;" & _

"Password="

insertcommand = "INSERT INTO Contacts (ProjNum, ContactCode, FirstName,
LastName, WorkPhone, Email, Pager, Source, Responsibility, LastMaintDate)
VALUES ('202-MV174', '0', 'Brenton', 'Kemmer', '248-830-1532', 'None', 'Na',
'Na', 'here', '2004-10-19 10:25:38')"

myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)

Dim myOleDbCommand As New OleDb.OleDbCommand(insertcommand,
myOleDbConnection)

myOleDbConnection.Open()

Dim temp_num As Integer

temp_num = myOleDbCommand.ExecuteNonQuery

myOleDbConnection.Close()

End Sub


Thanks

 

[Ken Tucker [MVP]]

Hi,

There is an error with sql statement. Place the update command in a
try catch block to get a better message. This code will display the error
message in the output window

myOleDbConnection.Open()

Dim temp_num As Integer

Try

temp_num = myOleDbCommand.ExecuteNonQuery

catch ex as exception
trace.writeline(ex.tostring)
end try

myOleDbConnection.Close()

Ken
-------------------------------
in message All,
I am attempting to insert a new row into an MS Access database using
VB.NET. This is an existing access database that I did not create, so I do
not know all of the field properties. I have used VB.NET in the past, but
never for database applications. When I run the following code I recieve the
error:

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

This error occurs at the line:

"temp_num = myOleDbCommand.ExecuteNonQuery"




Here is the code and thank you for your help in advance:


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim myOleDbConnection As OleDb.OleDbConnection

Dim insertcommand As String

Dim myConnectionString As String = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=T:\project_tracking\project database\Copy of projects.mdb;" & _

"User ID=Admin;" & _

"Password="

insertcommand = "INSERT INTO Contacts (ProjNum, ContactCode, FirstName,
LastName, WorkPhone, Email, Pager, Source, Responsibility, LastMaintDate)
VALUES ('202-MV174', '0', 'Brenton', 'Kemmer', '248-830-1532', 'None', 'Na',
'Na', 'here', '2004-10-19 10:25:38')"

myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)

Dim myOleDbCommand As New OleDb.OleDbCommand(insertcommand,
myOleDbConnection)

myOleDbConnection.Open()

Dim temp_num As Integer

temp_num = myOleDbCommand.ExecuteNonQuery

myOleDbConnection.Close()

End Sub


Thanks

 

[Ron Allen]

Wrap your date/time values inside # for quoting instead of ' i.e.
#2004-10-19 10:25:38#.
You may also need to quote the field name Source as [Source] as this may
be a reserved word for the OleDb driver.
For more specific responses about ADO.NET see
microsoft.public.dotnet.framework.adonet.

Ron Allen