• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • 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.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.  
2 Solutions
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

     on E: Exception do
         // and exit procedure maybe...
i used this procedure

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

  ADOQ.Active := true;

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

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...'
SLECT id,name,email FROM tablename....

Query1.SQL.Add ('Select * From tablename Where value = someData');
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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