Oh god, more SQL ¬__¬

[2710]

Hi,

so I effectively have this code:

Insert into GiftAid
(GiftAidID,Surname, Title, PostCode)
values (22,'q','MR', 'q');

and depending on whether I put quotes around 'GiftAid' (my table name) I get different errors:




woops posted the same picture, well the other says:

Syntax error in query. Incomplete query clause.

I have tried so many different ways of adding apostrophe here, adding sqaure brackets etc that I have almost given up. Why does SQL have to be so awkward >__>

Any help appreciated (wingedpanther )

Thanks

[Jordan]

I don't see anything wrong with your query. What does your Pascal/Delphi code around it look like? The code that executes the query... Are you using TAdoDataSet?

[WingedPanther]

When doing an insert, you use the ExecSQL method instead of the Open method.

[2710]

Ok, so now look at my code:

adoquery2.SQL.Clear;
adoquery2.SQL.Add('Insert into "GiftAid"');
adoquery2.SQL.Add('(GiftAidID, Title, Initial, Surname, Address, PostCode, E-Mail, DateR)');
adoquery2.SQL.Add('values');
adoquery2.SQL.Add('(' + ''''+Spinedit1.Text+''''  + ',' + ''''+combobox1.Text+'''' + ',' + '''' +Combobox2.Text+ '''' + ',' + ''''+Edit1.Text+'''' + ',' + ''''+Edit2.Text+'''' + ',' + ''''+Edit3.Text+'''' + ',' + ''''+Edit4.Text+'''' + ',' + ''''+Edit5.Text+'''' + ');');
adoquery2.execsql

But it still doesn't work.I keep getting an error message. Syntax error in query. Incomplete Query clause.



@Jordan, yes, I am using a dataset..i think. I have a Adoquery, and a Datasource which is linked to the query through dataset

EDIT: Is it something to do with putting my tablename in quotes? But if I dont, I get an 'INSERT INTO' error, but on most examples, they dont have quotes.

EDIT: Can it be something to do with 2007 Access' protection thingy?Well, Ima go to sleep now, so Ill figure it in the morning

[WingedPanther]

When debugging SQL, I tend to either add a showmessage (like below) or log the failed SQL to file using a try/except. What SQL is it generating?

adoquery2.SQL.Clear;
adoquery2.SQL.Add('Insert into "GiftAid"');
adoquery2.SQL.Add('(GiftAidID, Title, Initial, Surname, Address, PostCode, E-Mail, DateR)');
adoquery2.SQL.Add('values');
adoquery2.SQL.Add('(' + ''''+Spinedit1.Text+''''  + ',' + ''''+combobox1.Text+'''' + ',' + '''' +Combobox2.Text+ '''' + ',' + ''''+Edit1.Text+'''' + ',' + ''''+Edit2.Text+'''' + ',' + ''''+Edit3.Text+'''' + ',' + ''''+Edit4.Text+'''' + ',' + ''''+Edit5.Text+'''' + ');');
showmessage(adoquery2.SQL.text);
adoquery2.execsql

[2710]

Here ya go:



Hmm, I still don't see where the mistake is. I put the number in quotes because it is stored as a text in the database. Even if I don't put in a quote, it still gives an error. Hmm...

Here is the error:

[WingedPanther]

Depending on the type of database, you may have to exclude the ; at the end of your statement, or not put single quotes around your table name. Can you play around with the SQL syntax using a different tool?

[2710]

YESSSS! I figured it out after about an hour. GAWD!

The problem was the column 'E-mail'. For some reason, SQL doesn't like the hypen (-) sign and therefore gives an error. I just changed it to Email and voila! Well I guess its good that this happened, since now I will learn from it, but also, for SUCH a little problem !!!!!! Lol

Thanks guys!

[Orjan]

yeah. play safe, just use a-z and no reserved words as field names, and it will work good...

[WingedPanther]

Underscores also usually work well for field/table names.