Query with ADODB, SQL, Access, and vb,net 2003 Pro

I am trying to update an Access database, but am having trouble with moving from VB6 to VB.Net 2003.  It appears that I can connect using the following code, but I am having trouble when I insert the "rs2.Open(sSql, conn)" line of code.  Please look at the following and see if you can spot what I am doing wrong here.

--------

    Public Sub ConnectToAccess()
        Dim conn As New System.Data.OleDb.OleDbConnection
        Dim rs2 As ADODB.Recordset
        Dim sSql As String
        sSql = "SELECT * FROM TBL_FORMER2"
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\pactiv\former300_data.mdb"
        Try
            rs2 = New ADODB.Recordset
            rs2.Open(sSql, conn)
        Catch ex As Exception
            MessageBox.Show("Failed to connect to data source")
        Finally
            conn.Close()
        End Try
    End Sub

----

Here is the VB6 Code Snipet that I want to replicate in VB.Net

        Set rs2 = New ADODB.Recordset
       
        rs2.Open "SELECT * FROM TBL_FORMER2", sConn2, adOpenKeyset, adLockOptimistic
       
        rs2.AddNew
        rs2.Fields("Time") = Now()
        rs2.Fields("PRODUCT") = Left(cmb_Products.Text, 20)
        rs2.Fields("ALARM STATUS") = kOn
        rs2.Fields("RATE") = CommonRate
        rs2.Update
        If rs2.State = adStateOpen Then rs2.Close
        Set rs2 = Nothing
DLJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should work better
 Public Sub ConnectToAccess()
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim rs2 As System.Data.OleDb.OleDbDataReader
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim sSql As String
        sSql = "SELECT * FROM TBL_FORMER2"
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\pactiv\former300_data.mdb"
        Try
            conn = new System.Data.OleDb.OleDbConnection(conn)
            conn.Open

            cmd = new System.Data.OleDb.OleDbCommand(sSql, conn)

            rs2 = cmd.ExecuteReader

        Catch ex As Exception
            MessageBox.Show("Failed to connect to data source")
        Finally
            conn.Close()

        End Try
    End Sub

Open in new window

0
DLJAuthor Commented:

THanks,
Have a problem.  The conn in this statement is highlighted as a errror.

    Try
            conn = New System.Data.OleDb.OleDbConnection(conn)
                                                                                         ^
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I got that wrong
 Public Sub ConnectToAccess()
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim rs2 As System.Data.OleDb.OleDbDataReader
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim sSql As String
        sSql = "SELECT * FROM TBL_FORMER2"
        
        Try
            conn = new System.Data.OleDb.OleDbConnection 
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\pactiv\former300_data.mdb"
            conn.Open

            cmd = new System.Data.OleDb.OleDbCommand(sSql, conn)

            rs2 = cmd.ExecuteReader

        Catch ex As Exception
            MessageBox.Show("Failed to connect to data source")
        Finally
            conn.Close()

        End Try
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AshokSr. Software EngineerCommented:
Imports System.Data.OleDb
Public Class Form1 Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e as _
System.EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;_
Data Source=C:\emp.mdb;")
'provider to be used when working with access database
cn.Open()
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader
While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
' loading data into TextBoxes by column index
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub
End Class
0
DLJAuthor Commented:
Nice, general go by.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.