Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Should I use a dataset or write directly to an access database?

I have an application that uses an access database.  It is windows and vs2005.  It will contain around 300 records with 2 tables.  Should I use a dataset  or read and write directly to the database?  I do not have a datagridview control bound to the database.  I use a form for the user to enter data and edit it.  

Thanks,
John
0
leachj
Asked:
leachj
  • 5
  • 3
  • 2
  • +1
1 Solution
 
DhaestCommented:
Do you read only 1 record at the time ? Then I would also thinking about loading the record, let the user edit it and update it into the database. (you can do it with a dataset-dataadapter or trhough sql-statements, all depending of your own knowledge)
0
 
leachjAuthor Commented:
For daily usage, only one record at a time will be edited.  However for reporting purposes, a series or all of the records would be read.  I have already implemented a dataset but I am unsure of how to update it so it would be reflected into the actual tables of the database.  I have used access vba extensively but this is the first vb2005 application to implement reading and writing a database.  I have been unable to find a clear cut example of how to use these tools.  Each example has some part but not from start to finish (connection,open,read,insert into fields, write and close).  
0
 
natlozCommented:
I always recommend using a dataset just for future scalability, I am a big fan of the Disconnected approach, less database locks etc...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
leachjAuthor Commented:
natloz,
Can you point me to good example of usage of the dataset?

0
 
natlozCommented:
I will show you an N-Tier approach with a GUI - Controller - DAL layers.

GUI
-----
 '***********************************************************************
    'Description:   To do when form loads
    '***********************************************************************
    Private Sub CtrlSubFrmDesignDocument_ElectricalZone_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dtpEHTComplete.Value = Now.Date
        LoadLists()
        'If this is in EDIT MODE then call info into form
        If txtHiddenID.Text <> "NOID" Then
            LoadData() 'Set up form with data for selection based on ID
        End If

        bolLoaded = True
    End Sub

    '***********************************************************************
    'Description:   Function to load all data onto form based on txtHiddenID
    '***********************************************************************
    Public Sub LoadData()
        Dim oController As New Controller
        Dim dsResults As DataSet

        Try
            'Retrieve dataset of login results from controller class
            dsResults = oController.GetElectricalZone(_strDBConn, CInt(txtHiddenID.Text))

            'See if a record was returned
            If dsResults.Tables("ElectricalZone").Rows.Count > 0 Then
                With dsResults.Tables("ElectricalZone").Rows(0)
                    txtDesignDocumentNumber.Text = SafeString(.Item("varDesignDocumentNumber"))

                    txtElectricalZoneDescription.Text = SafeString(.Item("txtNotes"))
                    If SafeString(.Item("dteEHTComplete")) <> "" Then
                        chkEHTComplete.Checked = True
                        dtpEHTComplete.Value = CDate(SafeString(.Item("dteEHTComplete")))
                        dtpEHTComplete.Enabled = True
                    End If
                    'new fields
                    chkTrackedByICMS.Checked = CBool(SafeString(.Item("bolTrackedByICMS")))
                    If SafeString(.Item("fkPartyID_DocumentSupplier")) <> "" Then
                        cboSupplier.SelectedValue = SafeString(.Item("fkPartyID_DocumentSupplier"))
                    End If
                    intSelectedSupplier = cboSupplier.SelectedValue
                    'txtSheetNumber.Text = SafeString(.Item("varSheetNumber"))
                    'txtCopies.Text = SafeString(.Item("intCopies"))
                    'chkAsBuiltRequired.Checked = CBool(SafeString(.Item("bolAsBuiltRequired")))

                End With
            End If

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        Finally
            'Clean up objects
            dsResults.Dispose()
        End Try

    End Sub


    '************************************************************
    'Description:   Loads the From combo box on the form
    '************************************************************
    Private Sub LoadLists()
        'Declare variables and objects      
        Dim oController As New Controller
        Dim dsResultSet As DataSet

        Try
            'Load the Modules/Systems
            dsResultSet = oController.GetPartyListComboBox(_strDBConn, _intGroupID, False)
            If dsResultSet.Tables("PartyList").Rows.Count > 0 Then
                With cboSupplier
                    .DataSource = dsResultSet.Tables("PartyList")
                    .DisplayMember = "varParty"
                    .ValueMember = "pkPartyID"
                End With
            End If
            cboSupplier.SelectedValue = intSelectedSupplier

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        Finally
            dsResultSet.Dispose() 'Clean up dataset objects
        End Try
    End Sub

    '*************************
    'Description:   User wants to add/update
    '*************************
    Public Sub Save()
        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor

        Dim bFail As Boolean = False
        Dim intResult As Integer = 0
        Dim oController As New Controller
        With CType(Me.ParentForm, FrmDesignDocument_DocumentControl)

            If .cboDesignDocumentType.SelectedValue = 0 Then
                MsgBox("You MUST specify a Design Document type.")
                .cboDesignDocumentType.Focus()
                bFail = True
            Else
                If txtDesignDocumentNumber.Text = "" Then
                    MsgBox("You MUST specify a Design Document Number.")
                    txtDesignDocumentNumber.Focus()
                    bFail = True
                Else
                    'Checking to see if the user changes the design document type whilst in 'Edit Mode'.  If they do
                        'if they dont, set the selected text in the combo box back to what it was before and leave.
                        'If strStoredDesignDocumentType <> cboDesignDocumentType.Text And txtHiddenID.Text <> "NOID" Then
                        '    If MsgBox("Are you sure you wish to change the Design Document Types?" & (Chr(13) & Chr(10)) & "From: " & (Chr(13) & Chr(10)) & strStoredDesignDocumentType & (Chr(13) & Chr(10)) & "To:" & (Chr(13) & Chr(10)) & cboDesignDocumentType.Text, MsgBoxStyle.YesNo) = MsgBoxResult.No Then
                        '        cboDesignDocumentType.Text = strStoredDesignDocumentType
                        '        cboDesignDocumentType.Focus()
                        '        bFail = True
                        '    Else
                        '        'If they want to change the design document type, store the
                        '        'new design document type to compare if they wish to change it again.
                        '        strStoredDesignDocumentType = cboDesignDocumentType.Text
                        '    End If
                        'End If

                End If
            End If

            Try
                If bFail = False Then
                    'If attempting to ADD, see if Electrical Zone Number already exists
                    If txtHiddenID.Text = "NOID" Then 'NOID means attempting to ADD
                        If CheckPermission("Add") Then
                            'Add the new record
                            intResult = oController.AddElectricalZone_DocumentControl(_strDBConn, _intGroupID, _intProjectID, txtDesignDocumentNumber.Text.Trim, txtElectricalZoneDescription.Text.Trim, chkEHTComplete.Checked, dtpEHTComplete.Value, chkTrackedByICMS.Checked, cboSupplier.SelectedValue)
                            'Put form in edit mode
                            txtHiddenID.Text = intResult.ToString
                            .txtHiddenID.Text = txtHiddenID.Text
                            With CType(Me.ParentForm, FrmDesignDocument_DocumentControl)
                                .cboDesignDocumentType.Enabled = False
                                .btnAddDesignDocumentType.Enabled = False
                                .btnEditDesignDocumentType.Enabled = False
                            End With
                            .btnClear.Visible = False
                            .lblEdit.Text = "Edit Mode"
                        End If
                    Else
                        If CheckPermission("Update") Then
                            'Update existing record
                            oController.UpdateElectricalZone_DocumentControl(_strDBConn, CInt(txtHiddenID.Text), _intGroupID, _intProjectID, txtDesignDocumentNumber.Text.Trim, txtElectricalZoneDescription.Text.Trim, chkEHTComplete.Checked, dtpEHTComplete.Value, chkTrackedByICMS.Checked, cboSupplier.SelectedValue)
                            'cboDesignDocumentType.SelectedValue = intSelectedDesignDocumentType     'after updating, change the selected value of combo box to what it was changed to during the edit.
                        End If
                    End If

                    'LoadRevisionGrid() 'Load the grid of revisions
                End If



            Catch ex As Exception
                MsgBox(ex.Message.ToString)
            End Try
        End With
        Me.Cursor = System.Windows.Forms.Cursors.Default
    End Sub
