?
Solved

Delphi SQL query from a string list?

Posted on 2014-09-19
5
Medium Priority
?
1,482 Views
Last Modified: 2014-10-30
I have a query that needs to pull a list of parts IF those part numbers are found in another list/table. The problem is that the query pulling the parts list uses a direct ODBC connection for access and the other query is linked to a SQL table that shows the parts and whether or not they should be shown.

Here's my example:  FIRST I pull the blue parts into a string list...
  //PULL BLUE BOOK PIECES INTO LIST
  with qryBlue do begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT PieceType FROM PieceType WHERE Book = ''BLUE'' ');
    Open;
    while not EOF do begin
      slBlue.Add(qryBlue.FieldByName('PieceType').AsString);
      Next;
      end;

Open in new window


Now here's where I'm having trouble... how can I query this list?  Here's the 2nd part of my search (search doesn't work of course, here's where I need help):
  if sFrameless <> '' then begin
    with qryParser do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN ('''+slBlue+''' ');
      Open;
      end;
    end
  else begin
    with qryParser do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN (('''+slBlue+''') AND (PieceType <> ''45'') AND (PieceType <> ''46'')) ');
      Open;
      end;
    end;

Open in new window


Thank you.
0
Comment
Question by:Bianca
5 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 40333704
Try using slBlue.CommaText
0
 
LVL 28

Expert Comment

by:Sinisa Vuk
ID: 40333782
why not extend second and third sql to use first query - and you don't need first one at all:

...
Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN (SELECT PieceType FROM PieceType WHERE Book = ''BLUE'') ');
      Open;
      end;
...
Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN (SELECT PieceType FROM PieceType WHERE Book = ''BLUE'' AND (PieceType <> ''45'') AND (PieceType <> ''46'')) ');
      Open;
      end;
...

Open in new window


...and this ''BLUE'' can be parameter too.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 40336341
use bind variables ! no ? why not ?
don't care about performance ?

SQL.Add('SELECT PieceType FROM PieceType WHERE Book = :BOOKCOLOR');
SQL.ParamByName('BOOKCOLOR').AsString := 'BLUE';

is this for making a user selection ? > user_selection_table
insert the selected items into a table and then match the selection from that table

in pseudo:
delete from user_sel where user_session_id = :user_session_id
:user_session_id = number of connection id in database

sql.text := 'INSERT INTO user_sel (user_session_id, value) values(:user_session_id, :value)';
paramByName('USER_SESSION_ID').AsInteger := ___NumberOfSession;
for i := 0 to items.count - 1 do 
begin
  paramByName('VALUE').AsString := items[I];
  ExecSQL;
end;

sql.text := 
  'SELECT pt.* from PIECE p, user_sel u '+
  'where u.user_session_id = :user_session_id '+
  '  and p.piecetype = u.value';
ParamByName('USER_SESSION_ID').AsInteger := __NumberOfSession

Open in new window

0
 

Author Comment

by:Bianca
ID: 40336716
Sinisa - Thank you for your response. I tried that at first but here's my problem...

Piece table is using qryParser (connected to MDB)
PieceType table is using qryPiece (connected to SQL)

I need to pull records from table.Piece IF the field.PieceType is in table.PieceTable.  What is the best way to do this?
0
 
LVL 28

Accepted Solution

by:
Sinisa Vuk earned 2000 total points
ID: 40337587
Is it possible to import this table Piece (from mdb) into Sql server database? It is possible, but I want to know if you willing to..? If you do - then is my first solution a good way.
This is how to ... http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/importing/Import_Access_DB.asp

Other option is to link table in access side:
http://office.microsoft.com/en-001/access-help/link-to-sql-server-data-HA102809758.aspx

.. or in sql server side (which might be the best):
http://www.mssqltips.com/sqlservertip/1480/configure-microsoft-access-linked-tables-with-a-sql-server-database/

... and this way you can have only one sql statement too.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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