Recent

Author Topic: Show specific SQL data  (Read 7290 times)

ironman139

  • New Member
  • *
  • Posts: 36
Show specific SQL data
« on: October 25, 2016, 06:38:39 am »
Hello,

I have a question again.

I wont to filter a specific row from a sql database and want to show them in a listbox.

When i use this code, i get the whole row displayed in the Listbox.

Code: Pascal  [Select][+][-]
  1. procedure TForm3.Button1Click(Sender: TObject);
  2.   begin
  3.      SQLQuery1.Close;
  4.      SQLQuery1.SQL.text:='SELECT * FROM Auftrag ';
  5.  
  6.        while (SQLQuery1.Eof = false) do
  7.           begin
  8.          Listbox1.Items.add(SQLQuery1.Fields[1].AsString);
  9.           SQLQuery1.Next;
  10.  
  11.           end;
  12. end;

But when i apply my filter function, I just get the first record from the table.

Code: Pascal  [Select][+][-]
  1. procedure TForm3.Button1Click(Sender: TObject);
  2.   begin
  3.      SQLQuery1.Close;
  4.      SQLQuery1.SQL.text:='SELECT * FROM Auftrag where Fertig = :Fertig';
  5.  
  6.      SQLQuery1.Params.ParamByName('Fertig').AsString := Edit1.Text;
  7.      SQLQuery1.Open;
  8.  
  9.        while (SQLQuery1.Eof = false) do
  10.           begin
  11.          Listbox1.Items.add(SQLQuery1.Fields[1].AsString);
  12.           SQLQuery1.Next;
  13.  
  14.           end;
  15. end;

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Show specific SQL data
« Reply #1 on: October 25, 2016, 09:00:57 am »
What do you want to accomplish?
Code 1 and 2 gives the record you asked. Code 2 is filtered by parameter. If there's only one record with your put text, it's a logical one. I don't know your data in your table.

You have to tell more.

TIP: if you only want to fill you listbox, don't use '*' in your query, bu only the fields you want to see.

Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

ironman139

  • New Member
  • *
  • Posts: 36
Re: Show specific SQL data
« Reply #2 on: October 25, 2016, 02:41:37 pm »
Its basically a shedule sofware.

My table looks like :

Person               Date
Person1             10.24.2016
Person2             11.28.2016
Person3             11.28.2016
Person4             12.29.2016
...


So I type in now the date in my Edit1 field.
But i just get 1 record out of the table.
But I want all with the specific date.


balazsszekely

  • Guest
Re: Show specific SQL data
« Reply #3 on: October 25, 2016, 02:46:35 pm »
Hi ironman139,
After the line SQLQuery1.Open insert SQLQuery1.First;
Date is string on your db?

ironman139

  • New Member
  • *
  • Posts: 36
Re: Show specific SQL data
« Reply #4 on: October 25, 2016, 03:39:46 pm »
Yes , the date is saved as a string in the sql

Edit:
Sql.query1.first give me just the first record again.
But I want to type in the date in the edit1. Or pick it by the datetimepicker and search all records with the same data
« Last Edit: October 25, 2016, 04:19:14 pm by ironman139 »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Show specific SQL data
« Reply #5 on: October 25, 2016, 05:01:46 pm »
If Fertig and Edit1.Text are both string, are they compared with the same format?
For example: Fertig = '25/10/2016' and Edit1.Text = '25/10/2016'.

ironman139

  • New Member
  • *
  • Posts: 36
Re: Show specific SQL data
« Reply #6 on: October 25, 2016, 05:16:01 pm »
Yes
It's exactly the same format
While saving i put the date by the datetimepicker in the sql and for displaying also by the datetimpicker to the edit

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Show specific SQL data
« Reply #7 on: October 25, 2016, 06:58:07 pm »
Then this must return all record where Fertig=text in your edit field

