Professional Web Applications Themes

Schema info using ADO - Microsoft SQL / MS SQL Server

I'm having problems using ADO to return any schema information for a SQL Server database. Here's my code (VB6): Dim rs As ADODB.Recordset Dim con As ADODB.Connection Set con = New ADODB.Connection con.Open "Provider=SQLOLEDB.1;" & _ "Integrated Security=SSPI;" & _ "Persist Security Info=False;" & _ "Initial Catalog=Airplanes;" & _ "Data Source=MY-MACHINE" Set rs = con.OpenSchema(adSchemaTables) The code works for other providers (e.g. Jet and Caché) but for SQL Server I get RecordCount = -1. I have tried various recordset property settings, including using a client side cursor but the results are always the same. I've also run the instcat.sql script, ...

  1. #1

    Default Schema info using ADO

    I'm having problems using ADO to return any schema information for a
    SQL Server database. Here's my code (VB6):

    Dim rs As ADODB.Recordset
    Dim con As ADODB.Connection

    Set con = New ADODB.Connection

    con.Open "Provider=SQLOLEDB.1;" & _
    "Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=Airplanes;" & _
    "Data Source=MY-MACHINE"

    Set rs = con.OpenSchema(adSchemaTables)

    The code works for other providers (e.g. Jet and Caché) but for SQL
    Server I get RecordCount = -1.

    I have tried various recordset property settings, including using a
    client side cursor but the results are always the same. I've also run
    the instcat.sql script, to no effect. The same applies to other schema
    types (e.g. adSchemaColumns, adSchemaPrimaryKeys) with the appropriate
    criteria settings.

    Can anyone suggest what I'm doing wrong?

    Many thanks.
    onedaywhen Guest

  2. #2

    Default Re: Schema info using ADO

    You do realize that if you want a client-side recordset, you have to use
    Open to open it after setting the cursorlocation property ...? Execute
    returns the default server-side forward-only cursor, which should always
    have a recordcount of -1.

    Using your code with a Jet database, I got -1 for the recordcount. What
    code did you use with Jet to get a recordcount > 0?

    I submit that you should not be worrying about what the recordcount is.
    Check the EOF property to verify whether the recordset contains any records.
    Use GetRows to stuff the data in the recordset into an array if you need to
    find out how many records there are - Ubound(GetRowsArray,2)+1 will give you
    your record count.

    HTH,
    Bob Barrows

    onedaywhen wrote: 


    Bob Guest

  3. #3

    Default Re: Schema info using ADO

    Bob,
    Of course you were right. Although the RecordCount is -1 the EOF
    property is false. so I'm now using a Do While Not .EOF loop rather
    than a For..Next loop.

    And I was plain wrong about the recordcount for a Jet datasource. I
    forgot my code used ADOX to get the schema for Jet!

    Many thanks for your help.

    "Bob Barrows" <com> wrote in message news:<#phx.gbl>... [/ref]
    onedaywhen Guest

Similar Threads

  1. dumping the schema ?
    By raptor in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 29th, 08:23 PM
  2. xml schema validation with php
    By Ben Fitzgerald in forum PHP Development
    Replies: 1
    Last Post: November 18th, 08:58 PM
  3. Is it possible to edit schema with VSG v3.1?
    By Doug Baroter in forum Informix
    Replies: 0
    Last Post: November 2nd, 10:57 PM
  4. managing DB schema with VSS
    By Bryan Oliver in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 12th, 02:38 PM
  5. Importing XML Schema into 9.2.0.1
    By Stephen Hirsch in forum Oracle Server
    Replies: 0
    Last Post: June 24th, 03:38 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