A question regarding SQL Statements, ADO, and Text

[kcostain]

Hello All,

I have an interesting problem with designing my SQL statements around the data inside. Namely, the text fo a Memo control. If I were to add this data into my database:

Memo1.Text which is:

----- Start -----
This Kevin's line of text
on two "lines".
-----Finish------

I'd probably do this:

ADOCommand1.CommandText := 'INSERT INTO [Memos] ([MemoData])
Values("'+Memo1.Text+'")' ;
ADOCommand1.Execute;

If I try to do that I get all sorts of errors that relate to the use of the " and ' characters (although they don't say that of course)..

What I'd like to know is, is there a list of characters we need to stay away from and or is there a .Trim like function we can call on aMemo stream of text to rip out (normalize) these characters?

Thanks,
Kevin

 

[whosrdaddy]

why not do this :

ADOCommand1.CommandText := Format('INSERT INTO %s (%s) VALUES (''%s'')',[Yourtablename,YourColumn,Memo1.text]);

Small question, what database server do you use?? (SQL,ACCESS,...)



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

 

[kcostain]

whosrdaddy,

I'm just using an Access file (mdb) and ADO. I will try your suggestion, thanks for the help.

Kevin

 

[LucieLastic]

Can you not just use Quotedstr?

ADOCommand1.CommandText := 'INSERT INTO [Memos] ([MemoData])
Values('+QuotedStr(Memo1.Text)+')' ;
ADOCommand1.Execute;

 

[towerbase]

The best way to do this kind of thing is to use the QuotedStr function. QuotedStr handles the awkward quote characters. So the example given by whosrdaddy would become:

ADOCommand1.CommandText := Format ( 'INSERT INTO %s (%s) VALUES (%s)',[ Yourtablename, YourColumn, QuotedStr(Memo1.text)] );

Andrew
Hampshire, UK

 

[whosrdaddy]

good one andrew, should have thought of myself )

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

 

[kcostain]

who ha! You guys are rockin today on this one..

 

[kcostain]

That seems to take care of the ' characters, but when the " and the : characters are in the memo text I get the error

"Parameter object is imporperly defined"

I see for the ', the QuotedStr function is adding an extra ' - but it does not do this for the other two. Any Ideas?

Thanks,
Kevin

 

[kcostain]

I found the solution to this. You need to use parmeters and the special charaters will be taken care of.

My SQL statement went from this:

ADOCommand1.CommandText := 'UPDATE [report] SET [customer]="'+Edit5.Text+'",'+ ' [invoicenum] = "'+Edit1.Text+'", [datet]="'+DatetoStr(DateTimePicker1.Date)+'",[billhour]="'+Edit2.Text + '",[location]="'+ComboBox2.Text +'", [hardwaresold]="'+ComboBox3.Text   +'", [summary]='+ QuotedStr(Edit3.Text) + ', [invoicefn]="'+Edit4.Text +'", [prememo]='+QuotedStr(Memo1.Text) +', [durmemo]='+QuotedStr(Memo2.Text) +', [postmemo]='+QuotedStr(Memo3.Text) + ', [priority]="'+ComboBox4.Text + '" WHERE [id] = '+ Label13.Caption;

to this:

ADOCommand1.CommandText := 'UPDATE [report] SET [customer]=:p0, [invoicenum]=:p1, [datet]=:p2, [billhour]=:p3, [location]=:p4, [hardwaresold]=:p5, [summary]=:p6, [invoicefn]=:p7, [prememo]=:p8, [durmemo]=:p9, [postmemo]=:p10, [priority]=:p11 WHERE [id] = '+ Label13.Caption;

ADOCommand1.Parameters.ParamByName('p0').Value := Edit5.Text;
ADOCommand1.Parameters.ParamByName('p1').Value := Edit1.Text;
ADOCommand1.Parameters.ParamByName('p2').Value := DatetoStr(DateTimePicker1.Date);
ADOCommand1.Parameters.ParamByName('p3').Value := Edit2.Text;
ADOCommand1.Parameters.ParamByName('p4').Value := ComboBox2.Text;
ADOCommand1.Parameters.ParamByName('p5').Value := ComboBox3.Text;
ADOCommand1.Parameters.ParamByName('p6').Value := Edit3.Text;
ADOCommand1.Parameters.ParamByName('p7').Value := Edit4.Text;
ADOCommand1.Parameters.ParamByName('p8').Value := Memo1.Text;
ADOCommand1.Parameters.ParamByName('p9').Value := Memo2.Text;
ADOCommand1.Parameters.ParamByName('p10').Value := Memo3.Text;
ADOCommand1.Parameters.ParamByName('p11').Value := ComboBox4.Text;

Note: I did no pre-declare the # before using them...

THanks,
Kevin