How to save a datagrid back to database

Ilfirin

New member
Joined
Jul 3, 2007
Messages
3
Programming Experience
3-5
Ok, I'm writing a program in vb.net 2003, and connecting to an access 2000 database using oledb. I've got a datagrid , and i'm trying to figure out how to save any changes the user makes in the datagrid back to the database. I've messed around with the command builders and stuff, but nothing I do seems to be working. Could somebody please help me out? Here's the relevant bits of code in case i've missed any important details:

database class:


VB.NET:
Imports System.Data.OleDb
Public Class clsDB

    Private connectionString As String
    Private conn As OleDbConnection
    Dim cmd As OleDbDataAdapter
    Dim objCommandBuilder As New OleDbCommandBuilder(cmd)

    Public Sub New(ByVal strConnect As String)
        'Takes File path location of database

        connectionString = "Provider=Microsoft.Jet.OlEDB.4.0; Data Source=" & strConnect

    End Sub

    Public Function saveDataGrid(ByVal sql As String, ByVal ds As DataSet)

    End Function
    Public Function getDataSet(ByVal sql As String) As DataSet
        ' Takes the SQL statement and returns a DataSet

        Try
            Dim ds As DataSet

            conn = New OleDbConnection(connectionString)
            conn.Open()

            cmd = New OleDbDataAdapter(sql, conn)

            ds = New DataSet
            cmd.Fill(ds)

            Return ds
        Catch ex As Exception
            MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
                MsgBoxStyle.Critical, "Database Error getDataSet")
        End Try

    End Function

    Public Function UpdateDataSet(ByRef ds As DataSet)
        Try
            cmd.Update(ds, "tblCategory")
        Catch ex As Exception
            MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
                MsgBoxStyle.Critical, "Database Error UpdateDataSet")
        End Try
    End Function
    Public Function Execute(ByVal sql As String) As Boolean
        ' Executes a Non Query Such as an Update

        Try
            conn = New OleDbConnection(connectionString)
            conn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)


            cmd.ExecuteNonQuery()

            cmd.Dispose()
            conn.Close()
            conn = Nothing
            Return True
        Catch ex As Exception
            MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
               MsgBoxStyle.Critical, "Database Error Execute")
            Return False
        End Try

    End Function

    Public Function getDataReader(ByVal sql As String) As Object
        ' Takes the SQL statement and returns a DataReader

        Try
            conn = New OleDbConnection(connectionString)
            conn.Open()

            Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
            Dim dr As OleDbDataReader

            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            cmd.Dispose()
            cmd = Nothing

            Return dr
        Catch ex As Exception
            MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace & _
                 vbCrLf & "Target Site: " & ex.TargetSite.Name & vbCrLf & "Source: " & ex.Source.ToString, _
                MsgBoxStyle.Critical, "Database Error getDataReader")
        End Try

    End Function
    Public Function getRecordset(ByVal sql As String) As Object
        'Must close connection after closing recordset

        Try
            conn = New OleDbConnection(connectionString)
            conn.Open()

            Dim rs As ADODB.Recordset = New ADODB.Recordset

            rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
            Return rs
        Catch ex As Exception
            MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
               MsgBoxStyle.Critical, "Database Error getRecordset")
        End Try
    End Function
    Public Function close()
        ' Destroys the connection if it still exists

        If Not conn Is Nothing Then
            conn.Close()
            conn = Nothing
        End If

    End Function
End Class


and the code for the page:


VB.NET:
Public Class frmCheckRegister
    Inherits System.Windows.Forms.Form

