Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: problem with using String Variable in IN Function

    Hellow !!
    My problem is that I want to use a string variable in the 'where' clause Like this.I am using VB.6

    dim ID as string
    ID = "('1','2')"

    Select coursecode from student
    where studentid in ID

    Now tell me how to solve this problem. or is there any other way to solve this problem.
    Plz help me out.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: problem with using String Variable in IN Function

    One solution would be:

    dim ID as string
    ID = ".1.2."

    Select coursecode from student
    where ID LIKE '%.' || studentid || '.%';

  3. #3
    Join Date
    Sep 2003
    Posts
    3

    Re: problem with using String Variable in IN Function

    Hellow !!

    First i will say thanks. Tell me some other solution, this one is not working. Let me tell you i am using SQLSever 2000.
    Let me explaing my problem with some more details.

    recordSet.open" select coursecode from course where student id='1'" , connectionobject

    Now save the recordset data in an array or in a string

    let suppose
    array(0)= "Eng"
    array(1)="Phy"
    (OR)

    dim str as string
    str="('eng','phy')"

    now how i will use this array or this string in another select statement like

    recordset1.open "select studentid from student where coursecode = ???????",connectionObject,adopenstatic

    Plz dont tell to use sunquery
    I will be waiting for the reply Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: problem with using String Variable in IN Function

    You can use IN:

    select studentid from student where coursecode in ('eng','phy')

  5. #5
    Join Date
    Sep 2003
    Posts
    3

    Re: problem with using String Variable in IN Function

    Hellow :
    Thanks for the reply. Actually the result of the first query is not fixed.like

    recordSet.open" select coursecode from course where student id='1'" , connectionobject

    'Consider the result of this query will be different ever time when ever it runs.
    ' Like it may give 4 records,10 records may be 15 in the recordset
    'now i have two ways to store the result of the query , i can store it either in a "string variable" or in an "Array".
    ' if string variable is used like

    dim str as string
    str = "('phy','chem','math')" or str = ('eng','oop','db','oose')

    (forget this how i saved the data from the query in this string like this )
    the only problem with using the string in the query is the outer quots " " . like

    recordset1.open "select studentid from student where coursecode = "' & str & '" ",connectionObject,adopenstatic

    Now if i use an array for storing the result of the first query. Tell me the way how that array will be use in the second query like

    recordset1.open "select studentid from student where coursecode = array ",connectionObject,adopenstatic

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •