Professional Web Applications Themes

SQL-server-VB60: error on .recordcount?? - Microsoft SQL / MS SQL Server

When I open a SQL-Server 2000 table in VB60 the recordset-property returns -1 when there records in the table. I don't want to use cursorlocation=adUseClient. because this gives troubles with my floating point numbers. If I use the cursorlocation=adUseClient it works fine, but if I use Access2000 it works fine without it, so why not with SQL-server? What can be the problem? Greetz Dennis...

  1. #1

    Default SQL-server-VB60: error on .recordcount??

    When I open a SQL-Server 2000 table in VB60 the recordset-property
    returns -1 when there records in the table.
    I don't want to use cursorlocation=adUseClient. because this gives troubles
    with my floating point numbers.
    If I use the cursorlocation=adUseClient it works fine, but if I use
    Access2000 it works fine without it, so why not with SQL-server?

    What can be the problem?

    Greetz

    Dennis


    Dennis Snoeys Guest

  2. #2

    Default Re: SQL-server-VB60: error on .recordcount??

    Dennis Snoeys wrote:
    > When I open a SQL-Server 2000 table in VB60 the recordset-property
    > returns -1 when there records in the table.
    > I don't want to use cursorlocation=adUseClient. because this gives
    > troubles with my floating point numbers.
    > If I use the cursorlocation=adUseClient it works fine, but if I use
    > Access2000 it works fine without it, so why not with SQL-server?
    >
    A server-side, forward-only cursor should display -1 for RecordCount,
    regardless of the provider
    ([url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdapro05_9.asp[/url]). I just
    confirmed this in Access2000 using this code:

    Dim rs As ADODB.Recordset, cn As ADODB.Connection,
    Dim strsql As String
    Set cn = New ADODB.Connection
    cn.Open '"provider=microsoft.jet.oledb.4.0;data source=db14.mdb"
    strsql = "SELECT * FROM Table1"
    Set rs = cn.Execute(strsql, , adCmdText)
    MsgBox rs.RecordCount
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    If you have code where RecordCount returns anything other than -1 for a
    forward-only cursor, I would love to see it.

    I agree with your desire to avoid a client-side cursor. There are other ways
    to get a count of the records in the recordset. My favorite is to use
    GetRows to stuff the recordset's data into an array. Now, getting the
    recordcount is as simple as:

    Ubound(GetRowsArray, 2) + 1

    This has the added benefit that you can immediately close the recordset and
    connection, since you now have the array to work with.

    You could also run a separate query (select count(*) from ... where ... ) o
    get the record count, but that involves a second tip to the server, unless
    you batch them and use NextRecordset to access the multiple resultsets
    returned from the batch.

    If neither of the above appeals to you, write a stored procedure with an
    output parameter containing the record count.

    HTH,
    Bob Barrows


    Bob Barrows Guest

Similar Threads

  1. RecordCount Issues
    By supportcenter in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 9th, 01:05 PM
  2. Getting a recordcount
    By Andy Levy in forum PHP Development
    Replies: 3
    Last Post: October 29th, 08:55 AM
  3. RecordCount with Stored Procedure in SQL Server
    By Ken VdB in forum ASP Database
    Replies: 8
    Last Post: October 21st, 03:01 PM
  4. recordcount -1
    By middletree in forum ASP
    Replies: 8
    Last Post: October 16th, 02:06 PM
  5. RecordCount Property and SQL
    By Scott in forum ASP Database
    Replies: 2
    Last Post: July 22nd, 01:17 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139