Newb: Getting into an Access 2002 db with vb.net
Hi, I'm very new to application programming, but have a good number of years doing web based stuff with PHP/SQL.
What are the first steps I need to do to get into an Access 2002 database with Visual Basic 2008 Express?
First add this above the "Public Class Form1" line of your application:
The Imports statement opens a "namespace" for you, so you can write commands to it without explicitly calling it. Without this, you would have to properly call variable types like this: System.Data.OleDB.OleDBConnection
Next, read this post that I made that describes how to connect to and manipulate data in an Access database (this is a common enough question that I wrote a tutorial for it a few months ago):
I was never able to attach that screenshot...
-The top code box describes the objects I put on the form and what changes I made to them.
-The second describes how I set up the MS Access DB
-The third contains the code on my form, inside that you should find ample code and commenting to help you understand this. Feel free to pose any questions you'd like
First of all, THANK YOU! The code you wrote is a huge help and great learning tool.
I'm having a hard time figuring out the listview stuff. It doesn't look like lvData was declared in the code, so I put [code]Dim lvData As ListView[/code] in line 10 to get rid of the errors saying it wasn't declared yet, but it didn't really solve the issue (I think).
I'm able to open the database, but can't do any functions to records within it.
The error points to a NullReferenceException when lvData.Items is referenced.
I can give more detailed information if you want, but I'm assuming that the error's happening because I put the lvData dim in the wrong spot...?
You are right, you have declared lvData in the wrong spot.
Remove that line entirely. Instead, add a listview to your Form, and name it lvData. There are a few settings you will need to set on it, those settings are detailed in the top code box of the MS Access DB Program post.
The reason you recieve a Null reference error is because you are trying to access elements of the object lvData before it has been initialized. You would need a second line:
Dim lvData as ListView
lvData = new ListView() 'This is due to how Class Objects work, i am not good at explaining this
After looking at that post again... The entire first code box is describing objects that are to be drawn onto a form. I no longer have this project on this computer, but I could draw the form up and post a screenshot if you need me to.
Be sure that you set all of the properties for the listview, the most important one is the property "View" being set to Details.
That worked really well. Once I got the listview object in the form it all kind of fell into place. Thank you very much for the help.
I have 2 questions for you:
Is there any way to make it so a database loads when the form does, so the user doesn't have to hit the open button?
Is there any way to load the data within the database at the same time it's loaded, so the user doesn't have to hit the refresh button?
Nevermind, I figured it out
I put this in Form 1:
[code]Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim connString As String = "provider= microsoft.jet.oledb.4.0; " & _
Dim conn As New OleDbConnection(connString)
Catch ex As OleDbException
' Display error
MsgBox("Error: " & ex.ToString())
' Close Connection
And then I just copied and pasted the information from the RefreshLV sub you wrote and it loads fine!
I was in the middle of composing a reply when I saw your's.
Here is a note on having a filepath programmed straight in: (here I use constant variables, but you could program the filepath in straight. I prefer to make changes in only one place, so if I call FilePath in a bunch of places, only need to change it in the one delcaration)
How to do it is dependent on your program, but you can do things like this:
Const FilePath As String = "C:WhereItIsFileName.Mdb"
And put a Static path in (this would only be good in he instance that you know that the filepath would never ever... EVER... change. I would definitely not suggest this unless you were making a very short term solution, or a one time program)
If you program a static path, you don't have to prompt the user and ask where a file is, you can just supply FilePath (or whatever you name that variable).
If you want the path of the file to be relative to the folder the application is running from, you can do this:
Const FilePath as String = "FileName.mdb"
Const FilePath as String = "DataFileName.mdb"
If you want the file to be located anywhere on the computer, and it has a chance to move somewhere, you would want to save the file path to a flatfile and load that path back in when you load, it gets more complicated from there. Then you can check for the existence of the file (System.IO.File.Exists(FilePath)) and prompt for a new filePath when it couldn't find it.
I suggest setting it up to run relative to the application's path, unless you are writing a program that is only going to be used once or for a VERY brief time. (If it's for work, spend the extra time making it work better to save yourself a lot of time upgrading it in the future)
When you are debugging an application, it's startup path will be close to the path in parenthese. When you debug, the program actually compiles an exe file in this folder (which you could in theory just copy out of there to deploy the program on someone's PC)
(for VS 2008, it will be something like this: C:Visual Studio 2008 ProjectsProject NameProject NameBinDebug)
[b]You're awesome. [/b]
Is it possible for a user to search within a specific field in a ListView box? For example, FName?
Typically I store the data outside of the ListView, and search on that, but you can search through a listview.
I am not sure what exactly you want to do. I most comonly search ListViews for Identities (or a unique value, integer or string, that indetifies the record and makes it unique). Normally I store the unique identifiers in the Tag Field of a row in a list view
'This is how I add it
Dim xItem as New ListViewItem("FirstName")
xItem.Tag = UniqueKey 'This could be the primary key from the Database
'This is how I search on the tag
For r as integer = 0 to ListView1.Items.Count - 1
If ListView1.Items(r).Tag = SearchTerm Then
MsgBox("Found it on row: " & r)
Exit For 'This escapes the loop
' If I only need to find one, I don't want to continue searching
' In other words, Unique means 1 is only found*
'this searches all columns of each item
'and sends a message box when it finds them
Dim SearchTerm as String
For r as integer = 0 to ListView1.Items.Count - 1
For c as integer = 0 to Listview1.Items(r).SubItems.Count - 1
If ListView1.Items(r).SubItems(c).Text = SearchTerm
MsgBox("Found one on row: " & r & " column: " & c)
'Lets say I want to search for multiple records, and return that in an array.
'Since a 2 dimensioned array is a pain, I will use the Point object
'which holds an X (for Column) and Y (for Row)
Public Function GetSearchResultsInArray(ByVal SearchTerm as String) as Point()
Dim P() as Point()
'This is tricky, we don't know how many results there will be
'So we are going to have to Redimension the bounds of this array
'during run time. We will use ReDim for that, and ReDim Preserve
'To know when to do which, we need to check if any values exist in P() yet
'When No Bounds have been Dimensioned on an Array, it will cause an Exception (slips my mind which, but probably a NullReferenceError)
'So we will check and see if it 'Is Nothing'.
For r as integer = 0 to ListView1.Items.Count - 1
For c as integer = 0 to ListView1.Items(r).SubItems.Count - 1
If ListView1.Items(r).SubItems(c).Text = SearchTerm Then
'Check the state of P()
If isNothing(P) = True Then
ReDim P(0) 'ReDim makes the object lose all of it's content
ReDim Preserve P(P.Length) 'The Preserve keyword makes P keep it's contents
' P.Length - 1 is the uppermost bound of the array, the one we just made
P(P.Length - 1) = New Point(C, R)
'This has a case at returning a Null P() object, make sure you check
'Example of how in next function
Public Sub TestIt()
Dim PointArray() as Point
PointArray = GetSearchResultsInArray("TestTerm")
If IsNothing(PointArray) = True Then
For i as integer = 0 to PointArray.Length - 1
MsgBox("Column: " & PointArray(i).X & ", row: " & PointArray(i).Y)
hope that helps
i'm very new in VB.net programming,...
I've tried to follow your source code and able to insert a new record into the list view... but when i close and restart the program then load the database again, the record is not shown.. and when i open the cust.mdb file, there isn't any record inside....
what should i do to keep the record in the database file?
sorry if my english isn't that good...
This post doesnt really cover using a program to access data, but instead some specific problems this poster was having... I wrote a nice post with some great code examples on using an MS Access DB with code, here it is:
Sorry for the delay in response
thanks for your reply.
i finally managed to modify the source code with my friend's help..
now the database is working properly...
It looks like you're new here. If you want to get involved, click one of these buttons!
Assembly Code Share
Getting started in assembly
C and C++
C/C++ on Linux/Unix
C/C++ Windows API
C++ Game Development
Delphi and Kylix
Java Server Pages
Access databases and VB
Advance Visual Basic
DirectX Game dev
Newbie Game Programmers
Cooling & Overclocking
Database & SQL
Sound & Music
FreeLance Software City
C# & VB.NET School Support
Join the Team
Comments on this site
New programming languages
Off topic board
Mobile & Wireless
Operating Systems & Platforms
Witsbits Go Cloud
Embedded / RTOS
Windows CE & Pocket PC
Networking And Security
Windows 2003 Server
RUP & UML
Quality & Testing
Active Server Pages
HTML & WEB-Design
Mobile Internet & Messaging
WEB-Services / SOAP
In this Discussion