none
Accessing SQL Server 2000 remote data using ADODB objects in VB.NET RRS feed

  • Question

  •  

    For reasons too convoluted to get into, I need to pull data from a SQL Server 2000 database that is in a remote location into an ADODB Recordset within a VB.NET (2003) application.  Here is my code so far:

     

     

    Dim myRecordset As ADODB.Recordset

    Dim myConnectionString As String = "Provider=SQLOLEDB;" & _

    "Data Source=myRemoteServer;" & _

    "Initial Catalog=myDatabase;" & _

    "Trusted_Connection=yes"

    Dim myADOSQLConnection As New ADODB.Connection

    Dim mySQLQuery As String = "SELECT * FROM myTable"

    myADOSQLConnection.Open(myConnectionString)

    myRecordset.Open(mySQLQuery, myADOSQLConnection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)

    myADOSQLConnection.Close()

     

    What am I doing wrong here?

     

    BTW...  I know many of you will suggest I update to 2.0, or use datasets, or use a SqlConnection object... thanks, I thought of all of that, I can't do it now.  That's the next step, but I have to get this application to IJW status.

     

    Friday, September 28, 2007 4:24 PM

All replies

  • Never mind... I figured out that I was missing the keyword "New" when I declared the ADODB.Recordset.

     

    Should have said this:

     

    Dim myRecordset As New ADODB.Recordset

     

    Oh well.

     

    UPDATED:  This wound up only allowing me to create the connection... I still didn't retrieve any records.

     

    So, with this correction, why am I still not able to create my recordset?

    Friday, September 28, 2007 5:00 PM