SQL Error

[MLNorton]

I am trying to learn SQL. I have a simple test program that reads an Access database and empties all of the fields except 1. I have an ADOConnection, an ADOQuery, a DataSource component and a DB Grid. The Access database has a Table 1 and a Table 2. This problem is with Table2. I do not use a Query on Table1. The DBGrid Query statement is:
SELECT Week, DateName, WeekName, Visitors, Collect, UnResolved
FROM Columbia-Test.mdb

The DBGrid displays the fields correctly.
My code is as follows:

procedure TForm3_SQL.Button_Run_1Click(Sender: TObject);
begin
With ADOQuery1 do
begin
Active := false;
SQL.Clear;
SQL.Add ('Table2');
SQL.Add ('UPDATE DateName = '', WeekName = '', Collect = 0, Visitor = 0, UnResolved = ''');
SQL.Add ('FROM Table2');
ExecSQL;
end;
end;

I get the following error:

Cannot run the project unless a host application is defined. Use the Run|Prameters… dialog box.

Where am I going wrong?

 

[majlumbo]

This isn't an SQL problem.

It sounds like you started development on a DLL not an application. You cannot run a DLL directly, you need another program to call the procedures/functions they contain.

 

[whosrdaddy]

MlNorton,

sounds something is wrong with your application.
try to start over from a New.. VCL forms application.

+ your query is incorrect, use the one that was given here:

http://www.tek-tips.com/viewthread.cfm?qid=1672533

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[MLNorton]

With your help I have succeeded in creating a SQL UPDATE as follows;

With ADOQuery1 do
begin
Active := false;
SQL.Clear;
SQL.Add ('UPDATE Table2 SET DateName = null, WeekName = null, Collect = 0, Visitor = 0, UnResolved = null');
ExecSQL;
end;

The first column, Week, Is not changed and retains the values 1 to 52.

I now need to UPDATE the DateName and WeekName to values stored in arrays, DateNameArray and WeekNameArray.

I have researched the Internet and I have not found a way to UPDATE using the array values.

How can I do this?

 

[majlumbo]

The easiest way is to create another ADO Query with parameters.

the sql command will be like this

ADOQueryX.SQL.Text := update table2 set DateName = :paramDateName, WeekName = :paramWeekName where week = :paramWeekNum

Then in delphi you need to set each of the parameters before you issue the ExecSQL.

For I := 1 to 52 do
begin
   ADOQueryX.ParamByName('ParamDateName').AsString := '<ValueFromArray[I]>';
   ADOQueryX.ParamByName('ParamWeekName').AsString := '<ValueFromArray[I]>';
   ADOQueryX.ParamByName('paramWeekNum').Value := I;
   ADOQueryX.ExecSQL;
end;

This assumes that the values in the array are already ordered from week 1 to week 52. If your array is indexed from 0, then adjust the For loop to 0 to 51, and then you'd need to add 1 to I within the loop

 

[MLNorton]

I have implemented the suggested code in a test Procedure as follows:

procedure TForm_Main.Button_AddClick(Sender: TObject);
var
X: array[1..3] of string;
Y: array[1..3] of string;
I: integer;
begin
X[1] := 'Jan 2';
X[2] := 'Jan 9';
X[3] := 'Jan 16';
Y[1] := '1/2';
Y[2] := '1/9';
Y[3] := '1/16';

ADOQuery1.SQL.Text := update table2 set DateName = aramDateName, WeekName = aramWeekName where week = aramWeekNum
For I := 1 to 3 do
begin
ADOQuery1.ParamByName('ParamDateName').AsString := '<X>';
ADOQuery1.ParamByName('ParamWeekName').AsString := '<Y>';
ADOQuery1.ParamByName('paramWeekNum').Value := I;
ADOQuery1.ExecSQL;
end;
end;

This procedure does not work. I get the following error on the first line of code:

Incompatable types: 'WideString' and 'procedure, untyped pointer or untyped parameter

