Accessing a ms Access file with VB.NET

[Talk2Tom11]

Hey, I am trying to read info that I have stored in a ms access file. I have a module and a form. below is the code that I am currently using. It is not reading form the database and I am not sure why... if anyone can take a look at the and tell me what I have done wrong then that would be great... thanks.

 

 

Module:

    Public rs As New ADODB.Recordset
   Public con As New ADODB.Connection
   Public agentController As AgentObjects.Agent
   Public agentCharacter As AgentObjects.IAgentCtlCharacter


   Sub load_data()
       con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Database.mdb;Persist Security Info=False")
       rs.Open("SELECT * FROM Data", con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
   End Sub

 

 

Form1:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       Call load_data()

       If TextBox1.Text = rs.Fields!Last.Value Then

           Label2.Text = rs.Fields!Address.Value
           Label3.Text = rs.Fields!Town.Value
           Label4.Text = rs.Fields!State.Value
           Label5.Text = rs.Fields!Zip.Value
       End If

   End Sub

[PlausiblyDamp]

If you step through in a debugger is rs being set to anything in the load_data method?

 

Also is there a reason you are using ADO code rather than ADO.Net code?

[Talk2Tom11]

I am sorry but this is my very first time attempting to do ths type of thing where i read something from an access file. I have neve used ado or ado.net before, so this is my first time. is there other code that you feel would be better? This is just code i had read about online somewhere.

[silver83]

It seems you're trying the VB6 approach on the .Net.

.Net has a new infrastructure to handle database access.

try this on :

 

   Public ds As DataSet
   Public da As OleDb.OleDbDataAdapter
   Public conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Database.mdb")

   Private Sub load_data()
       Dim Qrystr As String = "SELECT * FROM Data"
       ds = new dataset
       da = New OleDb.OleDbDataAdapter(Qrystr, conn)
       conn.Open()
       da.Fill(ds, "Data")
       conn.Close()
   End Sub

 

after that, during your project u can access your dataset with

ds.tables("Data").rows(index).item(index)  

or

ds.tables("Data").columns(index).item(index)

 

where "index",for columns, can be either a number or the name of the column header. (ie. "headername")

 

HF

[Talk2Tom11]

It seems you're trying the VB6 approach on the .Net.

.Net has a new infrastructure to handle database access.

try this on :

 

   Public ds As DataSet
   Public da As OleDb.OleDbDataAdapter
   Public conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Database.mdb")

   Private Sub load_data()
       Dim Qrystr As String = "SELECT * FROM Data"
       ds = new dataset
       da = New OleDb.OleDbDataAdapter(Qrystr, conn)
       conn.Open()
       da.Fill(ds, "Data")
       conn.Close()
   End Sub

 

after that, during your project u can access your dataset with

ds.tables("Data").rows(index).item(index)  

or

ds.tables("Data").columns(index).item(index)

 

where "index",for columns, can be either a number or the name of the column header. (ie. "headername")

 

HF

 

thanx you soo much for this code. One more question though. What would the value for the "index", for index be?

[silver83]

in your example, wither the name of the column , "Address", "City" etc, or the number of the column, lets say if u want to excess the "address" column and you know its third (starting from 0 ) then you'll need to access .column(4), and the same idea for rows but for rows u can't use a string-type index (I think...never tryed :P)