By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,665 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,665 IT Pros & Developers. It's quick & easy.

Problem with INSERT INTO MS Access using VB.NET

P: n/a
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
--
kemmerc2 - forum member
http://www.visual-basic-data-mining.net/forum
Nov 21 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
-------------------------------
"http://www.visual-basic-data-mining.net/forum" <si******@gmail.com> wrote
in message news:O3**************@TK2MSFTNGP12.phx.gbl...
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
--
kemmerc2 - forum member
http://www.visual-basic-data-mining.net/forum

Nov 21 '05 #2

P: n/a
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
"http://www.visual-basic-data-mining.net/forum" <si******@gmail.com> wrote
in message news:O3**************@TK2MSFTNGP12.phx.gbl...
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
--
kemmerc2 - forum member
http://www.visual-basic-data-mining.net/forum

Nov 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.