CODEWITHSUNDEEP
c#asp.netsql-serverasp-classicwhere-in
Ali Sheikhpour
Ali Sheikhpour

Reputation: 11096

SQL - where column in another recordset from another database

I am familiar with "IN" clause in sql e.g.

select * from table1 where myfield in (select myfield from table2)

I am now between two databases! I want to select recordset where phone is in other recordset from another database. I am not working directly with sql server. You may suggest me a more complex method using a server side language e.g. php or asp etc.

My test in classic asp (where connectionObject1 connects to first database and connectionObject2 connects to second database) :

sql="select phone from persons"
recordset1.open sql,connectionObject1

sql="select * from persons where phone in ("& recordset1 &")"
recordset2.open sql,connectionObject2

Microsoft VBScript runtime error '800a000d'

Type mismatch

Upvotes: 0

Views: 134

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37460

Use fully-qualified object names in SQL, this should work:

sql="select * from [DB1Name].[SchemaName].persons where phone in (select phone from [DB2Name].[SchemaName].persons)"

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

Your error is here:

sql="select * from persons where phone in ("& recordset1 &")"

You are trying to concatenate strings, but recordset1 is a recordset as the name suggests, not a string.

I just looked this up. You should be able to convert your recordset to string with GetString thus:

sql = "select * from persons where phone in (" & 
        recordset1.GetString(adClipString, -1, ",", ",") &
      ")"

If phones are not numeric, you'd need additional quotes:

sql = "select * from persons where phone in (" & 
        "'" &
        recordset1.GetString(adClipString, -1, "','", "','") &
        "'" &
      ")"

I may be mistaken with the syntax. In that case look up your docs.

Upvotes: 2

Related Questions