Multiple SQL to Same RecordSet

[vbmorton]

Have a question to see if this is possible or if there is another way to accomplish what i want to happen.using vb6/sql server2000

This is what i have so far.

first it goes and get some users roles(dependent on what dept you selected from cbo box)in sql, gets the team members from that dept and puts it into a recordset(this works fine)
then,
I want it to go into a loop for those team members in that list (do until eof) and get from file newrequest all req for people in that dept, puttin each record into rs.Requests.
as soon as it gets thru the first person, adding it to rs.requests, it bombs out, when reads the next user saying Operation not allowedwhen the object is open.

any ideas? thoughts? better ways to handle?

  'QUERY SELECT Dept MEMBERS FROM I.T. ROLE IN SQL
      
      strsql = "EXEC sp_getuserrole '" & Trim$(cboDept.Text) & "' "
      
      rsdept.Open strsql, g_conn, adOpenStatic
      
    'LOAD Dept MEMBERS FROM QUERY &PUT THEM IN CBODept
  Do Until rsdept.EOF
       strUser = rsdept(0)
     
      strsql = "select  * from newrequest where userid = '" & Trim$(strUser) & "' order by requestidno asc"
     
     
      rsRequests.Open strsql, g_conn, adOpenStatic
     
     
     rsdept.MoveNext
 
 Loop

 

[HarleyQuinn]

Hi vbmorton,

You aren't technically adding to rsRequests, you are opening it based on the query specified in strsql. Then in the loop you are trying to re-open the already open recordset with another SQL statement and that will be why it is throwing up the error.

Could you try using the IN function to create the second recordset?



Harleyquinn

---------------------------------
For tsunami relief donations

 

[vbmorton]

Harleyquinn,

can you give me an example.. Im very new to VB and Learning everyday how powerfull it can be, but still Clueless in many functions.

Thanks,

vbmorton

 

[HarleyQuinn]

With the IN function (this is in the query) you can base a query on a query (similar to a join as far as I know) e.g.

You have a table with multiple instance of an id (newsrequest in your case) and a table of users. You could use something like:

select * from newrequest where userid in (select userid from tbl_users where role = '" & Trim$(cboDept.Text) & "') order by userid, requestidno asc

Basically that would select data from newsrequest for every id in a query pulling specific id's from a table based on combobox input.

Don't think I've explained that one so well but if you have any queries please post them back.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vbmorton]

So are you saying in my first rs.dept instead of putting the users names into that recordset create a table?

Cause the first query depending on the dept they select in cbo box, goes and gets the users from ROLES in SQL and puts just the user id's into rs.Dept

can you give ie. thanks alot in advance!

vbmorton

 

[HarleyQuinn]

Not into a table but having a query whose criteria is based on values returned in another query.

You could try putting the query to retrieve the roles into the () after the 'IN' in the SQL statment i specified before. Not sure if that would work but it might be worth a try because if it does it will be alot faster than creating a table everytime.

Harleyquinn

---------------------------------
For tsunami relief donations