hi there, im a beginner really and have just tried to finish a new computer based booking system for my work, im using sql and a database to store the customers and then store bookings too, but im having problems with the customer form, when i run it it kind of works until i click commit changes, when it throws an error A first chance exception of type 'System.NullReferenceException' occurred in LM_NewWork.exe
can you guys look at the code, and give suggestions to how to fix it and all to get it running, thanks, tell me where i have gone wrong with suggestions. feel free to edit the code.
Imports System.Data
Public Class Customers
Dim inc As Integer
Dim MaxavaliableRows As Integer
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Private Sub Qoutes_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = c:\LM_NewWork\DA_File.mdb"
con.Open()
sql = "SELECT * FROM tblCustomers"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "tblCustomers")
txtcustomerid.Text = ds.Tables("tblCustomers").Rows(0).Item(1)
txtforename.Text = ds.Tables("tblCustomers").Rows(0).Item(2)
txtsurname.Text = ds.Tables("tblCustomers").Rows(0).Item(3)
txtgroupname.Text = ds.Tables("tblCustomers").Rows(0).Item(4)
txtdob.Text = ds.Tables("tblCustomers").Rows(0).Item(5)
txtaddress.Text = ds.Tables("tblCustomers").Rows(0).Item(6)
txttown.Text = ds.Tables("tblCustomers").Rows(0).Item(7)
txtcounty.Text = ds.Tables("tblCustomers").Rows(0).Item(8)
txtpostcode.Text = ds.Tables("tblCustomers").Rows(0).Item(9)
txttelephone.Text = ds.Tables("tblCustomers").Rows(0).Item(10)
con.Close()
MaxavaliableRows = ds.Tables("tblCustomers").Rows.Count
inc = -1
End Sub
Private Sub NavigateRecords()
txtcustomerid.Text = ds.Tables("tblCustomers").Rows(inc).Item(1)
txtforename.Text = ds.Tables("tblCustomers").Rows(inc).Item(2)
txtsurname.Text = ds.Tables("tblCustomers").Rows(inc).Item(3)
txtgroupname.Text = ds.Tables("tblCustomers").Rows(inc).Item(4)
txtdob.Text = ds.Tables("tblCustomers").Rows(inc).Item(5)
txtaddress.Text = ds.Tables("tblCustomers").Rows(inc).Item(6)
txttown.Text = ds.Tables("tblCustomers").Rows(inc).Item(7)
txtcounty.Text = ds.Tables("tblCustomers").Rows(inc).Item(8)
txtpostcode.Text = ds.Tables("tblCustomers").Rows(inc).Item(9)
txttelephone.Text = ds.Tables("tblCustomers").Rows(inc).Item(10)
End Sub
Private Sub btnexitcustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexitcustomer.Click
Me.Hide()
Mainmenu.Show()
End Sub
Private Sub btnnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnext.Click
If inc <> MaxavaliableRows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MsgBox("No More Rows")
End If
End Sub
Private Sub btnprevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprevious.Click
If inc > 0 Then
inc = inc - 1
NavigateRecords()
ElseIf inc = -1 Then
MsgBox("No Records Yet")
ElseIf inc = 0 Then
MsgBox("First Record")
End If
End Sub
Private Sub btnlast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlast.Click
If inc <> MaxavaliableRows - 1 Then
inc = MaxavaliableRows - 1
NavigateRecords()
End If
End Sub
Private Sub btnfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfirst.Click
If inc <> 0 Then
inc = 0
NavigateRecords()
End If
End Sub
Private Sub btneditcustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btneditcustomer.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("tblCustomers").Rows(inc).Item(1) = txtcustomerid.Text
ds.Tables("tblCustomers").Rows(inc).Item(2) = txtforename.Text
ds.Tables("tblCustomers").Rows(inc).Item(3) = txtsurname.Text
ds.Tables("tblCustomers").Rows(inc).Item(4) = txtgroupname.Text
ds.Tables("tblCustomers").Rows(inc).Item(5) = txtdob.Text
ds.Tables("tblCustomers").Rows(inc).Item(6) = txtaddress.Text
ds.Tables("tblCustomers").Rows(inc).Item(7) = txttown.Text
ds.Tables("tblCustomers").Rows(inc).Item(8) = txtcounty.Text
ds.Tables("tblCustomers").Rows(inc).Item(9) = txtpostcode.Text
ds.Tables("tblCustomers").Rows(inc).Item(10) = txttelephone.Text
da.Update(ds, "tblCustomers")
MsgBox("Data updated")
End Sub
Private Sub btnsavecustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsavecustomer.Click
btncommit.Enabled = True
btnsavecustomer.Enabled = False
btneditcustomer.Enabled = False
btndeletecustomer.Enabled = False
txtcustomerid.Clear()
txtforename.Clear()
txtsurname.Clear()
txtgroupname.Clear()
txtdob.Clear()
txtaddress.Clear()
txttown.Clear()
txtcounty.Clear()
txtpostcode.Clear()
txttelephone.Clear()
End Sub
Private Sub btncommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncommit.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
If inc <> -1 Then
dsNewRow = ds.Tables("tblCustomers").NewRow()
dsNewRow.Item("Customer ID") = txtcustomerid.Text
dsNewRow.Item("Forename") = txtforename.Text
dsNewRow.Item("Surname") = txtsurname.Text
dsNewRow.Item("Group Name") = txtgroupname.Text
dsNewRow.Item("Date Of Birth") = txtdob.Text
dsNewRow.Item("Address") = txtaddress.Text
dsNewRow.Item("Town") = txttown.Text
dsNewRow.Item("County") = txtcounty.Text
dsNewRow.Item("Postcode") = txtpostcode.Text
dsNewRow.Item("Telephone") = txttelephone.Text
ds.Tables("tblCustomers").Rows.Add(dsNewRow)
da.Update(ds, "tblCustomers")
MsgBox("New Record added to the Database")
btncommit.Enabled = False
btnsavecustomer.Enabled = True
btneditcustomer.Enabled = True
btndeletecustomer.Enabled = True
End If
End Sub
Private Sub btndeletecustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndeletecustomer.Click
If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo,MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
End If
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("tblCustomers").Rows(inc).Delete()
MaxavaliableRows = MaxavaliableRows - 1
inc = 0
NavigateRecords()
da.Update(ds, "tblCustomers")
End Sub
End Class