ADO.NET Access Insert command error

[fpineda101]

Looking at some of the previous posts, I decided to try a suggestion of tech gnome to get the full error description. Below is what I got...

---------------------------
---------------------------
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
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.ExecuteNonQuery()
at ATM_V2.Access.ExecuteSQL(String inSQL) in C:\VB Examples\ATM V2\Access.vb:line 44
at ATM_V2.ATM.SetAction_Deposit(Int32 keyEntry) in C:\VB Examples\ATM V2\ATM.vb:line 517
---------------------------
OK
---------------------------

My sql that gets error is this:
SQL = "INSERT INTO TRANSACTION (account_no, trans_dt, trans_amt, trans_bal) VALUES ('" & dt.account_no & "', '" & Format(dt.transaction_dt, "dd-MMM-yy") & "', '" & dt.transaction_amt & "', '" & dt.transaction_bal & "')"

I try this sql too and this works for some reason...
SQL = "INSERT INTO account (account_no, account_id, account_type, pin, last_login, title, account_name, balance) VALUES ('78901', '51234', 'CHECKING', '9876', '" & Format(Now, "dd-MMM-yy") & "', 'Mrs.', 'PINEDA', '100')"

The string is passed to a sub routine which executes UPDATE commands just fine as well as the INSERT to the ACCOUNT table. Below is the sub routine...

Public Sub ExecuteSQL(ByVal inSQL As String)
Dim accCON As New OleDbConnection
Dim accCOMMAND As New OleDbCommand

accCON = New OleDbConnection(ACCESS_CONNECT_STRING)
accCOMMAND.CommandText = inSQL
accCOMMAND.Connection = accCON

accCON.Open()
accCOMMAND.ExecuteNonQuery()
accCON.Close()
End Sub

Tech Gnome, if you are out there please help! Or anyone else would be greatly appreciated too! It doesnt make sense to me that the 2nd insert would work but not the first...

 

[TechGnome]

Oh, great I've been called out by name.... ....

At this point, the first thing I would do is immediately after you create your SQL statement, do a MessageBox.Show SQL to see what the SQL *really* look like. At the moment, on the surface at least, I don't see anything really wrong, so it's hard to tell.

It's difficult to compare the two SQL statements because they are for different tables.

But give the MessageBox.Show a try and see if there's anything that looks odd in the SQL before executing it.

-tg

 

[fpineda101]

Tech Gnome -

I tried messagebox.show(sql). This is the result...
---------------------------
---------------------------
INSERT INTO TRANSACTION (account_no, trans_dt, trans_amt, trans_bal) VALUES ('23456', '3/29/2006', '150', '1025')
---------------------------
OK
---------------------------

The oledb error appears to be the same...
---------------------------
---------------------------
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
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.ExecuteNonQuery()
at ATM_V2.Access.ExecuteSQL(String inSQL) in C:\VB Examples\ATM V2\Access.vb:line 44
at ATM_V2.ATM.SetAction_Deposit(Int32 keyEntry) in C:\VB Examples\ATM V2\ATM.vb:line 522
---------------------------
OK
---------------------------

I am using Access 2003 but it is saved in Access 2000 format.

I also add these parameters before calling executenonquery()

accCOMMAND.Parameters.Add(dt.account_no, OleDb.OleDbType.Numeric, 15, "account_no")
accCOMMAND.Parameters.Add(dt.transaction_dt, OleDb.OleDbType.Date, 15, "trans_dt")
accCOMMAND.Parameters.Add(dt.transaction_amt, OleDb.OleDbType.Currency, 15, "trans_amt")
accCOMMAND.Parameters.Add(dt.transaction_bal, OleDb.OleDbType.Currency, 15, "trans_bal")

dt stands for deposit transaction. Deposit is a sub class that inherits from parent class Transaction. Should I try assigning the object variables to regular variables to plug into the SQL? Any other ideas?

Thanks for your help!

 

[fpineda101]

Resolved

Tech Gnome

I renamed the table from TRANSACTION to TRANSACTION1. I did the same for the INSERT. That got it to work. I didn't know that the table name was reserved by Access, its not for Oracle

 

[vis781]

Transaction is used widely by jet and vb, interestingly enough another key word i've found is 'classification' Have to admit that it's a real pain in the butt when this happens.

 

[TechGnome]

D'uh *slaps self on the head* Yeah TRANSACTION is a reserved keyword in SQL... it's what starts a transaction.... I should have picked up on that.

Glad you got it working though.

-tg

PS - if you find yourself getting errors because of tables or fields using reserved keywords like that.... put them in square brackets [ ]
[Transaction]
[classification]

That will offset it and treat it like the table/field name that it is.