Solved

Delphi SQL query from a string list?

Posted on 2014-09-19
5
703 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 25

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 36

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 25

Accepted Solution

by:
Sinisa Vuk earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

12 Experts available now in Live!

Get 1:1 Help Now