VB6 to VB.Net

Posted on 2005-03-28
Medium Priority
Last Modified: 2010-04-23
Can somebody please help me convert the following code to vb.net?  I used the wizard, but it just kept the DAO code.  I'm using an Access database.  Thanks in advance.  p.s.  I have to wait until I get back to work tomorrow before I can try it, so there will be a delay in awarding points.

Dim MyRS As DAO.Recordset
Dim AnotherRS As DAO.Recordset

Set MyRS = MyDB.OpenRecordset("TableName", dbOpenTable)
With MyRS
Do Until .EOF

    strSQL = "SELECT COUNT(*) AS MyCount FROM ListTable WHERE MyNUMBER = '" & _
    Set AnotherRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    !Count1 = CountRS!MyCount
End With
Question by:sg1nx01
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 10

Expert Comment

ID: 13649494
try this

Dim sqlDS  as DataSet = new DataSet()
strSQL = "SELECT COUNT(*) AS MyCount FROM ListTable WHERE MyNUMBER = '" & _

Dim  sqlDA as SqlDataAdapter= new SqlDataAdapter(strSQL,sqlConn)

btw where is this CountRS coming from??

Dim tempda  as SqlDataAdapter = new SqlDataAdapter("Select * from TableName",con)
Dim tempds as DataSet = new DataSet()
Dim cb as SqlCommandBuilder= new SqlCommandBuilder(tempda)
dt = tempds.Tables("Spares")
dim r as DataRow =  dt.Rows(IndexOfRowToUpdate)
r[1] = Int32.PArse(sqlDS.Tables(0).Rows(0)(0))

Author Comment

ID: 13649627
Sorry, it's supposed to be
!Count1 = AnotherRS!MyCount
LVL 25

Accepted Solution

RonaldBiemans earned 1500 total points
ID: 13650548
try something like this

Dim MyRS As New DataSet
        Dim da As New OleDb.OleDbDataAdapter("select * from your table", "your connection")
        For Each dr As DataRow In MyRS.Tables(0).Rows
            Dim strSQL As String = "SELECT COUNT(*) AS MyCount FROM ListTable WHERE MyNUMBER = '" & dr.Item("MyNUMBER") & "'"
            Dim cmd As New OleDb.OleDbCommand(strSQL)
            cmd.Connection = yourconnection
            dr.Item("count1") = cmd.ExecuteScalar

if you use sql server

Dim MyRS As New DataSet
        Dim da As New SQLCLIENT.sqlDataAdapter("select * from your table", yourconnection)
        For Each dr As DataRow In MyRS.Tables(0).Rows
            Dim strSQL As String = "SELECT COUNT(*) AS MyCount FROM ListTable WHERE MyNUMBER = '" & dr.Item("MyNUMBER") & "'"
            Dim cmd As New SQLCLIENT.SQLCommand(strSQL)
            cmd.Connection = yourconnection
            dr.Item("count1") = cmd.ExecuteScalar
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 27

Expert Comment

ID: 13651830
Sample with dataset


Public Class frmADOToolTip
    Inherits System.Windows.Forms.Form
    Dim sField As String = String.Empty

