Accessing a ms Access file with VB.NET

Talk2Tom11

Talk2Tom11

Well-known member
Joined
Feb 22, 2004
Messages
144
Location
Westchester, NY
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:
[VB] 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[/VB]


Form1:
[VB] 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[/VB]
 
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?
 
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.
 
It seems youre trying the VB6 approach on the .Net.
.Net has a new infrastructure to handle database access.
try this on :

[VB]
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
[/VB]

after that, during your project u can access your dataset with
[VB]
ds.tables("Data").rows(index).item(index) [/VB]
or
[VB]
ds.tables("Data").columns(index).item(index)
[/VB]

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

HF
 
silver83 said:
It seems youre trying the VB6 approach on the .Net.
.Net has a new infrastructure to handle database access.
try this on :

[VB]
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
[/VB]

after that, during your project u can access your dataset with
[VB]
ds.tables("Data").rows(index).item(index) [/VB]
or
[VB]
ds.tables("Data").columns(index).item(index)
[/VB]

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

HF
Click to expand...

thanx you soo much for this code. One more question though. What would the value for the "index", for index be?
 
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 youll need to access .column(4), and the same idea for rows but for rows u cant use a string-type index (I think...never tryed :P)
 
Back
Top