asked on

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

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

Bob Learned

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

Bob Learned

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

Dovberman

ASKER

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?

Bob Learned

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

Dovberman

ASKER

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)

Dovberman

ASKER

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,

ASKER CERTIFIED SOLUTION

Bob Learned

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dovberman

ASKER

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

Thanks,