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

Delphi Beginner needs help with writing Query and managing results

I'm using something similar to the following code to enter an sql query and then place the results of a particular field somewhere on my form.

    Query1.SQL.Clear;
    Query1.SQL.Add ('Select * From tablename Where value = someData');
    Query1.RequestLive := true;
    Query1.Active := true;
    Query1.Open; {open query + display data}

    EditBox.Text := Query1.FieldByName('name').AsString;

I just started with Delphi a couple days ago and I need some help.  A lot needs to change with this and I have several questions.

1) Probably the easiest question is how do I check the number of records that is returned from my query?

2)Next, I need to check if the fieldname 'name' exists before I attempt to assign its value to my EditBox how do I do that?  Also is there anyway to pull out all of the fieldnames in a table?

3) Finally, I tried putting the above in a try statement but even with that if I entered bad data into the query (e.g. a non-existent tablename, or tried to check values with incompatible types) the program would give me an error and crash (maybe I was doing it wrong).  Is there anyway that I could have something less harsh happen if the program can't execute the query.  
0
ICPooreman
Asked:
ICPooreman
2 Solutions
 
Ivanov_GCommented:
1) Query1.RecordCount

2) Never write queries like the one above. Do it specifying the columns:
   Select id, name From tablename Where value = someData
   This way if column name does not exists, it will blow with DB Exception

   Otherwise you have to check in Query1.Fields for all fields returned and check Fields[x].Name

3)
   try
     Query1.Open;
   except
     on E: Exception do
       begin
         ShowMessage(e.Message);
         // and exit procedure maybe...
       end;
   end;
0
 
wildzeroCommented:
i used this procedure

Procedure SetSql( ADOQ : TADOQuery; sqltxt : string; count : integer; paras  : array of variant);
var
i : integer;
Begin
  ADOQ.Active := false;
  ADOQ.Close;
  ADOQ.SQL.Text := sqltxt;
  for i:=0 to count-1 do
    begin
        ADOQ.Parameters.ParamByName('S'+inttostr(i)).Value := paras[i];
    end;

  ADOQ.Open;
  ADOQ.Active := true;
  ADOQ.First;
End;
//==============================================================================

SetSql(AdoQuery1, 'SELECT * FROM tablename WHERE value=:S0', 1, [someData]);
If (AdoQuery1.RecordCount > 0) then
  begin
    //handle the result
  end;
AdoQuery1.close;

You can then do things like
SetSql(AdoQuery1, 'SELECT * FROM tablename WHERE value=:S0 AND ValueT=:S1', 2, [someData,moreDate]);
SetSql(AdoQuery1, 'SELECT * FROM tablename WHERE id=:S0 AND value=:S1 AND ValueT=:S2', 3, [userid,someData,moreDate]);
This is the best way to do it, as you don't have to escape ' in your someData or anything like that

Also as the other guy said, you should try and always do
'SELECT colname...'
like
SLECT id,name,email FROM tablename....








Query1.SQL.Add ('Select * From tablename Where value = someData');
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now