asked on

Delphi Tmemo with more than one line into Mysql

Dear Experts,

I have some fields on a form, let's say one Tedit and Tmemo fileds.

Edit1 has as text "Customer 1"
Memo1 has as text

"south street 1
Las vegas"

Now I want to insert them into MySQL and I use the following statement:

query.SQL.text := 'insert into test (name, address) VALUES ("'+edit1.text + '","' + Memo1.Text + "'";

But I always get an error that the query was not successfull... getting the SQL string it shows:

'insert into test (name, address) VALUES ("Customer1",""south street1
Las Vegas"");

It is like when the memo contains more than one line then getting the memo text will add quotes at the
beginning and at the end...

Any idea on how to solve this issue?

Thank you.
Dave

SOLUTION

Pierre Cornelius

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dcrudo

ASKER

Hi,

sorry... it was a mistake in writing the string ... I didn't copy paste...just copy...because my query is a little more complex ;)

yes, my string is delimited as you wrote... and it gives me the error above...

any idea?

Thank you!!

Pierre Cornelius

What are the field dtypes for name and address?

dcrudo

ASKER

in the database the fields types are:

name : varchar 255
address : text

to give you a better idea let me explain more:

The following is the resulting query:

insert into ana_customers (id,firstname, lastname, civils, address, zip, city, tel_home,tel_mobile, tel_fax, email, no_avs, doctor_name, insurance_name, insurance_number, treatment_def, diagnosis, agreed_hours, def_positions, active,birth, treatment_start, treatment_end) VALUES ("109","Dave","Ohara","Married",""Las vegas"","","","","","","","","","","","","","90","","-1","1980-05-25","0000-00-00","0000-00-00")

as you see near 'Las Vegas' quotes are doubled... this is the only field of type MEMO... to insert those fields i use the following function:

Function TDM1.dbins(tablex,variablex : String; valuex: Variant): string;
var sql : string;

Begin

valuex := ReplaceStr(valuex,'''',''''''); // Double quotes in case one field contains them
valuex := ReplaceStr(valuex,char(186),'","'); //use ASCII code 186 to delimit fields

sql := 'insert into ' + tablex + ' ('+ variablex + ') VALUES ("' + valuex + '")';
qry_dbins.SQL.Text := sql;

Data.DM1.InfoBox(sql,1,1);

try
begin
qry_dbins.Execute;
result := '0';
end;
Except On E:Exception Do
begin
Data.DM1.InfoBox('SQL Error: '+E.message,1,0);
Data.DM1.InfoBox('------------------------------------------------------------------',1,0);
Data.DM1.InfoBox(sql,1,1);

result := '1';
end;
end;
end;

To collect all information from the form, I put all fields in a TstringList:

procedure TForm1.suiButton3Click(Sender: TObject);
var strlVal : TstringList;
x : Integer;
Fields : String;

begin
// Start Customer Insert

strlVal := TStringList.Create;
strlVal.Clear;
strlVal.Delimiter := char(186);

strlVal.Add(Data.DM1.nextId('cus'));
strlVal.Add(ed_c_firstname.Text);
strlVal.Add(ed_c_lastname.Text);
strlVal.Add(ed_c_civils.Text);
strlVal.Add(ed_c_address.EditingValue);
strlVal.Add(ed_c_zip.Text);
strlVal.Add(ed_c_city.Text);
strlVal.Add(ed_c_tel_home.Text);
strlVal.Add(ed_c_tel_mobile.Text);
strlVal.Add(ed_c_tel_fax.Text);
strlVal.Add(ed_c_email.Text);
strlVal.Add(ed_c_no_avs.Text);
strlVal.Add(ed_c_doctor_name.Text);
strlVal.Add(ed_c_insurance_name.Text);
strlVal.Add(ed_c_insurance_number.Text);
strlVal.Add(ed_c_treatment_def.Text);
strlVal.Add(ed_c_diagnosis.Text);
strlVal.Add(ed_c_agreed_hours.Text);
strlVal.Add(ed_c_def_positions.Text);
strlVal.Add(BoolToStr(ed_c_active.Checked));

strlVal.add(FormatDateTime('yyyy/mm/dd',ed_c_birth.CurrentDate));
strlVal.Add(FormatDateTime('yyyy/mm/dd',ed_c_treatment_start.CurrentDate));
strlVal.Add(FormatDateTime('yyyy/mm/dd',ed_c_treatment_end.CurrentDate));

Fields := 'id,firstname, lastname, civils, address, zip, city, tel_home,'+
'tel_mobile, tel_fax, email, no_avs, doctor_name, insurance_name, '+
'insurance_number, treatment_def, diagnosis, agreed_hours, def_positions, active,'+
'birth, treatment_start, treatment_end';

try
Begin
Data.DM1.dbins('ana_customers',Fields, strlVal.DelimitedText);
end;
except
Begin

end;
end;

end;

Pierre Cornelius

Is ed_c_address a TMemo? I'm not familiar with the "EditingValue". Should this not be

strlVal.Add(ed_c_address.Text);

dcrudo

ASKER

With:

strlVal.Add(ed_c_address.Text);

I get the same result... the quotes are doubled... I wonder if this could be an effect of the TStringList where I store the variables... that when it finds a 'new line' than it will add the quotes to everything...

any idea?

thank you!

Pierre Cornelius

Looks fine to me. Only thing I can think of is that the string you add to the Stringlist contains the "

After this call: strlVal.Add(ed_c_address.EditingValue);

Check if StrlVal[4]
contains this: "Las vegas"
or this: Las vegas

Pierre Cornelius

Can you show me your ReplaceStr function

ASKER CERTIFIED SOLUTION

Jacco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dcrudo

ASKER

Problem solved!

This is what happens:

when a TStringlist contains a new line like the following:

Street 1
las vegas

quotes are automatically added to the string (it does not happens if the line is only one!)

To solve it:

I've found this property on the TstringList object: StringList1.QuoteChar

StringList1.Quotechar := char(187);

and then when I have the complete insert statement ready, before executing the query I do:

ReplaceStr(valuex,char(187),'');

and will do the trick...

char 187 is a symbol that usually is not used when writing an addres ;)

Thank you very much!!

Dave.

dcrudo

ASKER

Jacco,

thx a lot for your time too!!

I will assign to both of you some points... thank you very much!!

Dave.