ExecSQL in a With problem

[PaidtheUmpire]

Can any body see the problem with this code... seriously i've been looking at it for an hour and still can't find it.

procedure TFrmTap2Control.FinishInsertion;
var TheLineData : string;
var CallNo, r   : integer;
begin
CallNo := CallMemo.Lines.Count - 2;
For r := 0 to CallNo do
  begin
  TheLineData := CallMemo.Lines[r];
//  If TheLineData <> '' then
  CallInsert.SQL.Clear;
  CallInsert.SQL.Add('INSERT INTO Call_Records (Client_Reference_Number, IMSI_Number,' + ' IMEI_Number, Phone_Number_Called, Start_Date_And_Time, Call_Length, Cost_To_Fastwave, Cost_To_Client, Location_Code, VoiceDataSMS, CTC_Currency, IncludedCall, Type, Charged_Length, FileName, LineNo, InvoiceType) VALUES (' + TheLineData + ');');
  CallInsert.ExecSQL;
  end;
end;

The TheLineData string is like so... (in one line)

1000103,901032140015983,300001001953390,0061732299576        ,09/04/05 08:55:54,000030,000000.308,2.2,00505,Data,AUD,Yes,IrdAusD,40,cdusa77FASTW03370.dat,4,Standard Data Calls

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

 

[towerbase]

What kind of error are you getting?

Is it a compiler error or warning ?

Is it a run time error ?

Is it an error returned from your DBMS ?

Which version of Delphi are you using ?

What DBMS are you using ?


My first suggestion is that some of fields in the VALUES clause should be in quotation marks. Non numeric fields, including date and times should be surrounded by quotes.

The best way to put quotes around a value is to use the QuotedStr function.

Or you should consider using the Params property of your SQL component.

It is possible that your date values are in the wrong format. For example, MySQL requires the date to be in the form 'yyyy-mm-dd'. Paradox requires 'mm/dd/yyyy'. However it depends on how the Call_Records table was created and which particular DBMS you are using.

Andrew
Hampshire, UK

 

[martinm]

(' + TheLineData + ');');


Try losing a '

 

[Zathras]

What DBMS are you using? I don't know of any that allows a column name like this:

   +IMEI_Number

Set a break point, use <Ctrl>+<F7> to bring up the Evaluate/Modify dialog. Evaluate
[tt]
CallInsert.SQL.Text
[/tt]
Copy and paste into your DBMS utility (E.g., Query Analyzer for SQL Server) format it and try to run it there. You should get a better error message to consider.

As towerbase indicated, much of your problem stems from not quoting the non-numeric values.

Consider using parameters.

 

[PaidtheUmpire]

I am getting a runtime error like so :

Parameter Object Improperly defined



I think it all boils down to this...

The SQL Insert

INSERT INTO Call_Records (Client_Reference_Number, IMSI_Number, IMEI_Number, Phone_Number_Called, Start_Date_And_Time, Call_Length, Cost_To_Fastwave, Cost_To_Client, Location_Code, VoiceDataSMS, CTC_Currency, IncludedCall, Type, Charged_Length, FileName, LineNo, InvoiceType) VALUES (1000020,'901033140037024','300001001445790','00254720962579       ','03/04/05 09:29:00',001639,000023.452,36.6212,'00640','','USD','Yes','IrdIntV',1640,'cdusa77FASTW03351.dat','4','Standard Voice Calls');#$D#$A

Where did the #$D#$A come from? THat i think is the problem.


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

 

[Zathras]

The #$D#$A is simply a carriage return / line feed sequence and is definitely NOT part of the problem.

Have you tried it without the trailing semi-colon?

"Parameter Object Improperly defined" generally means that Delphi has found what it thinks is a parameter and you have not set it up. That usually means a colon in front of a variable name, but the only colon is inside a quoted string (the time), so I don't think it's that. But perhaps the semi-colon is fooling it.

For the third time, WHAT DBMS ARE YOU USING?

Have you tried your statement in a utility (e.g. Query Analyzer) to see if it works there?

 

[svanels]

This reminds me of the classic problem:
How do you eat an elephant?

The answer: cut it in little pieces

Try to read/write 3 records first, if success expand...