Re: Temporary table with SQL Server 7.0

Tech-Archive recommends: Fix windows errors by optimizing your registry

Francois Houde wrote:
Hi everybody I'm using the code down below with VB6 and SQL Server
7.0. I want to use temporary table but it doesn't seem to work.

Any idea on what the problem could be ?

Public Sub main()
On Error GoTo main_err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim intTotal As Integer
Dim lngRecords As Long

strUserID = "strUserId"
strPassword = "strPassword"
strDSN = "strDSN"
strBD = "strBD"

strconnect = "DSN=" & strDSN & ";UID=" & strUserID & ";PWD=" &
strPassword & ";DATABASE=" & strBD
Set cnn = New ADODB.Connection
cnn.Open strconnect

cnn.Execute "select * into #tmp1 from table1", lngRecords
'Here lngRecords has a value of 20

Set rst = New ADODB.Recordset
rst.Open "select * from #tmp1", cnn
intTotal = 0
'Here rst.Recordcount has a value of -1

With a default server-side forward-only cursor, Recordcount will always
contain -1.

If rst.RecordCount > 0 Then
rst.MoveFirst

You just opened the recordset - there is no need to execute a MoveFirst.
If the recordset contains records, it will already be pointing at the
first record.

And of course, this loop will never run (RecordCount is less than zero).

Do While Not rst.EOF
intTotal = intTotal + 1
rst.MoveNext
Loop
End If
'Here intTotal has a value of 0 but should be 20

Of course. The loop never ran ...

Debug.Print intTotal
cnn.Close

What is your goal here? Simply to count the records? If so:

Set rst = cnn.Execute("select count(*) from tmp1",,adCmdText)
Debug.Print rst(0)
rst.close:set rst=nothing
cnn.close: set cnn=nothing


If you need to get a count and do something with the data as well, you
have two options:
1. Switch to a more expensive client-side or server-side static cursor
which supports RecordCount, or
2. Cease your dependance on RecordCount and use a GetRows array to
process the data (http://www.aspfaq.com/show.asp?id=2193)



The ADO documentation can be found at msdn.com. Here is a link:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp

Go there and look up the Open and Execute methods. You will see the
correct syntax for opening different cursor types and locations.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: Using a cursor SQL server side Problem
    ... >cursor to to the proper starting point in a sorted view, ... >declare @RecordCount as tinyint ... >@RecordCount as Rcnt ... >Dim rd As SqlClient.SqlDataReader ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: No recordcount from a procedure
    ... If you are checking the recordcount property in ADO, ... Dim cn As New ADODB.Connection ... Dim royalty As Variant ... adParamReturnValue) ...
    (microsoft.public.sqlserver.programming)
  • RE: Reading an external text file
    ... Changing the startRow and startColumn values won't make any difference. ... Dim startRow As Integer ... Dim recordCount As Integer ... populate an excel sheet for anaylsis. ...
    (microsoft.public.excel.programming)
  • Re: Pivot Records
    ... least return first 255 if recordcount was more ... ' Northwind.mdb, for example, and you want to transpose ... Dim tdfNewDef As DAO.TableDef ... Dim rstTarget As DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: Pivot Records
    ... least return first 255 if recordcount was more ... ' Northwind.mdb, for example, and you want to transpose ... Dim tdfNewDef As DAO.TableDef ... Dim rstTarget As DAO.Recordset ...
    (microsoft.public.access.queries)