Solved

Delphi Beginner needs help with writing Query and managing results

Posted on 2006-07-03
2
243 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now