How to Query Database by applying where condition from another recordset.

[Sreedhar Vankayala]

Hi,

I have a table IV00108wc which contains several thousand records in whcih
ITEMNMBR is the primary key.

I have a com+ object returning recordset which has ITEMNMBR and another
details related. [I do not have direct query to get the list]

I want to query another table based on Com+ object recordset.
Select distinct WebClas1 from IV00108wc where ItemNmbr in (...).
where, I want the ItemNmbr list from the COM+ recordset.

Please suggest me how to do the query and apply the where condition from the
resulted recordset ?

Regards,

Sreedhar Vankayala

[Bernie Yaeger]

Hi Sreedhar,

Can't you call scroll through the com+ recordset and build a global,
concatenated string containing all itemnmbr's brackets with chr(39), such as
this:

(if .net)
for each irow in rs.tables(0).rows
bulkstring = bulkstring & chr(39) & irow("itemnmbr") & chr(39)
next

Then concatenate the query.

If you are using sql server, you could also create a table using 'insert
into...from' etc inside the com+ object; then use that table to build
bulkstring as indicated above.

HTH,

Bernie Yaeger

"Sreedhar Vankayala" <v_********@hotmail.com> wrote in message
news:uo*************@TK2MSFTNGP10.phx.gbl...

Hi,

I have a table IV00108wc which contains several thousand records in whcih
ITEMNMBR is the primary key.

I have a com+ object returning recordset which has ITEMNMBR and another
details related. [I do not have direct query to get the list]

I want to query another table based on Com+ object recordset.
Select distinct WebClas1 from IV00108wc where ItemNmbr in (...).
where, I want the ItemNmbr list from the COM+ recordset.

Please suggest me how to do the query and apply the where condition from the resulted recordset ?

Regards,

Sreedhar Vankayala

[MeDhanush]

1. Create a comma separated string from COM+ recordset
2. Pass the generated string to Select method on the table IV00108wc,
as Select("ItemNumber IN( 100,300,450) " ) ;
sample code for the part 2 :
-----------------------------------
using System ;
using System.Data ;

class AB
{
public static void Main()
{
DataTable dt ;
DataRow dr ;
DataRow[] arDataRow ;
dt = new DataTable() ;
dt.Columns.Add ( "ItemNumber", typeof(int)) ;
dt.Columns.Add ( "ItemDescription", typeof( string )) ;
dr = dt.NewRow() ; dr[0] = 100 ; dr[1] = "AAAAA" ; dt.Rows.Add ( dr )
;
dr = dt.NewRow() ; dr[0] = 200 ; dr[1] = "BBBBB" ; dt.Rows.Add ( dr )
;
dr = dt.NewRow() ; dr[0] = 300 ; dr[1] = "CCCCC" ; dt.Rows.Add ( dr )
;
dr = dt.NewRow() ; dr[0] = 400 ; dr[1] = "DDDDD" ; dt.Rows.Add ( dr )
;
dr = dt.NewRow() ; dr[0] = 450 ; dr[1] = "DDDDD" ; dt.Rows.Add ( dr )
;
dr = dt.NewRow() ; dr[0] = 500 ; dr[1] = "DDDDD" ; dt.Rows.Add ( dr )
;

arDataRow = dt.Select("ItemNumber IN( 100,300,450) " ) ;
Console.WriteLine ( arDataRow.Length ) ;
}
}

-----------------------------------
"Sreedhar Vankayala" <v_********@hotmail.com> wrote in message news:<uo*************@TK2MSFTNGP10.phx.gbl>...

Hi,

I have a table IV00108wc which contains several thousand records in whcih
ITEMNMBR is the primary key.

I have a com+ object returning recordset which has ITEMNMBR and another
details related. [I do not have direct query to get the list]

I want to query another table based on Com+ object recordset.
Select distinct WebClas1 from IV00108wc where ItemNmbr in (...).
where, I want the ItemNmbr list from the COM+ recordset.

Please suggest me how to do the query and apply the where condition from the
resulted recordset ?

Regards,

Sreedhar Vankayala