?
Solved

Params.ParambyName problem?

Posted on 2003-03-06
8
Medium Priority
?
266 Views
Last Modified: 2010-04-04
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
Comment
Question by:HabBoy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8080475
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
 

Author Comment

by:HabBoy
ID: 8080710
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
 

Author Comment

by:HabBoy
ID: 8081025
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 8082062
??
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
 

Expert Comment

by:JimMcKeeth
ID: 8085045
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8087128
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
 

Author Comment

by:HabBoy
ID: 8088096
Thanks for the suggestion Jim. Could you tell me how to dump the contents to the clipboard and/or a TMemo?
0
 

Accepted Solution

by:
JimMcKeeth earned 300 total points
ID: 8089727
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question