ADO: SQL OLE DB vs. ODBC

ADO: SQL OLE DB vs. ODBC

Post by Grant Fritche » Thu, 10 Dec 1998 04:00:00



I've run into a problem I can't solve.

I'm performing my first connection with ADO through OLEDB, not ODBC. I
wanted to perform a simple procedure call to sp_databases. No parameters, no
weirdness. I connected to the database just fine. I created a command with
sp_databases as the CommandText and adCmdStoredProc as the CommandType.
Everything looked great. Then I tried to execute the command into a
recordset. I kept getting a recordset not open error. I tried all kinds of
different parameters, changing the cursor from client to server and back
again, setting the connection mode...

Anyway, nothing worked. Then I changed my connection from using the SQLOLEDB
provider to using the default. I set up an ODBC DSN and tried running the
proc again. This time it worked just fine. Wonderful. Now I can use ODBC to
make calls to the database. I haven't moved forward at all. I might as well
stick to RDO (or ODBC API calls in a pinch).

What am I doing wrong?

Here's the sample code:

Set gconDB = New ADODB.Connection
gconDB.Provider = DBPROVIDER
With gconDB
    .ConnectionString = "Data Source=" & txtDataSource.Text & _
        ";User ID=" & txtUserID.Text & ";Password=" & txtPassword.Text
    'ODBC Connection String
    '.ConnectionString = "Data Source=CTEDEV;User ID=" & _
        txtUserID.Text & ";Password=" & txtPassword.Text
    .Open
End With

Set comDBList = New ADODB.Command
With comDBList
    .ActiveConnection = gconDB
    .CommandText = "sp_databases"
    .CommandType = adCmdStoredProc
End With
Set rsDBList = comDBList.Execute

 
 
 

1. ODBC Vs ODBCDirect Vs ADO Vs OLE/DB advise

We have a relatively large VB 5.0 (Professional Editiion)  application which
primarily accesses tabular databases using DAO.

We wish to add a simple database explorer -- a bit of code that returns a
lists of servers and databases by driver.  It would mimic the network
neighbourhood app but for SQL databases.

The standard ODBC 2.0 call is SQLBROWSECONNECT. Can anyone confirm

1. whether this can this can only be achived using VB 5.0 (Enterprise
Edition) using ODBCDIRECT services?

2. whether  ADO or OLE/DB provide high level facilities for locating drivers
and returning a list of servers and databases?

3. whether I am correct in assuming that DAO and ODBC connectivity will be
superceded by ADO and OLE/DB?

4.whether  you will be able to write applications using VB which call OLE/DB
irectly - the current spec requires C++

5. whether there is a later version of ODBC that is more suitable for VB?

6. whether there is a third party database explorer library  suitable for VB
that does this task?

Finally the current SQLDRIVERS function will return a list of drivers if
they are installed on a local machine. If drivers are located on servers
rather than locally installed does the SQLDrivers function list (locate)
these drivers as well?

2. Runtime error "430" / OLEDB

3. OLE DB for SQL vs. OLE for ODBC

4. BulkCopy and Export Data

5. ODBC vs OLE DB (ADO)

6. Data combo help

7. help with runtime error

8. ODBC vs ADO/OLE DB

9. ADO/OLE DB vs Native vs RDO - Oracle

10. ADO 2.5 vs 2.6 with SQL OLE DB provider

11. OLE DB vs ODBC for Oracle DB access

12. ODBC provider vs MS SQL provider (OLE DB)