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.ExecuteCommandTextErrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior 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.

 

[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

 

[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/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

regards

Michel Posseth [MCP]