Results 1 to 2 of 2
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: problem while creating/acessing temp tables through ADO

    Hi,

    I have a project in RDO which works fine but i am now trying to upgrade it to use ADO. However, i am facing problem while accessing/creating temp tables in the project (same code which works fine with RDO). I have identified the code where the problem occurs and have created a sample code which replicates the problem.

    I am using Sybase databse with Sybase ASE driver 5.0 and MDAC 2.8 on win XP

    The code is as follows:

    Code:
    Private Sub Command1_Click()
        
        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim rs1 As New ADODB.Recordset
        Dim sSql As String
        Dim count As Integer
        
        Set con = New ADODB.Connection
        Set rs = New ADODB.Recordset
        
        con.Open "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Password=pass;DSN=MyServer"
           
        For count = 1 To 4
        
            sSql = "CREATE TABLE #tmp" & count
            sSql = sSql & " ("
            sSql = sSql & "     Id     int"
            sSql = sSql & " )"
            
            con.Execute sSql
            
            sSql = "Insert into #tmp" & count
            sSql = sSql & " Values (" & count & ")"
            
            con.Execute sSql 'THIS IS WHERE THE PROBLEM OCCURS IN SECOND LOOP
            
            rs.Open "select Id from #tmp" & count, con, adOpenStatic
            
            MsgBox "Table: #tmp" & count & Chr(13) & "Id: " & rs.Fields(0)
            
            rs.Close
             
         Next count
         
         'Do some updates
         
            sSql = "update #tmp1 set Id = 100"
            con.Execute sSql
            
            rs.Open "select Id from #tmp1", con
            MsgBox "Update Table #tmp1" & Chr(13) & "Now Id: " & rs.Fields(0)
            rs.Close
            
          'Try to update the Id does not exixts
         
            sSql = "update #tmp2 set Id = 200 where Id = 1"
            con.Execute sSql
            
            rs.Open "select Id from #tmp2", con
            MsgBox "Update Table #tmp2" & Chr(13) & "Now Id: " & rs.Fields(0) & Chr(13) & "Should be same as before"
            rs.Close
    
    End Sub
    When i run this code. The For loop creates first table just fine. I am able to insert a value and then select it from the temp table. However, the second time around the select statement fails even though the second create temp table statement does not throw any error. The error i get is "#tmp2 could not be found"

    help!!!!

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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