Board index » delphi » Delphi to SQL Param problems

Delphi to SQL Param problems


2004-02-10 12:08:13 AM
delphi51
Hello,
I am trying to pass a variable between my Delphi app and a SQL db. I have two variables, 'FIRST' and "SECOND' which are defined by :Shift. I can pass them into SQL and retrieve results without error (btnShift1, btnShift2). BUT, Now I want to return results from both of these variables, 'FIRST' and 'SECOND' and have been unable to do so (btnShiftboth). Below I have listed my code and also the query for your review. Thanks guys!
--------------------------------------
unit ActiveDatabaseImpl1;
{$WARN SYMBOL_PLATFORM OFF}
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ActiveX, AxCtrls, ActiveDatabaseProj1_TLB, StdVcl, StdCtrls, ADODB, DB,
Grids, DBGrids, {*word*249}gine, Series, ExtCtrls, TeeProcs, Chart, DbChart,
DBCtrls, GanttCh, ComCtrls, ovcbase, ovcdlg, ovccaldg, EsEdPop, EsEdCal;
type
TActiveDatabase = class(TActiveForm, IActiveDatabase)
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
ADOQuery2: TADOQuery;
ComboBoxKanban: TComboBox;
ComboBoxZone: TComboBox;
ADOQuery3: TADOQuery;
GroupBox1: TGroupBox;
btnToday: TRadioButton;
btnMonthToDate: TRadioButton;
DBChart1: TDBChart;
Series1: TLineSeries;
Series2: TLineSeries;
DateTimePicker1: TDateTimePicker;
Label1: TLabel;
Label2: TLabel;
GroupBox2: TGroupBox;
btnShift1: TRadioButton;
btnShift2: TRadioButton;
btnBoth: TRadioButton;
Label4: TLabel;
procedure Shift1Click(Sender: TObject);
procedure Shift2Click(Sender: TObject);
procedure ActiveFormCreate(Sender: TObject);
procedure ComboBoxKanbanChange(Sender: TObject);
procedure ComboBoxZoneChange(Sender: TObject);
procedure btnTodayClick(Sender: TObject);
procedure btnMonthToDateClick(Sender: TObject);
procedure DateTimePicker1CloseUp(Sender: TObject);
procedure DateTimePicker1KeyDown(Sender: TObject; var Key: Word;
Shift: TShiftState);
procedure btnBothClick(Sender: TObject);
procedure btnShift1Click(Sender: TObject);
procedure btnShift2Click(Sender: TObject);
private
{ Private declarations }
FEvents: IActiveDatabaseEvents;
procedure ActivateEvent(Sender: TObject);
procedure ClickEvent(Sender: TObject);
procedure CreateEvent(Sender: TObject);
procedure DblClickEvent(Sender: TObject);
procedure DeactivateEvent(Sender: TObject);
procedure DestroyEvent(Sender: TObject);
procedure KeyPressEvent(Sender: TObject; var Key: Char);
procedure PaintEvent(Sender: TObject);
protected
{ Protected declarations }
procedure DefinePropertyPages(DefinePropertyPage: TDefinePropertyPage); override;
procedure EventSinkChanged(const EventSink: IUnknown); override;
function Get_Active: WordBool; safecall;
function Get_AlignDisabled: WordBool; safecall;
function Get_AutoScroll: WordBool; safecall;
function Get_AutoSize: WordBool; safecall;
function Get_AxBorderStyle: TxActiveFormBorderStyle; safecall;
function Get_Caption: WideString; safecall;
function Get_Color: OLE_COLOR; safecall;
function Get_Cursor: Smallint; safecall;
function Get_DoubleBuffered: WordBool; safecall;
function Get_DropTarget: WordBool; safecall;
function Get_Enabled: WordBool; safecall;
function Get_Font: IFontDisp; safecall;
function Get_HelpFile: WideString; safecall;
function Get_HelpKeyword: WideString; safecall;
function Get_HelpType: TxHelpType; safecall;
function Get_KeyPreview: WordBool; safecall;
function Get_PixelsPerInch: Integer; safecall;
function Get_PrintScale: TxPrintScale; safecall;
function Get_Scaled: WordBool; safecall;
function Get_Visible: WordBool; safecall;
function Get_VisibleDockClientCount: Integer; safecall;
procedure _Set_Font(var Value: IFontDisp); safecall;
procedure Set_AutoScroll(Value: WordBool); safecall;
procedure Set_AutoSize(Value: WordBool); safecall;
procedure Set_AxBorderStyle(Value: TxActiveFormBorderStyle); safecall;
procedure Set_Caption(const Value: WideString); safecall;
procedure Set_Color(Value: OLE_COLOR); safecall;
procedure Set_Cursor(Value: Smallint); safecall;
procedure Set_DoubleBuffered(Value: WordBool); safecall;
procedure Set_DropTarget(Value: WordBool); safecall;
procedure Set_Enabled(Value: WordBool); safecall;
procedure Set_Font(const Value: IFontDisp); safecall;
procedure Set_HelpFile(const Value: WideString); safecall;
procedure Set_HelpKeyword(const Value: WideString); safecall;
procedure Set_HelpType(Value: TxHelpType); safecall;
procedure Set_KeyPreview(Value: WordBool); safecall;
procedure Set_PixelsPerInch(Value: Integer); safecall;
procedure Set_PrintScale(Value: TxPrintScale); safecall;
procedure Set_Scaled(Value: WordBool); safecall;
procedure Set_Visible(Value: WordBool); safecall;
public
{ Public declarations }
procedure Initialize; override;
end;
implementation
uses ComObj, ComServ;
{$R *.DFM}
{ TActiveDatabase }
procedure TActiveDatabase.DefinePropertyPages(DefinePropertyPage: TDefinePropertyPage);
begin
{ Define property pages here. Property pages are defined by calling
DefinePropertyPage with the class id of the page. For example,
DefinePropertyPage(Class_ActiveDatabasePage); }
end;
procedure TActiveDatabase.EventSinkChanged(const EventSink: IUnknown);
begin
FEvents := EventSink as IActiveDatabaseEvents;
inherited EventSinkChanged(EventSink);
end;
procedure TActiveDatabase.Initialize;
begin
inherited Initialize;
OnActivate := ActivateEvent;
OnClick := ClickEvent;
OnCreate := CreateEvent;
OnDblClick := DblClickEvent;
OnDeactivate := DeactivateEvent;
OnDestroy := DestroyEvent;
OnKeyPress := KeyPressEvent;
OnPaint := PaintEvent;
end;
Function Today: String;
Begin
Result := FormatDateTime('yyyy-mm-dd hh:mm:ss.mss',now);
End;
function TActiveDatabase.Get_Active: WordBool;
begin
Result := Active;
end;
function TActiveDatabase.Get_AlignDisabled: WordBool;
begin
Result := AlignDisabled;
end;
function TActiveDatabase.Get_AutoScroll: WordBool;
begin
Result := AutoScroll;
end;
function TActiveDatabase.Get_AutoSize: WordBool;
begin
Result := AutoSize;
end;
function TActiveDatabase.Get_AxBorderStyle: TxActiveFormBorderStyle;
begin
Result := Ord(AxBorderStyle);
end;
function TActiveDatabase.Get_Caption: WideString;
begin
Result := WideString(Caption);
end;
function TActiveDatabase.Get_Color: OLE_COLOR;
begin
Result := OLE_COLOR(Color);
end;
function TActiveDatabase.Get_Cursor: Smallint;
begin
Result := Smallint(Cursor);
end;
function TActiveDatabase.Get_DoubleBuffered: WordBool;
begin
Result := DoubleBuffered;
end;
function TActiveDatabase.Get_DropTarget: WordBool;
begin
Result := DropTarget;
end;
function TActiveDatabase.Get_Enabled: WordBool;
begin
Result := Enabled;
end;
function TActiveDatabase.Get_Font: IFontDisp;
begin
GetOleFont(Font, Result);
end;
function TActiveDatabase.Get_HelpFile: WideString;
begin
Result := WideString(HelpFile);
end;
function TActiveDatabase.Get_HelpKeyword: WideString;
begin
Result := WideString(HelpKeyword);
end;
function TActiveDatabase.Get_HelpType: TxHelpType;
begin
Result := Ord(HelpType);
end;
function TActiveDatabase.Get_KeyPreview: WordBool;
begin
Result := KeyPreview;
end;
function TActiveDatabase.Get_PixelsPerInch: Integer;
begin
Result := PixelsPerInch;
end;
function TActiveDatabase.Get_PrintScale: TxPrintScale;
begin
Result := Ord(PrintScale);
end;
function TActiveDatabase.Get_Scaled: WordBool;
begin
Result := Scaled;
end;
function TActiveDatabase.Get_Visible: WordBool;
begin
Result := Visible;
end;
function TActiveDatabase.Get_VisibleDockClientCount: Integer;
begin
Result := VisibleDockClientCount;
end;
procedure TActiveDatabase._Set_Font(var Value: IFontDisp);
begin
SetOleFont(Font, Value);
end;
procedure TActiveDatabase.ActivateEvent(Sender: TObject);
begin
if FEvents <>nil then FEvents.OnActivate;
end;
procedure TActiveDatabase.ClickEvent(Sender: TObject);
begin
if FEvents <>nil then FEvents.OnClick;
end;
procedure TActiveDatabase.CreateEvent(Sender: TObject);
begin
if FEvents <>nil then FEvents.OnCreate;
end;
procedure TActiveDatabase.DblClickEvent(Sender: TObject);
begin
if FEvents <>nil then FEvents.OnDblClick;
end;
procedure TActiveDatabase.DeactivateEvent(Sender: TObject);
begin
if FEvents <>nil then FEvents.OnDeactivate;
end;
procedure TActiveDatabase.DestroyEvent(Sender: TObject);
begin
if FEvents <>nil then FEvents.OnDestroy;
end;
procedure TActiveDatabase.KeyPressEvent(Sender: TObject; var Key: Char);
var
TempKey: Smallint;
begin
TempKey := Smallint(Key);
if FEvents <>nil then FEvents.OnKeyPress(TempKey);
Key := Char(TempKey);
end;
procedure TActiveDatabase.PaintEvent(Sender: TObject);
begin
if FEvents <>nil then FEvents.OnPaint;
end;
procedure TActiveDatabase.Set_AutoScroll(Value: WordBool);
begin
AutoScroll := Value;
end;
procedure TActiveDatabase.Set_AutoSize(Value: WordBool);
begin
AutoSize := Value;
end;
procedure TActiveDatabase.Set_AxBorderStyle(
Value: TxActiveFormBorderStyle);
begin
AxBorderStyle := TActiveFormBorderStyle(Value);
end;
procedure TActiveDatabase.Set_Caption(const Value: WideString);
begin
Caption := TCaption(Value);
end;
procedure TActiveDatabase.Set_Color(Value: OLE_COLOR);
begin
Color := TColor(Value);
end;
procedure TActiveDatabase.Set_Cursor(Value: Smallint);
begin
Cursor := TCursor(Value);
end;
procedure TActiveDatabase.Set_DoubleBuffered(Value: WordBool);
begin
DoubleBuffered := Value;
end;
procedure TActiveDatabase.Set_DropTarget(Value: WordBool);
begin
DropTarget := Value;
end;
procedure TActiveDatabase.Set_Enabled(Value: WordBool);
begin
Enabled := Value;
end;
procedure TActiveDatabase.Set_Font(const Value: IFontDisp);
begin
SetOleFont(Font, Value);
end;
procedure TActiveDatabase.Set_HelpFile(const Value: WideString);
begin
HelpFile := String(Value);
end;
procedure TActiveDatabase.Set_HelpKeyword(const Value: WideString);
begin
HelpKeyword := String(Value);
end;
procedure TActiveDatabase.Set_HelpType(Value: TxHelpType);
begin
HelpType := THelpType(Value);
end;
procedure TActiveDatabase.Set_KeyPreview(Value: WordBool);
begin
KeyPreview := Value;
end;
procedure TActiveDatabase.Set_PixelsPerInch(Value: Integer);
begin
PixelsPerInch := Value;
end;
procedure TActiveDatabase.Set_PrintScale(Value: TxPrintScale);
begin
PrintScale := TPrintScale(Value);
end;
procedure TActiveDatabase.Set_Scaled(Value: WordBool);
begin
Scaled := Value;
end;
procedure TActiveDatabase.Set_Visible(Value: WordBool);
begin
Visible := Value;
end;
procedure TActiveDatabase.ActiveFormCreate(Sender: TObject);
begin
//ShowMessage (Today);
//ShowMessage ('ActiveFormCreate');
ADOQuery1.Parameters[0].Value := (Now) ;
ADOQuery1.Parameters[1].Value := 'FIRST' ;
//bring in the values from KanbanNumbers into ComboBoxKanban
ADOQuery2.Open;
while not ADOQuery2.Eof do
begin
ComboBoxKanban.Items.Add (ADOQuery2.Fields [0].AsString);
ADOQuery2.Next;
end;
ComboBoxKanban.ItemIndex := 0; //set default value to 0
ADOQuery1.Parameters[2].Value := ComboBoxKanban.Items [0];
//bring in the values from Zone into ComboBoxZone
ADOQuery3.Open;
while not ADOQuery3.Eof do
begin
ComboBoxZone.Items.Add (ADOQuery3.Fields [0].AsString);
ADOQuery3.Next;
end;
ComboBoxZone.ItemIndex := 1; //set default value to Zone B
ADOQuery1.Parameters[3].Value := ComboBoxZone.Items [1];
//ShowMessage(ADOQuery1.Parameters[0].Value);
//ShowMessage(ADOQuery1.Sql.Text);
ADOQuery1.Open;
end;
// click Shift 1
procedure TActiveDatabase.Shift1Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'FIRST' ;
ADOQuery1.Open;
end;
// Click Shift 2
procedure TActiveDatabase.Shift2Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'SECOND' ;
ADOQuery1.Open;
end;
//OnKanbanChange pass dropdown value to query parameter
procedure TActiveDatabase.ComboBoxKanbanChange(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[2].Value := ComboBoxKanban.Items [ComboBoxKanban.ItemIndex];
ADOQuery1.Open;
end;
//OnKanbanChange pass dropdown value to query parameter
procedure TActiveDatabase.ComboBoxZoneChange(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[3].Value := ComboBoxZone.Items [ComboBoxZone.ItemIndex];
ADOQuery1.Open;
end;
//DateSelected Btn
procedure TActiveDatabase.btnTodayClick(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[0].Value := now;
ADOQuery1.Open;
end;
procedure TActiveDatabase.btnMonthToDateClick(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[0].Value := (Now-30);
ADOQuery1.Open;
end;
procedure TActiveDatabase.DateTimePicker1CloseUp(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[0].Value := DateTimePicker1.Date;
ADOQuery1.Open;
end;
procedure TActiveDatabase.DateTimePicker1KeyDown(Sender: TObject;
var Key: Word; Shift: TShiftState);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[0].Value := DateTimePicker1.Date;
ADOQuery1.Open;
end;
procedure TActiveDatabase.btnShift1Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'First' ;
ADOQuery1.Open;
end;
procedure TActiveDatabase.btnShift2Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'Second' ;
ShowMessage (ADOQuery1.Parameters[1].Value);
ADOQuery1.Open;
end;
procedure TActiveDatabase.btnBothClick(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'First,Second' ;
ShowMessage (ADOQuery1.Parameters[1].Value);
ADOQuery1.Open;
end;
initialization
TActiveFormFactory.Create(
ComServer,
TActiveFormControl,
TActiveDatabase,
Class_ActiveDatabase,
1,
'',
OLEMISC_SIMPLEFRAME or OLEMISC_ACTSLIKELABEL,
tmApartment);
end.
----------------------------------------------------
SELECT
M.TimeBeforeDepletion*60 AS TBDSecs,
L.Shift,
L.Zone,
L.KanbanNumber,
L.TimeRequested,
L.TimePrinted,
L.ElapsedTimeToPrint,
L.TimeStamp_Received
FROM Log L WITH (NOLOCK)
JOIN Master M WITH (NOLOCK)
ON L.KanbanNumber = M.KanbanNumber
and L.Zone = M.Zone
WHERE
L.TimeRequested>= :TimeRequested and
L.Shift IN (:Shift) and
L.KanbanNumber = :KanbanNumber and
L.Zone = :Zone and
L.TimePrinted is not NULL
ORDER BY L.TimeRequested
 
 

Re:Delphi to SQL Param problems

Also,
I am using Delphi 6 and MS SQL 2K.
-sorry about not pressing enter on last post!
 

Re:Delphi to SQL Param problems

Nathan S writes:
Quote
I am trying to pass a variable between my Delphi app and a SQL db. I
have two variables, 'FIRST' and "SECOND' which are defined by :Shift.
I can pass them into SQL and retrieve results without error
(btnShift1, btnShift2). BUT, Now I want to return results from both
of these variables, 'FIRST' and 'SECOND' and have been unable to do
so (btnShiftboth). Below I have listed my code and also the query
for your review. Thanks guys! --------------------------------------
[all kinds of very unnecessary stuff snipped, please quote only what is
relevant to your problem]
Quote
procedure TActiveDatabase.btnShift1Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'First' ;
ADOQuery1.Open;
end;
procedure TActiveDatabase.btnShift2Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'Second' ;
ShowMessage (ADOQuery1.Parameters[1].Value);
ADOQuery1.Open;
end;
procedure TActiveDatabase.btnBothClick(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.Parameters[1].Value := 'First,Second' ;
ShowMessage (ADOQuery1.Parameters[1].Value);
ADOQuery1.Open;
end;
----------------------------------------------------
WHERE
L.TimeRequested>= :TimeRequested and
L.Shift IN (:Shift) and
You cannot pass multiple values to a single parameter. The resulting query
ends up as
L.Shift IN ('First,Second')
but it needs to be
L.Shift IN ('First', 'Second')
You will have to manually code this part instead of using parameters (but
continue using parameters for the rest).
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

Re:Delphi to SQL Param problems

Thanks Wayne,
How would I go about manually coding this part? Im sorry about all the questions, but I am obviously new to all this.
Thanks again!
nathan
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes:
Quote
Nathan S writes:
>I am trying to pass a variable between my Delphi app and a SQL db. I
>have two variables, 'FIRST' and "SECOND' which are defined by :Shift.
>I can pass them into SQL and retrieve results without error
>(btnShift1, btnShift2). BUT, Now I want to return results from both
>of these variables, 'FIRST' and 'SECOND' and have been unable to do
>so (btnShiftboth). Below I have listed my code and also the query
>for your review. Thanks guys! --------------------------------------

[all kinds of very unnecessary stuff snipped, please quote only what is
relevant to your problem]

>procedure TActiveDatabase.btnShift1Click(Sender: TObject);
>begin
>ADOQuery1.Close;
>ADOQuery1.Parameters[1].Value := 'First' ;
>ADOQuery1.Open;
>end;
>procedure TActiveDatabase.btnShift2Click(Sender: TObject);
>begin
>ADOQuery1.Close;
>ADOQuery1.Parameters[1].Value := 'Second' ;
>ShowMessage (ADOQuery1.Parameters[1].Value);
>ADOQuery1.Open;
>end;
>procedure TActiveDatabase.btnBothClick(Sender: TObject);
>begin
>ADOQuery1.Close;
>ADOQuery1.Parameters[1].Value := 'First,Second' ;
>ShowMessage (ADOQuery1.Parameters[1].Value);
>ADOQuery1.Open;
>end;
>----------------------------------------------------
>WHERE
>L.TimeRequested>= :TimeRequested and
>L.Shift IN (:Shift) and

You cannot pass multiple values to a single parameter. The resulting query
ends up as
L.Shift IN ('First,Second')
but it needs to be

L.Shift IN ('First', 'Second')

You will have to manually code this part instead of using parameters (but
continue using parameters for the rest).

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson


 

Re:Delphi to SQL Param problems

Nathan S writes:
Quote
Thanks Wayne,
How would I go about manually coding this part? Im sorry about all
the questions, but I am obviously new to all this.
Add the SQL to the TAdoQuery component in code instead of design-time.
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select M.TimeBeforeDepletion*60 AS TBDSecs, ');
ADOQuery1.SQL.Add(' etc ');
//var InValues: string;
// set values needed, each one quoted
if NeedFirst then
InValues := QuotedStr('First');
if NeedLast then
InValues := InValues + ',' + QuotedStr('Last');
ADOQuery1.SQL.Add( 'L.Shift IN (' + InValues + ') and ');
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

Re:Delphi to SQL Param problems

Thanks again
Wayne