I have a form that is supposed to update tblMain in my database, and when I press btnSave it says in a messagebox, saved, but when I look at the table or try and recover the data from the dataset all I see is NULL values. Any help would be greatly appreciated. I've been trying to get this to work for WEEKS :((
Data connection to MS Access database ComputerManagement2008.mdb
DataSet dsCM2008.xsd
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Xml
Public Class frmMain
Inherits System.Windows.Forms.Form
Dim con As New OleDb.OleDbConnection
Dim da As New OleDb.OleDbDataAdapter("select * from tblMain", con)
Dim com As New OleDb.OleDbCommand()
Dim dsCM2008 As New DataSet
Dim inc As Integer
Dim max As Integer
' ConnectAdapter for database connection
Public Sub ConnectionAdaptor()
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ComputerManagement2008.mdb"
con.Open()
da.Fill(dsCM2008, "tblMain")
'Refreshed DataSet Filling
da.Fill(dsCM2008, "tblMain")
con.Close()
inc = 0
max = dsCM2008.Tables("tblMain").Rows.Count - 1
End Sub
' navigate records in recordset
Private Sub NavigateRecords()
'Refreshed DataSet Filling
Dim dsCM2008 As New DataSet
Dim MyDA As New dsCM2008TableAdapters.tblMainTableAdapter
Dim MyTable As New dsCM2008.tblMainDataTable
da.Fill(dsCM2008, "tblMain")
Dim MyRow As DataRow = MyTable.NewRow()
With MyRow
.Item(0) = Me.txtCompany.Text
.Item(1) = Me.txtMailingAddress.Text
.Item(2) = Me.txtCity.Text
.Item(3) = Me.txtStateProvince.Text
.Item(4) = Me.txtZipPostal.Text
.Item(5) = Me.txtLocations.Text
.Item(6) = Me.mtxtTelephone.Text
.Item(7) = Me.mtxtFacsimile.Text
.Item(8) = Me.mtxtTollFree.Text
.Item(9) = Me.mtxtCellPhone.Text
.Item(10) = Me.mtxtFEIN.Text
.Item(11) = Me.mtxtSSN.Text
.Item(12) = Me.mtxtSIN.Text
.Item(13) = Me.txtNotes.Text
.Item(14) = Me.txtCompanyID.Text
End With
End Sub
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim dsCM2008 As New DataSet
' display the database full path
lblTSStatus.Text = AppPath()
Call ConnectionAdaptor()
If txtCompany.Text = String.Empty Then
da.Fill(dsCM2008, "tblMain")
Else
' Fill recordset with company data
da.Fill(dsCM2008, "tblMain")
' begin disable fields for editing
Me.txtCompany.Enabled = False
Me.txtMailingAddress.Enabled = False
Me.txtCity.Enabled = False
Me.txtStateProvince.Enabled = False
Me.txtZipPostal.Enabled = False
Me.mtxtTelephone.Enabled = False
Me.mtxtFacsimile.Enabled = False
Me.mtxtTollFree.Enabled = False
Me.mtxtCellPhone.Enabled = False
Me.mtxtFEIN.Enabled = False
Me.mtxtSSN.Enabled = False
Me.mtxtSIN.Enabled = False
Me.txtLocations.Enabled = True
Me.txtNotes.Enabled = True
' end disable fields for editing
End If
End Sub
Private Sub chkCanada_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkCanada.CheckedChanged
' change the SSN from USA to SIN in Canada
If chkCanada.Checked = True Then
mtxtSSN.Visible = False
lblSSN.Text = "SIN"
mtxtSIN.Visible = True
Else
mtxtSSN.Visible = True
lblSSN.Text = "SSN"
mtxtSIN.Visible = False
End If
End Sub
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
' begin enable fields for editing
Me.txtCompany.ReadOnly = True
Me.txtMailingAddress.ReadOnly = False
Me.txtCity.ReadOnly = False
Me.txtStateProvince.ReadOnly = False
Me.txtZipPostal.ReadOnly = False
Me.mtxtTelephone.ReadOnly = False
Me.mtxtFacsimile.ReadOnly = False
Me.mtxtTollFree.ReadOnly = False
Me.mtxtCellPhone.ReadOnly = False
Me.mtxtFEIN.ReadOnly = False
Me.mtxtSSN.ReadOnly = False
Me.mtxtSIN.ReadOnly = False
Me.txtLocations.ReadOnly = False
Me.txtNotes.ReadOnly = False
Me.txtCompanyID.ReadOnly = True
' end enable fields for editing
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Call ConnectionAdaptor()
If (Me.txtCompanyID.Text = String.Empty = True) Then
txtCompanyID.Text = "1"
'com.Connection = con
Dim MyDA As New dsCM2008TableAdapters.tblMainTableAdapter
Dim MyTable As New dsCM2008.tblMainDataTable
'com.CommandText = "Update tblMain Set Company='" + Me.txtCompany.Text + "', MailingAddress='" + Me.txtMailingAddress.Text + "',City='" + Me.cboCity.Text + "',Zipcode='" + Me.cboZipPostal.Text + "',Telephone ='" + Me.mtxtTelephone.Text + "',Facsimile ='" + Me.mtxtFacsimile.Text + "',TollFree='" + Me.mtxtTollFree.Text + "',CellPhone='" + Me.mtxtCellPhone.Text + "',SSN='" + Me.mtxtSSN.Text + "',SIN='" + Me.mtxtSIN.Text + "',FEIN='" + Me.mtxtFEIN.Text + "' where CompanyID=" + Me.txtCompanyID.Text
'da.UpdateCommand = com
Dim MyRow As DataRow = MyTable.NewRow() 'ds.Tables("tblMain").NewRow()
With MyRow
.Item(0) = Me.txtCompany.Text
.Item(1) = Me.txtMailingAddress.Text
.Item(2) = Me.txtCity.Text
.Item(3) = Me.txtStateProvince.Text
.Item(4) = Me.txtZipPostal.Text
.Item(5) = Me.txtLocations.Text
.Item(6) = Me.mtxtTelephone.Text
.Item(7) = Me.mtxtFacsimile.Text
.Item(8) = Me.mtxtTollFree.Text
.Item(9) = Me.mtxtCellPhone.Text
.Item(10) = Me.mtxtFEIN.Text
.Item(11) = Me.mtxtSSN.Text
.Item(12) = Me.mtxtSIN.Text
.Item(13) = Me.txtNotes.Text
.Item(14) = Me.txtCompanyID.Text
End With
MyTable.Rows.Add(MyRow)
MyDA.Update(MyTable)
MsgBox("Main Information Saved")
Call ConnectionAdaptor()
Call NavigateRecords()
Me.txtCompany.ReadOnly = True
Me.txtMailingAddress.ReadOnly = True
Me.txtCity.ReadOnly = True
Me.txtStateProvince.ReadOnly = True
Me.txtZipPostal.ReadOnly = True
Me.mtxtTelephone.ReadOnly = True
Me.mtxtFacsimile.ReadOnly = True
Me.mtxtTollFree.ReadOnly = True
Me.mtxtCellPhone.ReadOnly = True
Me.mtxtFEIN.ReadOnly = True
Me.mtxtSSN.ReadOnly = True
Me.mtxtSIN.ReadOnly = True
Me.txtLocations.Enabled = True
Me.txtNotes.Enabled = True
Me.txtCompanyID.ReadOnly = True
ElseIf (txtCompany.Text) = String.Empty = False Then
Call NavigateRecords()
MsgBox("Main Information Updated")
Call ConnectionAdaptor()
Call NavigateRecords()
Me.txtCompany.ReadOnly = True
Me.txtMailingAddress.ReadOnly = True
Me.txtCity.ReadOnly = True
Me.txtStateProvince.ReadOnly = True
Me.txtZipPostal.ReadOnly = True
Me.mtxtTelephone.ReadOnly = True
Me.mtxtFacsimile.ReadOnly = True
Me.mtxtTollFree.ReadOnly = True
Me.mtxtCellPhone.ReadOnly = True
Me.mtxtFEIN.ReadOnly = True
Me.mtxtSSN.ReadOnly = True
Me.mtxtSIN.ReadOnly = True
Me.txtLocations.Enabled = True
Me.txtNotes.Enabled = True
Me.txtCompanyID.ReadOnly = True
End If
End Sub
' button to populate textboxes and comboboxes
Private Sub btnPopulate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPopulate.Click
txtCompany.Text = "Your Company, LLC"
txtMailingAddress.Text = "P.O. Box 000" & vbCrLf & "0000 Main Street" & _
vbCrLf & "Anytown, AA 00000"
txtCity.Text = "Anytown"
txtStateProvince.Text = "AA"
txtZipPostal.Text = "00000"
mtxtTelephone.Text = "0000000000"
mtxtFacsimile.Text = "0000000000"
mtxtTollFree.Text = "8005551212"
mtxtCellPhone.Text = "0000000000"
mtxtFEIN.Text = "999999999"
mtxtSSN.Text = "123456789"
mtxtSIN.Text = "234567890"
txtLocations.Text = "Anytown, USA" & vbCrLf & "Everytown, USA"
txtNotes.Text = "this is the notes section"
End Sub
End Class