0
 
natlozCommented:
Controller
-----------
'********************************************
    'Description:   Gets a single Electrical Zone
    '********************************************
    Public Function GetElectricalZone(ByVal strDBConn As String, ByVal intDesignDocumentID As Integer) As DataSet
        Return ElectricalZone.GetElectricalZone(strDBConn, intDesignDocumentID)
    End Function

    '*****************************************
    'Description:   Adds a new Electrical Zone
    '*****************************************
    Public Function AddElectricalZone(ByVal strDBConn As String, ByVal intGroupID As Integer, ByVal intProjectID As Integer, ByVal strElectricalZone As String, ByVal strElectricalZoneDescription As String, ByVal bolEHTComplete As Boolean, ByVal dteEHTComplete As DateTime) As Integer
        Return ElectricalZone.AddElectricalZone(strDBConn, intGroupID, intProjectID, strElectricalZone, strElectricalZoneDescription, bolEHTComplete, dteEHTComplete)
    End Function

    '************************************************
    'Description:   Update an existing ElectricalZone
    '************************************************
    Public Sub UpdateElectricalZone(ByVal strDBConn As String, ByVal intDesignDocumentID As Integer, ByVal intGroupID As Integer, ByVal intProjectID As Integer, ByVal strElectricalZone As String, ByVal strElectricalZoneDescription As String, ByVal bolEHTComplete As Boolean, ByVal dteEHTComplete As DateTime)
        ElectricalZone.UpdateElectricalZone(strDBConn, intDesignDocumentID, intGroupID, intProjectID, strElectricalZone, strElectricalZoneDescription, bolEHTComplete, dteEHTComplete)
    End Sub
