Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Newb: Getting into an Access 2002 db with vb.net

mikepreblemikepreble Posts: 5Member
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?


«1

Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭

    First add this above the "Public Class Form1" line of your application:
    [code]
    Imports System.Data
    Imports System.Data.OleDB
    [/code]

    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):
    http://www.programmersheaven.com/mb/VBNET/386345/386345/program-demonstrating-ms-access-database-manipulation/?S=B20000

    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

    Sean Campbell
  • mikepreblemikepreble Posts: 5Member
    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...?

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    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
    -----------------------
    SeanCampbell
  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    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.
  • mikepreblemikepreble Posts: 5Member
    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?
  • mikepreblemikepreble Posts: 5Member
    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; " & _
    "data source=c:/dbtest/db1.mdb;"

    Dim conn As New OleDbConnection(connString)

    Try
    conn.Open()

    Catch ex As OleDbException
    ' Display error
    MsgBox("Error: " & ex.ToString())
    Finally
    ' Close Connection
    conn.Close()

    End Try[/code]

    And then I just copied and pasted the information from the RefreshLV sub you wrote and it loads fine!
  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    Good work!!
    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"
    or
    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)
  • mikepreblemikepreble Posts: 5Member
    [b]You're awesome. [/b]

    Is it possible for a user to search within a specific field in a ListView box? For example, FName?
  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    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
    [code]
    'This is how I add it
    Dim xItem as New ListViewItem("FirstName")
    xItem.subItems.add("LastName")
    xItem.subItems.add("FamilyName")
    xItem.subItems.add("PetName")
    xItem.Tag = UniqueKey 'This could be the primary key from the Database

    ListView1.Items.Add(xItem)
    [/code]

    [code]
    '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*
    End If
    Next r
    [/code]

    [code]
    '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)
    End If
    Next c
    Next r
    [/code]

    [code]
    '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
    ElseIf
    ReDim Preserve P(P.Length) 'The Preserve keyword makes P keep it's contents
    End If
    ' P.Length - 1 is the uppermost bound of the array, the one we just made
    P(P.Length - 1) = New Point(C, R)
    End If
    Next c
    Next r

    Return P
    'This has a case at returning a Null P() object, make sure you check
    'Example of how in next function
    End Function

    Public Sub TestIt()

    Dim PointArray() as Point
    PointArray = GetSearchResultsInArray("TestTerm")
    If IsNothing(PointArray) = True Then
    MsgBox("No Results")
    Else
    For i as integer = 0 to PointArray.Length - 1
    MsgBox("Column: " & PointArray(i).X & ", row: " & PointArray(i).Y)
    Next i
    End If

    End Sub
    [/code]

    hope that helps
    FireSickle.Com
  • ario_fario_f Posts: 2Member
    hi.

    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...

    thank you...
«1
Sign In or Register to comment.