Solved

Delphi Beginner needs help with writing Query and managing results

Posted on 2006-07-03
2
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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