Code: Pascal  [Select][+][-]
  1. begin
  2.      SQLQuery1.Close;
  3.      SQLQuery1.SQL.text:='SELECT * FROM Auftrag where Fertig = :Fertig';
  4.      SQLQuery1.ParamByName('Fertig').AsString := Edit1.Text;
  5.      SQLQuery1.Open;
  6.  
  7.        Listbox1.Items.Clear;
  8.        while not SQLQuery1.Eof do
  9.           begin
  10.           Listbox1.Items.add(SQLQuery1.Fields[1].AsString);
  11.           SQLQuery1.Next;
  12.           end;
  13. end;
  14.  

ironman139

  • New Member
  • *
  • Posts: 36
Re: Show specific SQL data
« Reply #8 on: October 25, 2016, 08:31:47 pm »
Hm

I'll post the whole code tonight when I'm at home.
I have no idea at the moment .

ironman139

  • New Member
  • *
  • Posts: 36
Re: Show specific SQL data
« Reply #9 on: October 26, 2016, 06:25:29 am »
 >:( >:( >:(

I´m just stupid, sorry :D
I have 3 different stuffs in my table

Person Fertig Erstellt   ( German Words  :-X)

So fertig means finished and erstellt means created, and I changed both , and one has no dates inside... sorry for that  >:( >:(
It´s working perfect now.




« Last Edit: October 26, 2016, 07:18:27 am by ironman139 »

Omirbay

  • New Member
  • *
  • Posts: 12
Re: Show specific SQL data
« Reply #10 on: October 26, 2016, 02:40:03 pm »
Hello there!

I m encounterin some difficulty to reply by mobile.
I follow what you want, n succeeded in doin so when I was having the same problem.


 
Code: Pascal  [Select][+][-]
  1. Procedure search(str : string);
  2. Var
  3.  Ssql : string;
  4. Begin
  5.  With form1 or datamodule do begin
  6.   //with here you need to specify the form or datamodule query component is placed
  7.   Query.close;
  8.   Query.sql.clear;
  9.   Queru.sql.add('SELECT * FROM AUTFRAG');
  10.   Query.sql.add('WHERE fertig like ' + quotedstr(str));
  11.   Query.open;
  12.  End;
  13.  
  14.  
  15. End;
  16.  
  17. Procedure edit1.onchange;
  18. Begin
  19.  Search(edit1.text);
  20. End;
  21.  
  22.  

Omirbay

  • New Member
  • *
  • Posts: 12
Re: Show specific SQL data
« Reply #11 on: October 26, 2016, 02:47:49 pm »
Oops I forgot to include listbox
Code: Pascal  [Select][+][-]
  1. Procedure search(str:string);
  2. Var
  3.  S1 : string;
  4.  I,n : integer;
  5. Begin
  6.  With form1 or datamodule do begin
  7.   //with here you need to specify the form or datamodule query component is placed
  8.   Query.close;
  9.   Query.sql.clear;
  10.   Queru.sql.add('SELECT * FROM AUTFRAG');
  11.   Query.sql.add('WHERE fertig like ' + quotedstr(str));
  12.   Query.open;
  13.  End;
  14.  
  15.  N := query.recordcount-1;
  16.  Listbox1.Items.clear;
  17.  Query.first;
  18.  For I := 0 to n do begin
  19.    S1 := query.fieldbyname('fertig').asstring;
  20.  
  21.    Listbox1.items.add(s1);
  22.    Query.next;
  23.  End;
  24. End;
  25.  
  26.  

ironman139

  • New Member
  • *
  • Posts: 36
Re: Show specific SQL data
« Reply #12 on: October 27, 2016, 03:56:54 am »
Another question, how can i show the last record of a colum ?

Code: Pascal  [Select][+][-]
  1. SELECT LAST(column_name) FROM table_name;


This one is not working unfortunately ...

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Show specific SQL data
« Reply #13 on: October 27, 2016, 08:37:58 am »
http://www.w3schools.com/sql/sql_func_last.asp

or use property last of dataset.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

ironman139

  • New Member
  • *
  • Posts: 36
Re: Show specific SQL data
« Reply #14 on: October 27, 2016, 02:38:04 pm »
I figured out when I just put
Code: Pascal  [Select][+][-]
  1. SQLQuery1.Last;
After the .Open syntax, I get the last one.
Easy  ;)

 

TinyPortal © 2005-2018