We help IT Professionals succeed at work.

Convert VB6 data connection and ADODB.Recordset syntax to VB.Net syntax

Dovberman
Dovberman asked
on
4,139 Views
Last Modified: 2013-11-26
I am building a new windows application using Visual Studio 2005
This app is based on a VB 6 app.

How do I convert VB6 data connection and ADODB.Recordset syntax to VB.Net syntax?

'Create a connection object
'Works for VB6 but not for VB.NET
Set gcnnStocks = New ADODB.Connection
    gstrConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " _
    & "Data Source=C:\Develop\Apps\StockSelect\Data\" _
    & "StockSelect.mdb"
    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=physical path to .mdb file
   
    'Create the connection.
    gcnnStocks.Open gstrConnect

'Create a dataset to replace VB6 ADODB.Recordset
'Works for VB6 but not for VB.NET
Function GetEventDate(ByVal pstrDateFieldName As String) As Date
    Dim rstStockTrend As ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT " & pstrDateFieldName & " FROM tblStockTrendApp "
   
    Set rstStockTrend = New ADODB.Recordset
    rstStockTrend.CursorType = adOpenKeyset
    rstStockTrend.LockType = adLockOptimistic
    rstStockTrend.Open strSQL, gcnnStocks
   
    GetEventDate = rstStockTrend(pstrDateFieldName)
    rstStockTrend.Close
End Function

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
Try something like this:

Imports System.Data.OleDb

...

    Private Function GetEventDate(ByVal dateFieldName As String) As Date
        Dim connectionString As String = My.Settings.ConnectionString
        Using connection As New OleDbConnection(connectionString)
            connection.Open()

            Dim commandText As String = "SELECT " & dateFieldName & " FROM tblStockTrendApp"
            Using command As New OleDbCommand(commandText, connection)
                Return command.ExecuteScalar()
            End Using
        End Using
    End Function

Bob
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
The connection string is stored as an application setting from right-clicking on the project in the Solution Explorer, and selecting Properties, and finding the Settings tab, and adding an Application setting for connection string.

Bob

Author

Commented:
This was helpful. I found out about the My.settings feature which simulates application variables.
I simplified the code for testing purposes and used a sub instead of a function.

I get the following exception argument:
Format of the initialization string does not conform to specification starting at index 35.

   Sub Main()
        Dim gstrConnect As String = My.Settings.AccessConnectionString
        Using connection As New OleDbConnection(gstrConnect)  'Exception argument here
            connection.Open()

            Dim commandText As String = "SELECT * FROM tblStockTrendApp"
            Using command As New OleDbCommand(commandText, connection)
                  Stop ' examine the command object properties.
                  'Return command.ExecuteScalar() Apply in a function that returns a dataset ?
            End Using
        End Using
         
    End Sub

Any ideas?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
1) With ExecuteScalar, you need a single field in a single row, so you need to create an SQL statement with a single field and a Where clause to get that condition.

2) What does the connection string value for 'gstrConnect' equal?

Bob

Author

Commented:
gstrConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " _
    & "Data Source=C:\Develop\Apps\StockSelect\Data\" _
    & "StockSelect.mdb"

this connection string works in VB 6.0 for the same database.

Does the following return a dataset ?
command.ExecuteReader(CommandBehavior.Default)

Author

Commented:
Thanks for the hint. My connection string was improperly concatonated.

Now for the main question.  How do I return a dataset? Are the rows and columns returned from command.ExecuteReader(CommandBehavior.Default) as expected from a SELECT * FROM Tablename?

I believe I need the datareader.

Is this correct?

Using connection As New OleDbConnection(gstrConnect)
            connection.Open()

            Dim commandText As String = "SELECT * FROM tblStockTrendApp"
            Using command As New OleDbCommand(commandText, connection)

                Dim MyDataReader As OleDbDataReader = command.ExecuteReader()
                'Use the properties and methods of MyDataReader to work with the query results

            End Using
        End Using
Thanks,

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I lke the adapter option. The .NET framework supports a much richer base for data centric applications than VB6.

Thanks,
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.