Question Insert Into Error

[ggunter]

I'm a VB6 programmer trying to get a handle on .NET and I'm really hoping someone can point me in the right direction.

My insert sql statement is as follows:

sql = "INSERT INTO tblMTDadjust (Lend, ManagerAcid, AssocAcid, Month, " & _
"Day, Hour, Minute, Region, Unit, Activity, Symbol, AdjHour, " & _
"AdjMinute, AdjustedDate, EnteredDate, RunningProgram) " & _
"VALUES(@lend, @mgracid, @acid, @month, @da, @hr, @min, " & _
"@region, @unit, @activity, @symbol, @adjhr, @adjmin, @adjdate, " & _
"@entereddate, @procnum)"

The add new row statement appears to work okay but when I try add data to the row I receive the following error message: "Syntax error in INSERT INTO statement".

At first, I thought there might be a typo in the statement so I copied the field names directly from the table into my code.

Then, I checked the net for the error code. There were a few entries and they all said that I needed to add a value for every field but my code is already doing that.

Then, I checked the details of the error message and found the following: "In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.". I checked the table but there are no indexed fields in the table.

Please help. I'm out of ideas as to where to look next.

 

[JohnH]

The places I've seen syntax for this statement there is a space between VALUES and (, does that make a difference?

 

[ggunter]

Thanks for the quick reply John.

Unforetunately, the added space did not solve the problem.

It, then, dawned on me that I had neglected to put in an UPDATE statement. Since you add a row and then update it, I thought that might be causing the problem. However, the same error message still occurs.

 

[JohnH]

Have you verified that you are not using any reserved keywords in column names? These must be escaped with [...].

 

[ggunter]

I believe Day, Month, Hour, and Minute are function calls but since those columns are referenced with quotes ("Day") it wouldn't apply would it?

I checked help and could not find any of the column names listed as reserved words.

I don't know if this helps but I have determined the error occurs at the dataAdapter.Update command.

 

[MattP]

Have you verified that you are not using any reserved keywords in column names? These must be escaped with [...].

Month and Day are reserved in his current statement.

 

[ggunter]

Okay. I changed to sql statement to this:

sql = "INSERT INTO tblMTDadjust (Lend, ManagerAcid, AssocAcid, [Month], " & _
"[Day], Hour, Minute, Region, Unit, Activity, Symbol, AdjHour, " & _
"AdjMinute, AdjustedDate, EnteredDate, RunningProgram) " & _
"VALUES (@lend, @mgracid, @acid, @month, @da, @hr, @min, " & _
"@region, @unit, @activity, @symbol, @adjhr, @adjmin, @adjdate, " & _
"@entereddate, @procnum)"

It still generates the same error. BTW, thank you for the input MattP.

Also, I just noticed this was moved to the SQL Server section. (Wish I had access to the SQL Server!!) I'm using an Access 2003 database. Would that make a difference? Sorry, if I put the original thread in the wrong place. I thought this would go in ADO since that is the type of connection I'm attempting to use.

 

[MattP]

I'm using an Access 2003 database. Would that make a difference?

This makes a difference as parameters are denoted differently.

sql = "INSERT INTO tblMTDadjust (Lend, ManagerAcid, AssocAcid, [Month], " & _
"[Day], Hour, Minute, Region, Unit, Activity, Symbol, AdjHour, " & _
"AdjMinute, AdjustedDate, EnteredDate, RunningProgram) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

I think you can name them ?lend, ?acid, etc. if it helps you keep track of them but Access will ignore that.

 

[ggunter]

Okay.

I have tried the sql statement with "Month" and "Day" bracketed and without. I tried it with named parameters and with question marks. And I am still getting the same error message.

Here is the sql I am using to populate the datatable:

sql = "SELECT tblMTDadjust.Lend, tblMTDadjust.ManagerAcid, " & _
"tblMTDadjust.AssocAcid, tblMTDadjust.[Month], tblMTDadjust.[Day], " & _
"tblMTDadjust.Hour, tblMTDadjust.Minute, tblMTDadjust.Region, " & _
"tblMTDadjust.Unit, tblMTDadjust.Activity, tblMTDadjust.Symbol, " & _
"tblMTDadjust.AdjHour, tblMTDadjust.AdjMinute, tblMTDadjust.AdjustedDate, " & _
"tblMTDadjust.EnteredDate, tblMTDadjust.RunningProgram " & _
"FROM tblMTDadjust;"

Here is the sql I am trying to use to update the database (as of my last try):

sql = "INSERT INTO tblMTDadjust (Lend, ManagerAcid, AssocAcid, [Month], " & _
"[Day], Hour, Minute, Region, Unit, Activity, Symbol, AdjHour, " & _
"AdjMinute, AdjustedDate, EnteredDate, RunningProgram) " & _
"VALUES (@lend, @mgracyd, @acyd, @mnth, @da, @hr, " & _
"@min, @reg, @unit, @actv, @sym, @adjhr, @adjmin, " & _
"@adjdat, @ntrdat, @procnum)"

And here is the error I am getting:

System.Data.OleDb.OleDbException was unhandled by user code
ErrorCode=-2147217900
Message="Syntax error in INSERT INTO statement."
Source="Microsoft JET Database Engine"
StackTrace:
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
       at TactAdj.clsAdo.SaveToDatabase() in C:\z_GG Project\Tact Adj\v1.0.0.2\TactAdj\clsAdo.vb:line 234
       at TactAdj.frm_4_Rumba.bckWorker_DoWork(Object sender, DoWorkEventArgs e) in C:\z_GG Project\Tact Adj\v1.0.0.2\TactAdj\frm_4_Rumba.vb:line 166
       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

It doesn't matter what I try, it's always the same message.

I'm beginning to wonder if this isn't one of those Microsoft error messages that, although technically correct, tend to point an inexperienced user (like me) in the wrong direction.

Before beginning this program, I wrote a small app to test reading/writing to/from a database and it worked fine. Now, using the same principles, I can't get the database to update no matter what I do.

 

[ggunter]

Finally solved it. Apparently Hour and Minute are also reserved words. Here's the sql that finally worked.

sql = "INSERT INTO tblMTDadjust (Lend, ManagerAcid, AssocAcid, [Month], " & _
"[Day], [Hour], [Minute], Region, Unit, Activity, Symbol, AdjHour, AdjMinute, " & _
"AdjustedDate, EnteredDate, RunningProgram) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

 

[MattP]

Oops, while Hour and Minute don't constrain SQL grammar they are ODBC reserved words.

While you're at it I would avoid using Region as a column name.