0
 
natlozCommented:
DAL
-----

'*************************************
    'Description:   Gets a single Electrical Zone
    '*************************************
    Public Shared Function GetElectricalZone(ByVal strDBConn As String, ByVal intDesignDocumentID As Integer) As DataSet

        Dim oConnection As New SqlConnection(strDBConn)

        Dim oCommand As New SqlCommand("spGetElectricalZone", oConnection)
        oCommand.CommandType = CommandType.StoredProcedure
        oCommand.Parameters.Add("@pkDesignDocumentID", SqlDbType.Int).Value = intDesignDocumentID

        Dim oAdapter As New SqlDataAdapter(oCommand)

        Dim dsResults As New DataSet
        oAdapter.Fill(dsResults, "ElectricalZone")

        Return dsResults

    End Function

    '*************************************
    'Description:   Adds a new Electrical Zone
    '*************************************
    Public Shared Function AddElectricalZone(ByVal strDBConn As String, ByVal intGroupID As Integer, ByVal intProjectID As Integer, ByVal strElectricalZone As String, ByVal strElectricalZoneDescription As String, ByVal bolEHTComplete As Boolean, ByVal dteEHTComplete As DateTime) As Integer

        Dim oConnection As New SqlConnection(strDBConn)
        Dim intResult As Integer

        Dim oCommand As New SqlCommand("spAddElectricalZone", oConnection)
        oCommand.CommandType = CommandType.StoredProcedure
        oCommand.Parameters.Add("@fkGroupID", SqlDbType.Int).Value = intGroupID
        oCommand.Parameters.Add("@fkProjectID", SqlDbType.Int).Value = intProjectID
        oCommand.Parameters.Add("@varElectricalZone", SqlDbType.VarChar).Value = strElectricalZone
        oCommand.Parameters.Add("@varElectricalZoneDescription", SqlDbType.VarChar).Value = strElectricalZoneDescription
        If bolEHTComplete = True Then
            oCommand.Parameters.Add("@dteEHTComplete", SqlDbType.DateTime).Value = dteEHTComplete
        End If

        oConnection.Open()
        Try
            intResult = CInt(oCommand.ExecuteScalar())
        Finally
            oConnection.Close()
        End Try

        Return intResult

    End Function

    '*************************************
    'Description:   Update an existing ElectricalZone
    '*************************************
    Public Shared Sub UpdateElectricalZone(ByVal strDBConn As String, ByVal intDesignDocumentID As Integer, ByVal intGroupID As Integer, ByVal intProjectID As Integer, ByVal strElectricalZone As String, ByVal strElectricalZoneDescription As String, ByVal bolEHTComplete As Boolean, ByVal dteEHTComplete As DateTime)

        Dim oConnection As New SqlConnection(strDBConn)

        Dim oCommand As New SqlCommand("spUpdateElectricalZone", oConnection)
        oCommand.CommandType = CommandType.StoredProcedure
        oCommand.Parameters.Add("@pkDesignDocumentID", SqlDbType.Int).Value = intDesignDocumentID
        oCommand.Parameters.Add("@fkGroupID", SqlDbType.Int).Value = intGroupID
        oCommand.Parameters.Add("@fkProjectID", SqlDbType.Int).Value = intProjectID
        oCommand.Parameters.Add("@varElectricalZone", SqlDbType.VarChar).Value = strElectricalZone
        oCommand.Parameters.Add("@varElectricalZoneDescription", SqlDbType.VarChar).Value = strElectricalZoneDescription
        If bolEHTComplete = True Then
            oCommand.Parameters.Add("@dteEHTComplete", SqlDbType.DateTime).Value = dteEHTComplete
        End If

        oConnection.Open()
        Try
            oCommand.ExecuteNonQuery()
        Finally
            oConnection.Close()
        End Try

    End Sub
0
 
natlozCommented:
So basically, GUI calls CONTROLLER which calls DAL and the DAL returns the records back through the CONTROLLER to the GUI where it is displayed. When they hit save, the same path is followed with more data flowing into the CONTROLLER/DAL.
0
 
VBRocksCommented:
Here's a very simple alternative approach to how you can do it:

    Private da As System.Data.OleDb.OleDbDataAdapter
    Private dt As New System.Data.DataTable()

    Private Sub FillDataSet()

        Dim connectionString As String = "your connection string"

        'Replace 'table' with the name of the Table in Access
        Dim SQL As String = "SELECT * FROM table"

        da = New System.Data.OleDb.OleDbDataAdapter(SQL, connectionString)
        da.Fill(dt)

        'Bind controls to datatable

    End Sub

    Private Sub UpdateDatabase()
        Dim db As New OleDb.OleDbCommandBuilder(da)
        da.Update(dt)

        MsgBox("Done")
    End Sub

0
 
leachjAuthor Commented:
Processing................
0
 
VBRocksCommented:
I'm still interested, and willing to try to help if I can.

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now