## Windows Form Designer generated code ##

    
    Dim uid As Integer = 1
    Dim dr As OleDb.OleDbDataReader
    Dim db As clsDB
    Dim dsMonthlyTransactions As DataSet
    Dim dsCheckRegister As DataSet
    Dim dsAccountName As DataSet
    Dim dsOtherAccountBalance As DataSet
    Dim sql As String
    Dim intLastRow As Integer
    Dim intBalance As Integer
    Dim intTransactionNumber As Integer
    Dim strAccountName As String

    Private Sub frmCheckRegister_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        db = New clsDB("DB\Capstone.mdb")

        ' Populate lstAccounts
        sql = "SELECT AccountName FROM tblAccounts WHERE UID=" & uid & " AND AccountType = 'Checking'"
        dr = db.getDataReader(sql)
        While dr.Read
            lstAccounts.Items.Add(dr("AccountName"))
        End While
        dr.Close()
        dr = Nothing

        ' Populate lstAllAccounts
        sql = "SELECT AccountName FROM tblAccounts WHERE UID=" & uid
        dr = db.getDataReader(sql)
        While dr.Read
            lstOtherAccounts.Items.Add(dr("AccountName"))
        End While
        dr.Close()
        dr = Nothing

        displayCheckRegister()
    End Sub

    Public Function displayCheckRegister()
        ' Populate dgCheckRegister
        sql = "SELECT  tblTransactions.AccountNumber, tblTransactions.TransactionType, tblTransactions.TransactionAmount, tblTransactions.TransactionDate, tblTransactions.Balance, tblTransactions.Description, tblTransactions.IsCleared, tblTransactions.Category FROM tblTransactions INNER JOIN tblAccounts ON tblTransactions.AccountNumber = tblAccounts.AccountNumber WHERE (((tblAccounts.UID)=" & uid & ") AND ((tblAccounts.AccountType)='Checking'));"

        dsCheckRegister = db.getDataSet(sql)
        dgCheckRegister.DataSource = dsCheckRegister.Tables(0)
        dsCheckRegister.EnforceConstraints = True

        ' Load the balance
        intLastRow = dsCheckRegister.Tables(0).Rows.Count
        intBalance = dsCheckRegister.Tables(0).Rows(intLastRow - 1).Item("Balance")
        lblTotalBalance.Text = intBalance

        ' Populate dgMonthlyTransactions
        sql = "SELECT tblMonthlyTransactions.TransactionName, tblMonthlyTransactions.TransactionAmount, tblMonthlyTransactions.TransactionType FROM tblMonthlyTransactions"

        dsMonthlyTransactions = db.getDataSet(sql)
        dgMonthlyTransactions.DataSource = dsMonthlyTransactions.Tables(0)

    End Function

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        ' Save the datagrid to the database
    End Sub

    Private Sub lstOtherAccounts_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstOtherAccounts.SelectedValueChanged
        strAccountName = lstOtherAccounts.SelectedItem()
    End Sub

    Private Sub btnTransfer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTransfer.Click
        Dim intTransferAmount As Integer
        Dim intCurrentAccount As Integer
        Dim intOtherAccount As Integer
        Dim intOtherBalance As Integer
        If IsNumeric(txtAmountToTransfer.Text) = False Then
            MsgBox("Please enter a numeric monetary amount to transfer.", MsgBoxStyle.Exclamation, "Error")
        Else
            If txtAmountToTransfer.Text <= 0 Then
                MsgBox("Please enter a positive amount to transfer.", MsgBoxStyle.Exclamation, "Error")
            Else
                ' Get the amount to transfer
                intTransferAmount = txtAmountToTransfer.Text

                ' Get the last TransactionNumber so it can be auto-numbered through the program
                intLastRow = dsCheckRegister.Tables(0).Rows.Count
                intBalance = dsCheckRegister.Tables(0).Rows(intLastRow - 1).Item("Balance")
                intTransactionNumber = dsCheckRegister.Tables(0).Rows(intLastRow - 1).Item("TransactionNumber")

                ' Get this account number for the transfer
                intCurrentAccount = dgCheckRegister.Item(0, 0)

                ' Add the transaction into the current record
                sql = "INSERT INTO tblTransactions"
                sql = sql + "( AccountNumber, TransactionType, TransactionAmount, TransactionDate, Balance, Description, IsCleared, Category)"
                sql = sql + "VALUES"
                sql = sql + "(" & intCurrentAccount & ",'Debit(-)'," & intTransferAmount & ",#" & Now() & "#," & intBalance - intTransferAmount & ",'" & txtTransferReason.Text & "',true,'Transfer')"
                db.Execute(sql)
                displayCheckRegister()

                ' Get the other account number
                sql = "SELECT * FROM tblAccounts WHERE AccountName='" & strAccountName & "'"
                dsAccountName = db.getDataSet(sql)
                intOtherAccount = dsAccountName.Tables(0).Rows(0).Item("AccountNumber")

                ' Get the balance from the other account so that it can be properly updated.
                sql = "SELECT * from tblTransactions WHERE AccountNumber=" & intOtherAccount & ""
                dsOtherAccountBalance = db.getDataSet(sql)
                intLastRow = dsOtherAccountBalance.Tables(0).Rows.Count
                intOtherBalance = dsOtherAccountBalance.Tables(0).Rows(intLastRow - 1).Item("Balance")

                ' Add a transfer record to the other account
                sql = "INSERT INTO tblTransactions"
                sql = sql + "(AccountNumber, TransactionType, TransactionAmount, TransactionDate, Balance, Description, IsCleared, Category)"
                sql = sql + "VALUES"
                sql = sql + "(" & intOtherAccount & " ,'Credit(+)'," & intTransferAmount & ",#" & Now() & "#," & intOtherBalance + intTransferAmount & ",'" & txtTransferReason.Text & "',true,'Transfer')"
                db.Execute(sql)
            End If
        End If
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Hide()
        'Me.Owner.Show()
    End Sub

    Private Sub frmCheckRegister_Closed(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Closed
        Me.Hide()
        'Me.Owner.Show()
    End Sub


End Class
 
Top Bottom