SQL INSERT syntax error, but the statement is correct!

[kosta]

hello! one of my forms communicates with a database, and is
supposed to add a row to a table using an Insert
statement... however, I get a 'oledb - syntax error'
exception... I have double checked, and the insert works
fine (tried to use it from access)... im using visual C#
express 2k5... what could be wrong? thanks!


it is an access 2000 database.
here is the statement:

INSERT INTO Partyers (Sex, Last, First, CPhone, Phone,
City, BDay, Car, PicPath, FPotential, TPotential,
PartyCities) Values (False, '', '', '', '', '', '10/16/2004
12:17:22 AM', False, '', '', '', '')

when I use it from access, it goes file...

here is the code I use to Add it:

public static int ANum(String sql)
{
OleDbConnection con = new OleDbConnection();
con.ConnectionString =
ConfigurationSettings.AppSettings["conString"];
OleDbCommand cmd = new OleDbCommand(sql,con);
con.Open();
cmd.ExecuteNonQuery();
string identity = "select @@identity";
cmd.CommandText = identity;
int id = (int)cmd.ExecuteScalar();
con.Close();
return id;
}

and the exception:


{"Syntax error in INSERT INTO statement."}
[System.Data.OleDb.OleDbException]: {"Syntax error in
INSERT INTO statement."}
Data: {System.Collections.ListDictionaryInternal}
HelpLink: null
InnerException: null
Message: "Syntax error in INSERT INTO statement."
Source: "Microsoft JET Database Engine"
StackTrace: " at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult
hr)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)\r\n at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()\r\n at
SQL.Connect.ANum(String sql) in f:\\my documents\\visual
studio\\projects\\connect\\connect.cs:line 63\r\n at
SQLStatements.PartiesDB.AddPartier(Boolean Sex, String
LName, String FName, String CPhone, String Phone, String
City, DateTime BDay, Boolean Car, String PicPath, String
PotentialClient, String PotentialTripper, String PCities)
in f:\\my documents\\visual
studio\\projects\\sqlstatements\\partiersdb.cs:line 20\r\n
at DataBase.Partyer
Update.Add() in F:\\My Documents\\Visual
Studio\\Projects\\Database\\PartyerUpdate.cs:line 35"
TargetSite: {Void
ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)}


thanks a lot in advance!
Kosta.

 

[Joep]

Have you tried a null date, or another format for that matter? What is the
result of that exercise? The statement looks ok, so maybe its a value format
thing. It would be good to exclude that possibillty.

 

