Error in SQL statement

KMFCross

Hi Guys,

Just a quick question in relation to why the following code is not working, it causes a syntax error in the SQL statement.

The suspect line is:

clsAccessDatabaseCode.SqlInsertUpdateCommand("Insert INTO Testing (ProcName, Interval) Values ('" + txtProc.Text.Trim().ToString() + "', " + decimal.Parse(txtInterval.Text) + ")");

Below is what the parameter is when passed into the function:

Insert INTO Testing (ProcName, Interval) Values ('calc', 60)


And below is the lines of code in the function:

OleDbCommand cmdInsert = new OleDbCommand(InsertUpdateString, DatabaseConn);
cmdInsert.ExecuteNonQuery(); //This is where the exception is raised

I know it is not the SqlInsertUpdateCommand function as this works fine for other tables.

The table structure is
ProcName, Text(50), PK
Interval, Number(Long Integer) Decimal = Auto

I have also changed the interval field to be Text and passed in text instead of the decimal but still the same problem.

Can anybody see anything that i am overlooking, i have spent hours at this now to no avail!

Thanks in Advance.

Evil Phil

Can you post the exception you're getting?

Beano

the sql statement works fine for me.
What database are you using?
Have you tried running the sql statement directly on the database?

KMFCross

ex.Message
Syntax error in INSERT INTO statement.

ex.Source
Microsoft JET Database Engine

ex.stacktrace
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 Application_Starter.clsAccessDatabaseCode.SqlInsertUpdateCommand(String InsertUpdateString) in C:\Application_Starter\clsAccessDatabaseCode.cs:line 97
at Application_Starter.frmTesting.btnSave_Click(Object sender, EventArgs e) in C:\Application_Starter\frmTesting.cs:line 27

errorcode
-2147217900

Evil Phil

^^ what Beano said. Get the sql statement that is generated by your code (don't guess, step through your code to get it) and run that against the database, this should show you what the problem is. Post the sql here too, I'm curious

KMFCross

Hi Beano,

Yes i have ran the SQL as a query in Access 2000 and it works fine.

The database was created in Access 200, but the database has been edited in Access 2003 & 2007, I dont think this should cause a problem however.

KMFCross

Hi Evil Phil,

Yes i had done that already in Access.

Here is the SQL :

Insert INTO Testing (ProcName, Interval) Values ('calc', 60)

Beano

jsut tried running that query in access 2003 and had no problems. odd.

Evil Phil

Yeah, odd. Can we see the code for: SqlInsertUpdateCommand

(I bet this is staring us right in the face :-/)

DeVore

http://www.google.ie/search?hl=en&q=-2147217900&meta=

That might answer you too....

DeV.

KMFCross

//The DB String for the main database
//The DB String for the main database
publicstaticstring strDBFile = Application.StartupPath + "\\DataFile.udl";
// The string for the database connection
privatestaticstring DBString = "file name=" + strDBFile;
//The oledbconnection object passed the string to open the connection
publicstaticOleDbConnection DbConnectionAccess = newOleDbConnection(DBString);

publicstaticvoid SqlInsertUpdateCommand(string InsertUpdateString)
{
//This function inserts the specified string into the required database
OleDbConnection DatabaseConn = null;
// A string to store the database to be used

DatabaseConn = DbConnectionAccess;
//Not catching error as higher up calling function does
//Declare a command object taking the string and database as parameters
OleDbCommand cmdInsert = newOleDbCommand(InsertUpdateString, DatabaseConn);
//Call the executenonQuery method and return its value to the calling code
cmdInsert.ExecuteNonQuery(); //Problem Line 'Exception occurs'

}

KMFCross

Hi Devore,

I have looked at those links, but i dont think its anything to do with ASP & security. The reason for this is because all my other Insert statements work, and they use the same method 'SqlInsertUpdateCommand'

The only difference with the other SQL's is none of them use the Number type in access, but i have tested this table with the text type as well and same problem:(

Beano

perhaps its something wrong witht the table itself. Is there any data currently in the table? If you're using the text column as a primary key does the data you are inserting already exist?

Evil Phil

Beano wrote: »

If you're using the text column as a primary key does the data you are inserting already exist?

/slaps forehead

KMFCross

Yes the text column is the PK, i am just using the database as a dummy data store and doing the logic in code.

The entry does not exist in the table already so this is not the problem.

KMFCross

I finally figured this out. I added another table same structure to see if this would work, however same thing.

Then i decided to execute SQL against another table with two fields aswell in the same form as the problem code. THIS WORKED. So i knew it was'nt anything with the form.

The only difference with this other table was that the filed name was ProcLoc, not Interval. So i changed the field name to TheInterval. THIS WORKED:eek:

So access & OLEDB has a problem with a field name called Interval, I don't know why, just glad to get this working.

Hope this info helps others.

Beano

Interval must be a reserved word in access sql. Instead of renaming the field you could have wrapped the field name in []s (so it would be [Interval]). But changing it is probably a better move.

KMFCross

Hi Beano,

After doing a bit of research on reserved words in Access i did not find Interval as a reserved word, however i am using the Jet Engine to communicate with the database and i found the following

http://support.microsoft.com/kb/321266

Interval is a reserved word in the Jet Engine as well as many others that can not be used as field names without enclosing them in square brackets in SQL as you said.

Thanks for the help guys, hope this info helps others.

DeVore

Thus we all learn

DeV.

Beano

that is one odd list of reserved words. INTEGER1, INTEGER2 and INTEGER4 but no INTEGER3.