SQL Insertion into TABLE with "NOT EXISTS"

[PaidtheUmpire]

I am trying to put through some data into an Access Table from Delphi 7, and am getting the following error:

"Missing Semicolon ( at the end of SQL Statement"

Here is the code... for the SQL statement. All the variables are correctly formatted.

But i am wondering if you could help me find the error of my ways.

SystemData.Junkish.SQL.Clear;
SystemData.JunkIsh.SQL.Add('INSERT INTO Call_Records VALUES ' + QuotedStr(CRN) + ', ' + 
   QuotedStr(IMSI) + ', ' + QuotedStr(IMEI) + ', ' + QuotedStr(PhoneNo) + ', ' + 
   QuotedStr(FullTime) + ', ' + QuotedStr(ChargeUnit) + ', ' + QuotedStr(CostD + '.' + 
   CostC) + ', ' + FloatToStr(CostClient) + ', ' + QuotedStr(LocationCode) + ', ' + 
   QuotedStr(DataType) + ', ' + QuotedStr(CUR) + ', ' + QuotedStr('Yes') + ', ' + 
   QuotedStr(Qwerty) + ', ' + IntToStr(Fakeness) + ', ' + QuotedStr(Part2) + ', ' + 
   QuotedStr(IntToStr(LineNo)) + ', ' + QuotedStr(InvoiceWords) + 'WHERE NOT EXISTS(SELECT
   Call_Records.Client_Reference_Number, Call_Records.IMSI_Number, 
   Call_Records.Phone_Number_Called, Call_Records.Start_Date_And_Time FROM Call_Records' + 
   'WHERE (((Call_Records.Client_Reference_Number)=[CRN]) AND ((Call_Records.IMSI_Number)=[IMSI])
   AND ((Call_Records.Phone_Number_Called)=[Phone]) AND ((Call_Records.Start_Date_And_Time)=[Time]));');
SystemData.Junkish.ExecSQL;

Thanks,
Ricko

PS. Sorry about the formatting of the SQL code



Delphi, Delphi, Delphi. Oi! Oi! Oi!

 

[whosrdaddy]

rewrite your query and use parameters, this way you can test it in access and use it in delhpi, it will be less error prone...

--------------------------------------
What You See Is What You Get

 

[towerbase]

I am not familiar with Access or the variations of SQL it supports but I would have been surprised if it allows syntax of the form:

INSERT INTO table VALUES ( ... ) WHERE condition

which I think is what you are trying to do.

In situations such as these it would have been helpful to have shown the actual SQL statement that the ExecSQL procedure is trying to execute.

Andrew
Hampshire, UK

 

[svanels]

INSERT INTO Call_Records VALUES
WHERE NOT EXISTS(SELECT
WHERE
AND
AND
AND

This sql statement is too complex, you are trying to do too much at a time.
Break it in pieces, retrieve the data you need, display and test it before you insert. I have my doubts about this nested select.
If you want to make the SQL commands visible, open access, build the query, and examine the SQL generated, probably with some modifications it can be used in Delph (copy/paste)

Steven