[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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
0
DLJ
Asked:
DLJ
  • 2
  • 2
1 Solution
 
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
 
AshokCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now