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:
and the code for the page:
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 Classand 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
