Thread: SQL within SQL

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    7
    Rep Power
    0

    SQL within SQL


    I have a select that pulls my primary data. No problem.

    As I loop through each record set, I extract the data for each "person". Based on that data, I need to dynamically build a 2nd SQL for the "person" in question, but it keeps giving me an error.

    Slightly abbreviated code is:
    Code:
    Set con = CreateObject("ADODB.Connection")
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("XXXXX.accdb") & ";Persist Security Info=False;"
    Set rs = CreateObject("ADODB.Recordset")
    SQL = "select blah blah blah"
    rs.Open SQL, con
    Do While not rs.EOF
    	' Extract data
    	SQL2 = "Select blah blah blah"
    	rs2.Open SQL2, con ' <-- this line blows up
    	' never gets past here
    
    	rs.MoveNext
    Loop
    The error is:
    Microsoft VBScript runtime error '800a01a8'
    Object required: ''

    I do not want to try to put this all in the initial select due to the variability of the situation.

    Any thoughts?
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    7
    Rep Power
    0
    rs2 is define outside the initial loop. Here is the full code (minus all the extraction and dynamic stuff). Note that most of the inner loop is commented out at this point as I try to simply get the one line to work.

    Thanks

    Code:
    ' Open a connection to the database
    Dim con
    Dim rs
    Dim SQL
    Set con = CreateObject("ADODB.Connection")
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("XXXX.accdb") & ";Persist Security Info=False;"
    Set rs = CreateObject("ADODB.Recordset")
    
    Dim rs2
    Dim SQL2
    
    ' Select all the PCs and NPCs
    SQL = "SELECT *, Party.PartyName"
    SQL = SQL & " FROM People INNER JOIN Party ON People.PartyID = Party.PartyID"
    SQL = SQL & " ORDER BY Party.SortBy, People.Rank"
    
    rs.Open SQL, con
    Do While not rs.EOF
    	' extract data as needed from record set 1
    
    	SQL2 = "SELECT Skills.SkillName, PersonalSkills.SkillID, PersonalSkills.SkillLvl"
    	SQL2 = SQL2 & " FROM Skills INNER JOIN PersonalSkills ON Skills.SkillID = PersonalSkills.SkillID"
    	SQL2 = SQL2 & " WHERE PersonalSkills.PeopleID = " & rs("PeopleID")
    	rs2.Open SQL2, con	<=== BLOWS UP ON THIS LINE!!!
    '	Do While not rs2.EOF
    '		' Get a character's skills
    '		rs2.MoveNext
    '	Loop
    '	rs2.Close
    '	Set rs2 = Nothing
    	' Consider:
    	'		Skill_IDs_Pos
    	'		Skill_Lvls_Pos
    	rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    con.Close
    Set con = Nothing
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    And the part where you set it to a new ADODB.Recordset?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    7
    Rep Power
    0
    Updated code. Still failing on:
    rs2.Open SQL2, con

    Code:
    	' Open a connection to the database
    	Dim con
    	Set con = CreateObject("ADODB.Connection")
    	con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("GURPS.accdb") & ";Persist Security Info=False;"
    
    	' Define record sets
    	Dim rs
    	Set rs = CreateObject("ADODB.Recordset")
    	Dim rs2
    	Set rs2 = CreateObject("ADODB.Recordset")
    
    	Dim SQL
    	Dim SQL2
    
    	' Select all the PCs and NPCs
    	SQL = "SELECT *, Party.PartyName"
    	SQL = SQL & " FROM People INNER JOIN Party ON People.PartyID = Party.PartyID"
    	SQL = SQL & " ORDER BY Party.SortBy, People.Rank"
    
    	rs.Open SQL, con
    	Do While not rs.EOF
    		' Extract data from record set
    
    		SQL2 = "SELECT Skills.SkillName, PersonalSkills.SkillID, PersonalSkills.SkillLvl"
    		SQL2 = SQL2 & " FROM Skills INNER JOIN PersonalSkills ON Skills.SkillID = PersonalSkills.SkillID"
    		SQL2 = SQL2 & " WHERE PersonalSkills.PeopleID = " & rs("PeopleID")
    		rs2.Open SQL2, con
    '		Do While not rs2.EOF
    '			' Get a character's skills
    '			rs2.MoveNext
    '		Loop
    '		rs2.Close
    '		Set rs2 = Nothing
    		' Consider:
    		'		Skill_IDs_Pos
    		'		Skill_Lvls_Pos
    		'		Weapon_IDs_Pos
    		'		Gear_Pos
    		rs.MoveNext
    	Loop
    	rs.Close
    	Set rs = Nothing
    	con.Close
    	Set con = Nothing
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    Failing with the exact same error? That's surprising.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    7
    Rep Power
    0
    Correction! Different error:

    ADODB.Recordset error '800a0e79'
    Operation is not allowed when the object is open.

    Code is as described above.
  14. #8
  15. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    So it's already open and it's trying to do something else. I see you have rs2.Close commented out...
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    7
    Rep Power
    0
    I think that got it!

    Obviously, this is not my strong suite.

    Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo