insert into tabl error

[davincinewfie]

oledbexception was unhandled....syntax error in insert into statement is the error I get I can edit/update information, delete and everything except for add

the cmdSave code is::::

Dim drNewRow As DataRow = m_dtTable1.NewRow()

With Me
drNewRow("id") = .txtid.Text
drNewRow("Date") = .txtdate.Text
drNewRow("Time") = .txttime.Text
drNewRow("CallSign") = .txtcallsign.Text
drNewRow("Freq") = .txtfreq.Text
drNewRow("Mode") = .txtmode.Text
drNewRow("RST") = .txtrst.Text
drNewRow("Notes") = .txtnotes.Text
drNewRow("QslTX") = .txtqsltx.Text
drNewRow("QslRX") = .txtqslrx.Text
End With

m_dtTable1.Rows.Add(drNewRow)
m_daTable1.Update(m_dtTable1) <<<<-----ERROR HERE

m_intRowPosition = m_dtTable1.Rows.Count - 1

m_blnNewRecord = False
m_blnUpdateRecord = False

ShowCurrentRecord()


----------------------------------------------------------------

declarations:::
Public m_cnTable1 As New OleDb.OleDbConnection
Public m_daTable1 As OleDb.OleDbDataAdapter
Public m_cbTable1 As OleDb.OleDbCommandBuilder
Public m_dtTable1 As New DataTable
Public m_intRowPosition As Integer = 0
Public m_blnNewRecord As Boolean = False
Public m_blnUpdateRecord As Boolean = False


----------------------------------------------------------------

I can post full code if need be but I figured going through that much might be enough

[jmcilhinney]

The most likely reason for this is that you have a query like this:

SELECT * FROM MyTable

and at least one of your column names is an SQL reserved word, like "password" or "date". When the CommandBuilder generates the SQL code for the INSERT statement it doesn't use brackets around column names (which is an oversight on Microsoft's part) so those column names are interpreted as reserved words and cause the syntax error. You have two options:

1. Don't use a CommandBuilder and rather create the InsertCommand of the DataAdapter yourself.
2. Don't use a wild card in your query but rather write out the full column list. That way you will have to use brackets yourself and the CommandBuilder will too, e.g.

SELECT UserID, [Password] FROM MyTable

[davincinewfie]

hmmmm I do have date as a column in the database, if I changed date to say mydate in the database then changed everything in the code to reflect that, do you think it would help

I ask first only because you can only imagine how many times that appears in the code lol

[davincinewfie]

my current how I conenct is called from frmload.... below is the code

Public Sub ConnectToTable1()
m_cnTable1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Windows.Forms.Application.StartupPath & "\hamlog.mdb"
m_cnTable1.Open()
m_daTable1 = New OleDb.OleDbDataAdapter("SELECT Table1.* FROM Table1", m_cnTable1)
m_cbTable1 = New OleDb.OleDbCommandBuilder(m_daTable1)
m_daTable1.Fill(m_dtTable1)

' Move to, and display the first row (record)
m_intRowPosition = 0
ShowCurrentRecord()

End Sub

[jmcilhinney]

It is preferable to not use SQL key words as entity names in your database but it's certainly not the end of the world. You certainly wouldn't change the name of a column from "Date" to "MyDate". You would at least change it to something meaninful, like "CreatedDate" or the like. Maybe you were only using that as an example.

Anyway, I've already explained what you need to do to fix this issue. Did you actually read my post?

Don't use a wild card in your query but rather write out the full column list. That way you will have to use brackets yourself and the CommandBuilder will too, e.g.

SELECT UserID, [Password] FROM MyTable

As I have demonstrated, any column names that are reserved words simply require brackets. You may choose to enclose all table and column names in brackets all the time to ensure that this sort of issue can never happen. Some people do; I don't.

Note also that, while it does not do any harm, qualifying the column names with the table name is fairly pointless when your query involves only a single table.