What am I doing wrong?

 

[majlumbo]

Sorry, my instructors in college always wrapped everything in <> when we need to replace the values, just assumed everyone would understand that.

Just get rid of the <> and quotes (even for string values held in variables) and all should work..

X;

Also, if you actually need quotes in the SQL, use the function QuotedStr() instead.

 

[MLNorton]

Problem is with the first lin of code.
I changed

ADOQuery1.ParamByName('ParamDateName').AsString := <'X'>;
ADOQuery1.ParamByName('ParamWeekName').AsString := <'Y'>;

to

ADOQuery1.ParamByName('ParamDateName').AsString := X;
ADOQuery1.ParamByName('ParamWeekName').AsString := Y;

Problrm still exist.

I have forgotten a lot from my college days since I graduated in 1950.

 

[majlumbo]

First, are you getting a runtime, or compile time error?

I'm wondering what the underlying data type is for the field within the database. If it's a date field, then the values you are passing are not valid values.

Valid dates (at least in MySQL) are passed formatted like: '2012/01/02'

In Oracle: to_date('2012/01/02', 'yyyy/mm/dd')

 

[MLNorton]

I am getting a compile error. The values are string values not dates.

 

[majlumbo]

Here's an example from Delphi About web page

http://delphi.about.com/od/database/l/aa050101a.htm

with ADOQuery1 do begin
  Close;
  SQL.Clear;
  SQL.Add('SELECT * FROM Applications WHERE type =:apptype');
  ParamByName('apptype').Value:='multimedia';
  Open;
end;

You can use .Value or .AsString, or if it's a date field .AsDateTime.

I usually only use .Value when I'm supplying an integer value, and .AsDate or .AsDateTime for dates, and .AsString for most everything else.

 

[MLNorton]

I have finally the code compiling but failinh on Run.
My code is as follows:
procedure TForm_Main.Button_AddClick(Sender: TObject);
var
X: array[1..3] of string;
Y: array[1..3] of string;
I: integer;
begin
X[1] := 'Jan 2';
X[2] := 'Jan 9';
X[3] := 'Jan 16';
Y[1] := '1/2';
Y[2] := '1/9';
Y[3] := '1/16';

ADOQuery1.Close;
ADOQuery1.SQL.Text := 'UPDATE Table2 SET "Key" = aramKey,"DateName" = aramDateName, "WeekName" = aramWeekName values "Key", :"DateName", :"WeekName"';

For I := 1 to 3 do
begin
ADOQuery1.Parameters.ParamByName('ParamDateName').Value := X;
ADOQuery1.Parameters.ParamByName('ParamWeekName').Value := Y;
ADOQuery1.Parameters.ParamByName('paramKey').Value := I;
ADOQuery1.ExecSQL;
end;
end;

I get the following error:

Parameter object is improperly defined. Inconsisent or incomplete information was provided.

 

[majlumbo]

I don't think you need all the quotes in your sql statement, and values (XXX) is actually only for database inserts, not updates.

ADOQuery1.SQL.Text := 'UPDATE Table2 SET Key = aramKey,DateName = aramDateName, WeekName = aramWeekName';

A database insert would be:

ADOQuery1.SQL.Text := 'INSERT INTO Table2 (Col1, .., Coln) values col1param, ..., :colnparam)';

Delphi takes everything with a colon preceding it as a parameter you have to set before you execute your query. To set a parameter, you can either do it via the Query's parambyname or by iterating through the params array. Params array is zero based, first parameter to appear in the SQL is 0, 2nd is 1 etc.

ADOQuery1.parambyname('<name>').Value := XXX;

or

ADOQuery1.params[0].Value := XXX;

 

[DjangMan]

As an aside I would suggest looking at using the u_db_utilities unit that includes the TIADOQuery that Daddy created in this post:

http://www.tek-tips.com/viewthread.cfm?qid=1647984

It makes working with the ADOQuery object very, very easy.