Error With Insert

[Materialised]

I am having a problem, inserting 4 items into a database table.

The database table simply consists of 4 records:

Date
Com
Note
Name

All fields are of type 'Text'

Here is my sub:

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdAdd.Click
Dim sqlString As String
sqlString = "Insert Into NOTES (DATE, COM, NOTE, NAME) Values (" _
& "'" & Now().ToString & "', " _
& "'" & Form3.CName.Text & "', " _
& "'" & txtNotes.Text & "', " _
& "'" & txtName.Text & "')"
MsgBox(sqlString)
Try
Dim dbConn As OleDbConnection
Dim dbCommand As New OleDbCommand
dbConn = New OleDbConnection(pPROVIDER & pDataSource)
dbCommand.CommandText = sqlString
dbCommand.Connection = dbConn
dbConn.Open()
Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader
dbConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, "Error")
End Try
End Sub

When I execute this sub, I get the following error:

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior,
Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at WindowsApplication1.Form4.cmdAdd_Click(Object sender, EventArgs e) in
\Forms\Form4.vb:line 181
Its probably a stupid error on my part. But for the life of my I am having
trouble tracking it down.
Can anyone help?

[Cor Ligthert [MVP]]

Materialised,

We have seen this done in many way, however you are the winner.

You cannot use reserved keywords in a SQL string.

Cor

[Materialised]

Thanks for that info. I wasn't aware of that to be honest.
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:ec****************@TK2MSFTNGP14.phx.gbl...

Materialised,

We have seen this done in many way, however you are the winner.

You cannot use reserved keywords in a SQL string.

Cor

[Ken Tucker [MVP]]

Hi,

You will not get any records back when you execute your query.
Do not use a datareader.

Replace

Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader

With
dbCommand.ExecuteNonQuery

Ken
--------------------

"Materialised" <ma**********@privacy.net> wrote in message
news:3v*************@individual.net...

I am having a problem, inserting 4 items into a database table.

The database table simply consists of 4 records:

Date
Com
Note
Name

All fields are of type 'Text'

Here is my sub:

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdAdd.Click
Dim sqlString As String
sqlString = "Insert Into NOTES (DATE, COM, NOTE, NAME) Values (" _
& "'" & Now().ToString & "', " _
& "'" & Form3.CName.Text & "', " _
& "'" & txtNotes.Text & "', " _
& "'" & txtName.Text & "')"
MsgBox(sqlString)
Try
Dim dbConn As OleDbConnection
Dim dbCommand As New OleDbCommand
dbConn = New OleDbConnection(pPROVIDER & pDataSource)
dbCommand.CommandText = sqlString
dbCommand.Connection = dbConn
dbConn.Open()
Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader
dbConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, "Error")
End Try
End Sub

When I execute this sub, I get the following error:

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior,
Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at WindowsApplication1.Form4.cmdAdd_Click(Object sender, EventArgs e) in
\Forms\Form4.vb:line 181
Its probably a stupid error on my part. But for the life of my I am having
trouble tracking it down.
Can anyone help?

[m.posseth]

well not completely true ,,,, you can as long as you put brackets around
these words like [Date] :-)

although it is ofcourse recomend coding practice to avoid words that are on
the reserved words list

most databases use the SQL 92 reserved words list

http://msdn.microsoft.com/library/de...ra-rz_9oj7.asp

regards

Michel Posseth [MCP]
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:ec****************@TK2MSFTNGP14.phx.gbl...

Materialised,

We have seen this done in many way, however you are the winner.

You cannot use reserved keywords in a SQL string.

Cor