#Region " Windows Form Designer generated code "

    Public Sub New()

        'This call is required by the Windows Form Designer.

        'Add any initialization after the InitializeComponent() call
        PopulateListFromDS(DS, "ProductName")
    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
            End If
        End If
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents tipView As System.Windows.Forms.ToolTip
    Friend WithEvents lstBoxResults As System.Windows.Forms.ListBox
    Friend WithEvents RadioButton1 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton2 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton3 As System.Windows.Forms.RadioButton
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.lstBoxResults = New System.Windows.Forms.ListBox
        Me.tipView = New System.Windows.Forms.ToolTip(Me.components)
        Me.RadioButton1 = New System.Windows.Forms.RadioButton
        Me.RadioButton2 = New System.Windows.Forms.RadioButton
        Me.RadioButton3 = New System.Windows.Forms.RadioButton
        Me.lstBoxResults.Location = New System.Drawing.Point(8, 8)
        Me.lstBoxResults.Name = "lstBoxResults"
        Me.lstBoxResults.Size = New System.Drawing.Size(176, 82)
        Me.lstBoxResults.TabIndex = 0
        Me.tipView.SetToolTip(Me.lstBoxResults, "Click to test the tool tip")
        Me.RadioButton1.Location = New System.Drawing.Point(204, 4)
        Me.RadioButton1.Name = "RadioButton1"
        Me.RadioButton1.TabIndex = 1
        Me.RadioButton1.Text = "By Unit Price"
        Me.RadioButton2.Location = New System.Drawing.Point(204, 32)
        Me.RadioButton2.Name = "RadioButton2"
        Me.RadioButton2.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton2.TabIndex = 2
        Me.RadioButton2.Text = "By Units In Stock"
        Me.RadioButton3.Location = New System.Drawing.Point(204, 60)
        Me.RadioButton3.Name = "RadioButton3"
        Me.RadioButton3.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton3.TabIndex = 3
        Me.RadioButton3.Text = "By Product Name"
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(332, 117)
        Me.Name = "frmADOToolTip"
        Me.Text = "ADO ToolTip Test"

    End Sub

#End Region
    '/ <summary>
    '/ The main entry point for the application.
    '/ </summary>
    <STAThread()> Public Shared Sub Main()
        Application.Run(New frmADOToolTip)
    End Sub
    Private Sub InitializeData()

        Cursor.Current = Cursors.WaitCursor
        sDBName = "C:\Visual Studio Projects\TestArea\Northwind.mdb"       '<---- A standard access database from Microsoft
        sTableName = "Products"

        Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
        'EXTRA CODE

        sSql = ""
        sSql = "SELECT * "
        sSql += "FROM " & sTableName & " "
        sSql += "ORDER BY ProductName"
            'TblAdapter = New SqlDataAdapter(sSql, Connect)            'FOR SQL Server
            'TblAdapter = New Odbc.OdbcDataAdapter(sSQL, SyConnect)    'FOR SyBase Server
            ADOAdapter = New OleDb.OleDbDataAdapter(sSql, Con)         'FOR ADO Access database

        Catch Exp As Data.OleDb.OleDbException
            MsgBox("FillDataSet Procedure Error", MsgBoxStyle.Critical, "Load Report Error")
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try
        Cursor.Current = Cursors.Default
    End Sub
    Private Sub lstboxResults_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstBoxResults.Click
        Dim sCost As String
        Dim sUnits As String
        Dim str As String
        Select Case True
            Case RadioButton1.Checked      'By Unit Price
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Product Name: " & sCost & vbCr & "Unit In Stock: " & sUnits
            Case RadioButton2.Checked      'By Units In Stock
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                str = "Product Name: " & sCost & vbCr & "Cost: " & Format(CDbl(sUnits), "$###.00")
            Case RadioButton3.Checked      'By Product Name
                sCost = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Cost: " & Format(CDbl(sCost), "$###.00") & vbCr & "Unit In Stock: " & sUnits
            Case Else
                sCost = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Cost: " & Format(CDbl(sCost), "$###.00") & vbCr & "Unit In Stock: " & sUnits
        End Select

        tipView.SetToolTip(Me.lstBoxResults, str)
    End Sub
    Private Sub PopulateListFromDS(ByVal DS As DataSet, ByVal sFieldName As String)
        ' A table in a dataset is used to populate a list box with
        ' product name from the products table.
        ' With the dataset the name of the table and field must be known
        ' at design time. If they are misspelled or mistyped
        ' an error will be generated only at runtime.

        Dim s As String
        Dim i As Integer


        For i = 0 To DS.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the product name for each record.
                s = DS.Tables(0).Rows(i)(sFieldName).ToString()
                ' Add product name to the list box
            End If
        Next i
    End Sub
    Private Sub RadioButtonChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged, RadioButton2.CheckedChanged, RadioButton3.CheckedChanged
        Select Case sender.text
            Case "By Unit Price"
                sField = "Unitprice"
            Case "By Units In Stock"
                sField = "UnitsInStock"
            Case "By Product Name"
                sField = "ProductName"
        End Select
        PopulateListFromDS(DS, sField)
    End Sub
