Solved

Delphi Beginner needs help with writing Query and managing results

Posted on 2006-07-03
2
248 Views
Last Modified: 2013-12-03
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
Comment
Question by:ICPooreman
2 Comments
 
LVL 12

Accepted Solution

by:
Ivanov_G earned 300 total points
ID: 17033468
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
 
LVL 10

Assisted Solution

by:wildzero
wildzero earned 200 total points
ID: 17033771
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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