• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Params.ParambyName problem?

Could someone tell me why I am having this problem. With the first statement, no problems, update occurs.
With the second statement, database error occurs. I have narrowed it down to this: I put an apostrophe in the English_Description.text for both statements. However the second statement fails when the last_upd_date and last_upd_source are done after the English_Description.text.

Update Statement 1:
QUERY.SQL.Add('UPDATE promotion');
QUERY.SQL.Add('Set last_upd_source = :last_upd_source,');
QUERY.SQL.Add('last_upd_date = :last_upd_date ,');
QUERY.SQL.Add('promo_start_date = "'+Start_Date.Text+'",');
QUERY.SQL.Add('promo_end_date = "'+End_Date.Text+'", ');
QUERY.SQL.Add('promo_desc_english = "'+English_Description.Text+'",');
QUERY.SQL.Add('promo_desc_french = "'+French_Description.Text+'",');
QUERY.SQL.Add('coupon_limit = '+Coupon_Limit.Text+',');
QUERY.SQL.Add('promotion_priority = '+Processing_Priority.Text+');
QUERY.SQL.Add(' WHERE promotion_number = '+promo_number);
QUERY.Params.ParamByName('last_upd_source').AsString := Get_User_Name;
QUERY.Params.ParamByName('last_upd_date').AsDate := Now;
QUERY.ExecSQL;


Update Statement 2:
QUERY.SQL.Add('UPDATE promotion');
QUERY.SQL.Add('Set
QUERY.SQL.Add('promo_start_date = "'+Start_Date.Text+'",');
QUERY.SQL.Add('promo_end_date = "'+End_Date.Text+'", ');
QUERY.SQL.Add('promo_desc_english = "'+English_Description.Text+'",');
QUERY.SQL.Add('promo_desc_french = "'+French_Description.Text+'",');
QUERY.SQL.Add('coupon_limit = '+Coupon_Limit.Text+',');
QUERY.SQL.Add('promotion_priority = '+Processing_Priority.Text+',');
QUERY.SQK.ADD('last_upd_source = :last_upd_source,');
QUERY.SQL.Add('last_upd_date =  :last_upd_date ');
QUERY.SQL.Add(' WHERE promotion_number = '+promo_number);
QUERY.Params.ParamByName('last_upd_source').AsString := Get_User_Name;
QUERY.Params.ParamByName('last_upd_date').AsDate := Now;
QUERY.ExecSQL;


Any comments would be appreciated.
0
HabBoy
Asked:
HabBoy
  • 3
  • 2
  • 2
  • +1
1 Solution
 
kretzschmarCommented:
use quotedstr instaed, like


QUERY.SQL.Add('promo_desc_english = "'+quotedstr(English_Description.Text)+'",');

use already AsDateTime rather than AsDate

btw, what database do u use?

0
 
HabBoyAuthor Commented:
Sybase 11.92 is what we have here.

Thanks for your suggestion. While it no longer fails it does insert double quotes into the database. Like if the description is Bob's, in the database is 'Bob''s'
0
 
HabBoyAuthor Commented:
It would seem to me that if I could put quotes around my params then this would solve the problem but I have not found a way to do that.
In the end I just might have to switch all my insert and update statements to be like my first one with the date_time and source being updated before any text fields that could have an apostrophe in them.

If kretz or anybody has any other ideas...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kretzschmarCommented:
??
if in sybase this char " is the sign for strings,
then use quotedstr as well

also there
QUERY.SQL.Add('promo_start_date = "'+Start_Date.Text+'",');
QUERY.SQL.Add('promo_end_date = "'+End_Date.Text+'", ');

if the dates-fields are are really
datefields rather than a text field,
then i guess sybase supports a function
for conversion string to date
(in oracle it is to_date(DateStr,FormatStr) )
you should use this function in your statement,
if available

(you see, i have no background about sybase)

hope ther is such a conversion-function

meikl ;-)
0
 
JimMcKeethCommented:
I find it is really helpful to dump the contents of the SQL.Text to the clipboard or a TMemo to make it easy to see what it will look like to the database or compare it to known working SQL.  You can also then paste into a database SQL tool.
0
 
esoftbgCommented:
Try that:

procedure Proc;
var
  T:    WideString;
begin
  T := ''
     + 'UPDATE promotion'
     + 'Set last_upd_source = :last_upd_source,'
     + 'last_upd_date = :last_upd_date ,'
     + 'promo_start_date ='
     + ''''' + Start_Date.Text + ''''' + ', '
     + 'promo_end_date ='
     + '''' + End_Date.Text + '''' + ', '
     + 'promo_desc_english='
     + '''' + English_Description.Text + '''' + ','
     + 'promo_desc_french='
     + '''' + French_Description.Text + '''' + ','
     + 'coupon_limit = '
     + '''' + Coupon_Limit.Text + '''' + ', '
     + 'promotion_priority= '
     + '''' + Processing_Priority.Text + ''''
     + ' WHERE promotion_number = '
     + promo_number
     + '';
  QUERY.SQL.Text := T;
  QUERY.Params.ParamByName('last_upd_source').AsString := Get_User_Name;
  QUERY.Params.ParamByName('last_upd_date').AsDate := Now;
  QUERY.ExecSQL;
end;
0
 
HabBoyAuthor Commented:
Thanks for the suggestion Jim. Could you tell me how to dump the contents to the clipboard and/or a TMemo?
0
 
JimMcKeethCommented:
To past them in the clipboard you need to add ClipBrd to you uses clause.  Then add the following code:

ClipBoard.AsText := QUERY.SQL.Text;

Put it after the last QUERY.SQL.Add line.  ClipBoard is a singleton that you do not need to create or initialize (just like Application).

To put it in a Memo just put a memo on your form and add this code:

Memo1.Lines.Text := QUERY.SQL.Text;

Where Memo1 is the name of the memo.  Place this in the same place as the clipboard code.  

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now