[Jon Skeet [C# MVP]]

Have you tried a null date, or another format for that matter? What is the
result of that exercise? The statement looks ok, so maybe its a value format
thing. It would be good to exclude that possibillty.

And whether or not it turns out that that's the problem, embedding
values directly into SQL like that isn't generally a good idea. Use
parameters (in this case OleDbParameter) instead, so that the driver
does any formatting required.

 

[Joep]

I agree completely with using parameters but that is another topic.

This is about finding an error. I am not suggesting that using hard-coded
values is a better solution. By not seeing the values being passed we cannot
say that no mismatch is taking place. So I would like to see values (or
actually, have kosta make sure that there is no mismatch ;-) ), reduced
statements and the like.

 

[Guest]

first of all, I havent tried to use another date format,
but i have numerous other insert queries with the same
format, which work just fine, so I really dont think that's
the problem...

as for the OleDbParameter, could you please elaborate a
bit? Im kinda doing what ive been taught... cwhy is my way
wrong/not a good idea? what adventeges does oledbparameter
has over my way? and how is it used?

thanks!
kosta

 

[kosta]

hmmm, that last message before yours was mine...
I assure you that there is no mismatch.... I have double
and triple checked...
also, why, if there is a mismatch, would I be able to use
that same INSERT from within access?

 

[Jon Skeet [C# MVP]]

first of all, I havent tried to use another date format,
but i have numerous other insert queries with the same
format, which work just fine, so I really dont think that's
the problem...

If you haven't tried, you just don't know. If you can't see anything
wrong, but people have suggested a way of testing something, why not
test it?

as for the OleDbParameter, could you please elaborate a
bit? Im kinda doing what ive been taught... cwhy is my way
wrong/not a good idea? what adventeges does oledbparameter
has over my way? and how is it used?

See http://www.pobox.com/~skeet/csharp/faq/#db.parameters

If you've been taught to embed values in SQL statements, btw, I would
have a very close look at what else you've been taught from the same
source.

 

[Joep]

Using parameters has nice advantages and is a good recommendation but for
now,

you gave this SQL statement:

INSERT INTO Partyers (Sex, Last, First, CPhone, Phone,
City, BDay, Car, PicPath, FPotential, TPotential,
PartyCities) Values (False, '', '', '', '', '', '10/16/2004
12:17:22 AM', False, '', '', '', '')

I would first

- remove the columns/values that are not mandatory
- add the column names of the remaining columns
- test the effects of the above and try to pass a null date as well if
possible

Then post the remaining statement and the test result. Maybe even post the
table definition.

Maybe the problem has another origin but that is not something that I can
see with the information you provided sofar. So my suggestion is to
eliminate possible causes.

 

[Ravichandran J.V.]

Actually, the OleDb data provider requires that all dynamic values be
passed as parameters.

For instance, the values for columns - Sex, Last, First should be passed
to the command object as parameter objects as follows

cmd.Parameters.Add(new OleDbParameter("@Sex"));
cmd.Parameter["@Sex"].Value='';

etc.


with regards,


J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- http://www.411asp.net/func/search?
qry=Ravichandran+J.V.&cob=aspnetpro
- http://www.southasianoutlook.com
- http://www.MSDNAA.Net
- http://www.csharphelp.com
- http://www.poetry.com/Publications/
display.asp?ID=P3966388&BN=999&PN=2
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com

 

[Ron Allen]

kosta,
Probably one of the field names is a Reserved Keyword for the OleDb
driver. Try putting brackets ( [ ] pairs) around your field names and see
if that works. Access treats things differently by itself.

Ron Allen

 

[Guest]

Thatnks a lot! thats a good idea
ill try that...
kosta

-----Original Message-----
kosta,
Probably one of the field names is a Reserved Keyword for the OleDb
driver. Try putting brackets ( [ ] pairs) around your field names and see
if that works. Access treats things differently by itself.

Ron Allen

hmmm, that last message before yours was mine...
I assure you that there is no mismatch.... I have double
and triple checked...
also, why, if there is a mismatch, would I be able to use
that same INSERT from within access?

.

 

[Guest]

can you please point me to some page that explains this a
bit deeply please? ive been using my way ever since I
started...

 

[kosta]

well, that is what ive been tought to do in school, where i learned
C#....

Best Regards,
Kosta.

 

[kosta]

I have tried this method, and it works... thanks! apparently either
'Last' or 'First' are reserved....

Best Regards,
Kosta.

 

[kosta]

If you haven't tried, you just don't know. If you can't see anything
wrong, but people have suggested a way of testing something, why not
test it?


See http://www.pobox.com/~skeet/csharp/faq/#db.parameters

If you've been taught to embed values in SQL statements, btw, I would
have a very close look at what else you've been taught from the same
source.

wow, ive been posting from msdn... now i looked at it from google
groups since it finally worked, and I see that i maw maybe half of the
messages posted...

thanks to everyone who posted! I will try all of these!

 

[Jon Skeet [C# MVP]]

well, that is what ive been tought to do in school, where i learned
C#....

Then you'd be doing the students who come after you a favour if you
dropped your teachers an email to ask them to teach their students to
use parameters rather than inserting values directly into SQL
statements. It can help performance, security and correctness.

 

[kosta]

ok, ill do that.

 

[kosta]

ok, ill do that.