End Class


Imports System.Data
Imports System.Data.OleDb
Module ModADO
#Region " MY Global Vars "
    'Const adOpenStatic = 3
    'Const adLockOptimistic = 3
    'Const adCmdText = &H1
    '********* database Var's ************
    Public sSql As String
    Public sTableName As String
    Public iCounter As Short
    Public myDataRow As DataRow
    Public DS As New DataSet
    Public ADOAdapter As OleDb.OleDbDataAdapter  '(ADO Access)

    '**** Server Connection Vars *********
    Public sDBServer As String               '<--- Server Name
    Public sUSERid As String                 '<--- User Id
    Public sDBName As String                 '<--- Database Name
    Public sDBPass As String                 '<--- Password
    Public Con As OleDb.OleDbConnection      '<--- for Access

    '******* Standard Var's *************
    Public sAppPath As String = System.Windows.Forms.Application.StartupPath
    Public bLogin As Boolean
#End Region
#Region " MY Server Connections "

    '***Sample for a Access Database***
    'User ID=Admin;
    'Data Source=C:\MyDatabase.mdb;
    'Mode=Share Deny None;
    'Extended Properties="";
    'Locale Identifier=1033;
    'Persist Security Info=False;
    'Jet OLEDB:System database=C:\SYSTEM.MDW;
    'Jet OLEDB:Registry Path="";
    'Jet OLEDB:Database Password="";
    'Jet OLEDB:Engine Type=5;         'Type=4 is for Access 95
    'Jet OLEDB:Database Locking Mode=1;
    'Jet OLEDB:Global Partial Bulk Ops=2;
    'Jet OLEDB:Global Bulk Transactions=1;
    'Jet OLEDB:New Database Password="";
    'Jet OLEDB:Create System Database=False;
    'Jet OLEDB:Encrypt Database=False;
    'Jet OLEDB:Don't Copy Locale on Compact=False;
    'Jet OLEDB:Compact Without Replica Repair=False;
    'Jet OLEDB:SFP=False

    Public Sub OpenDBADOConnection()
            Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
#End Region
End Module

Expert Comment

ID: 13653810
what version of access is the database? Can you upgrade it to 2000 or higher, or SQL Server

Author Comment

ID: 13667586
The Access version is 2000.

RonaldBiemans, I tried your solution and I think it will work, but for some reason it's not updating the "count1" column in the "TableName" table.  It doesn't give me any errors.

I walked through it in debug and it does compare each "MyNumber".  When I run an Access query, I do get hits, so I know there are matches on the "MyNumber" column.  Is there a step missing?  It never refers back to the "as MyCount" in the SQL statement.

Expert Comment

ID: 13668232
it seems that you are used to using dao and ado is not much different. instead of using the disconnected approach that .net uses you could use ADO. just add the COM reference to ADO 2.8 or 2.7 and do this

Dim MyDB As New ADODB.Connection
Dim MyRS As New ADODB.Recordset
Dim AnotherRS As New ADODB.Recordset

MyDB.CursorLocation = adUseClient
MyDB.Open "[Connection String]"

MyRS.Open "Select * From TableName", MyDB, adOpenStatic, adLockOptimistic
With MyRS
    Do Until .EOF
        Dim strSQL As String
        strSQL = "SELECT COUNT(*) AS MyCount FROM ListTable WHERE MyNUMBER = '" & .Fields("MyNUMBER").Value & "'"
        AnotherRS.Open strSQL, MyDB, adOpenStatic, adLockOptimistic
        .Fields("Count1").Value = AnotherRS.Fields("MyCount").Value
End With

'cleanup and get rid of the object when we are done with them
Set MyRS = Nothing
Set AnotherRS = Nothing
Set MyDB = Nothing

Author Comment

ID: 13676702
The missing step was the AcceptChanges method.  After that, it worked like a charm.  Thanks SO much for your help.

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question