Using a dataset.  Path to database?

Posted on 2007-08-06
Last Modified: 2013-11-05
I have generated a dataset for my access database using the wizard in vs2005.  Since the connectionstring was created with the wizard, do I need to declare the connection string in my form code to access the data?  The path embedded by the wizard for the dataset is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\BiliProject.mdb

If I need to declare this in my form code, do I need to use the pipes ( | ), in the path?

The wizard named the datasource as BiliProjectDataSet.  I want to read, write and update records to this database.  My understanding is a little murky at this point regarding the wizard configured dataset and one being declared in code.

Question by:leachj
    LVL 53

    Expert Comment

    You'll need the pipes because your database will be standing in a subdirectory of the running program.
    LVL 27

    Expert Comment

    If the Wizard helped you created a strongly-typed dataset from a database, such as Access, and
    it created a TableAdapter for each table added to the dataset, then you don't need to declare the
    connection string in code.  You can tell if a TableAdapter was created for each table if you look in
    the dataset designer, and you look at a table, you'll see the <tablename>TableAdapter on the bottom-half.

    Here's an example where I created a strongly-typed DataSet with a TableAdapter, that included an
    imbeded connection.  Notice that I do not declare the connection string.

            'Declare your TableAdapter and Table from your strongly typed dataset.
            Dim ta As New DataSet1TableAdapters.CompaniesTableAdapter
            Dim dt As New DataSet1.CompaniesDataTable

            'Use the TableAdapter to retrieve the data from the database and load the table

            'Fill the DataGridView
            Me.DataGridView1.DataSource = dt


    Author Comment

    I am confused on your syntax.  My dataset is named BiliProjectDataSet. It contains 2 tables, tblBabys and tblMom.  Since the dataset name is long, I want to declare a variable to hold it.  Do I need to use the New keyword?

    Dim Dset as BiliProjectDataSet
    Dim Dset as New BiliProjectDataSet

    Also do I need to fill the dataset or is it done through the wizard setup.  I think I am being confused by not knowing what is done by the wizard and what needs to be done manually.
    LVL 27

    Expert Comment

    Well, you actually have to fill each table individually.  Can you give me the name of one of the tables
    in your dataset?  And also, can you look at the table in the dataset designer and tell if it has a


    Author Comment

    The table names are

    There is attached on the bottom of each table in the designer, a grey header which says tblMomTableAdapter and tblBabysTableAdapter with an sql phrase below them which reads...
    I am assuming these are existing table adapters.  Although I can add new ones by double clicking the TableAdapter control in the toolbox.  This will add a free floating TableAdapter and start a configuration wizard.  I have not actually done that though.

    Here is what I have so far...

        Dim DSet As BiliProjectDataSet
        Dim TA1 As New BiliProjectDataSetTableAdapters.tblMomTableAdapter
        Dim TA2 As New BiliProjectDataSetTableAdapters.tblBabysTableAdapter
        Dim DT1 As New BiliProjectDataSet.tblMomDataTable
        Dim DT2 As New BiliProjectDataSet.tblBabysDataTable
    LVL 27

    Accepted Solution

    That's perfect.  Try it like this:

            'Declare your TableAdapter and Table from your strongly typed dataset.
            Dim TA1 As New BiliProjectDataSetTableAdapters.tblMomTableAdapter
            Dim DT1 As New BiliProjectDataSet.tblMomDataTable

            'Use the TableAdapter to retrieve the data from the database and load the table

            'Fill the DataGridView (if you have one)
            Me.DataGridView1.DataSource = DT1


    Author Comment

    Ok, I think I'm on my way.  I do not use a datgidview to display data.  I am using a form with text controls etc. On retrieval of data, is there a function built in that will allow me to search a field in for a match in my app? i.e. I want to retrieve the record that matches the text in the mrn textbox. mrn is the medical record number. Or do I have to iterate through the records and compare to find the corresponding record in the datbase?
    LVL 27

    Expert Comment

    If you want to search for a field, then use a DataView.  Every table has a Default DataView.  All you
    have to do is set it's Sort property to the column you want to Search, and then you can use either the Find method (if there will only be 1 return result), or you can use the FindRows or RowFilter Property
    (if there will be more than 1 row returned).

    'Here's an example of how you can use it (You may have to fix the field names)
    '    Note:  <TableName> and <ColumnName> need to be replaced with the actual names of
    '    your table and column
    Dim searchText as string = mrn.Text

    Dim dv as DataView = <TableName>.DefaultView
    dv.Sort = "<ColumnName> ASC"    'Can use either ASC for ascending or DESC for descending

    'The find method will return the index of the row that contains the searchText
    '    A -1 will be returned if it doesn't find anything
    Dim i as Integer = dv.Find(searchText)
    if i > -1 then
        'Row found:  access the row by using:  dv(i)
    end if

    'You can, alternatively, use the RowFilter Property like this:
    dv.RowFilter = "<ColumnName> LIKE '" & searchText & "%'"
        'Just a few examples of the RowFilter Property for you
        '    Pretending you have a column named "MRN" for Medical Record Number
        dv.RowFilter = "MRN="   & searchText     'Searches for an exact numberic match
        dv.RowFilter = "MRN='"   & searchText  & "'"    'Searches for an exact string match
        dv.RowFilter = "MRN LIKE '%"   & searchText  & "%'"    'Searches for a string with the searchText
               appears somewhere in the field

    You may have to play with it a bit, but that's how it works in a nutshell.

    I didn't give an example of FindRows, but I can if you need it, or you can find examples of how to use
    it in Help.


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Executing several SQL INSERT statement in ASP.NET VB.NET 5 37
    WS_CHILD window 14 36
    Help file 1 28
    I need C# converted to 1 36
    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now