Unable to save record to table

  • Thread starter Thread starter gwboolean
  • Start date Start date
G

gwboolean

Guest
Method one



I have been using the method below to add a record to a table. In the process here, an existing record is loaded into the Form controls and displayed in the Load Event. The user then executes the AddButton_Click event to display a blank record. The user then inputs information into the form controls and then executes the SaveButton_Click even to save the new record.

Private Sub frmAuthorList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
'point to help file
hlpAuthors.HelpNamespace = Application.StartupPath + "\Authors.chm"
'connect to books database
BooksConnection = New SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=F:\TextSourceCode2\VBDB\ProjectDatabases\SQLBooksDB.mdf; Integrated Security=True; Connect Timeout=30; User Instance=True")
BooksConnection.Open()
'establish command object
AuthorsCommand = New SqlCommand("Select * from Authors ORDER BY Author", BooksConnection)
'establish data adapter/data table
AuthorsAdapter = New SqlDataAdapter()
AuthorsAdapter.SelectCommand = AuthorsCommand
AuthorsTable = New DataTable()
AuthorsAdapter.Fill(AuthorsTable)
'bind controls to data table
txtAuthorID.DataBindings.Add("Text", AuthorsTable, "Au_ID")
txtAuthorName.DataBindings.Add("Text", AuthorsTable, "Author")
txtYearBorn.DataBindings.Add("Text", AuthorsTable, "Year_Born")
'establish currency manager
AuthorsManager = DirectCast(Me.BindingContext(AuthorsTable), CurrencyManager)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error establishing Authors table.", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
Me.Show()
Call SetState("View")
Call SetText()
End Sub

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
If Not (ValidateData()) Then Exit Sub
Dim SavedName As String = txtAuthorName.Text
Dim SavedRow As Integer
Try
AuthorsManager.EndCurrentEdit()
AuthorsTable.DefaultView.Sort = "Author"
SavedRow = AuthorsTable.DefaultView.Find(SavedName)
AuthorsManager.Position = SavedRow
MessageBox.Show("Record saved. ", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
Call SetState("View")
Catch ex As Exception
MessageBox.Show("Error saving Record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Call SetText()
End Sub

Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
Try
MyBookmark = AuthorsManager.Position
AuthorsManager.AddNew()
Call SetState("Add")
Catch ex As Exception
MessageBox.Show("Error adding record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Call SetText()
End Sub


So, what I wanted to do was to just begin with a new record. My intent was to first create a new record, Populate some of the controls on the form, then populate the new record from the form controls and save it. Everything executes, with no exception, but no record is created. I just am unable to see what I am doing wrong.

Private Sub frmChangeRequest_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'Point to Health File
hlpChangeRequest.HelpNamespace = Application.StartupPath + "\books.chm" 'Need to create specific for this application.

_strTable = "tblChangeMaster"
_MyState = "New"
'Define connection
Dim strConnection As String = "Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=aspnet-MasterBase3.0;Integrated Security=SSPI;AttachDBFilename=F:\SiTechMasterBase1.2\DataBase\MasterBase\MasterBase3.0.mdf"
'Set Form formats and properties
Try
'Connect to database
_MasterBaseConnection = New SqlConnection(strConnection)
_MasterBaseConnection.Open()
'Connection query
'MyQuery = CStr(qryChangeRequestByChangeID(MyQuery))
'Set Command object
_MySQLCommand = New SqlCommand("SELECT * FROM tblChangeMaster WHERE chrChangeID = " & "'" & _strChangeID & "'", _MasterBaseConnection)
'Set data adapter/table
_MySQLAdapter = New SqlDataAdapter
_MySQLAdapter.SelectCommand = _MySQLCommand
_MySQLtable = New DataTable()
_MySQLAdapter.Fill(_MySQLtable)
_MasterBaseClose = True
'Establishe currency Manager
_MySQLManager = DirectCast(Me.BindingContext(_MySQLtable), CurrencyManager)
'Add new row
_MyBookMark = _MySQLManager.Position
_MySQLManager.AddNew()
'Load Data to table
With Me
.lblChangeID.DataBindings.Add("Text", _MySQLtable, "chrChangeID")
.lblProcessID.DataBindings.Add("Text", _MySQLtable, "chrProcessID")
.txtTitle.DataBindings.Add("Text", _MySQLtable, "chrTitle")
.txtRevision.DataBindings.Add("Text", _MySQLtable, "chrRevision")
.txtProcess.DataBindings.Add("Text", _MySQLtable, "chrChangeObject")
.cmbManager.DataBindings.Add("Text", _MySQLtable, "chrManager")
.cmbOwner.DataBindings.Add("Text", _MySQLtable, "chrOwner")
.cmbWhere.DataBindings.Add("Text", _MySQLtable, "chrWhere")
.chkQualify.DataBindings.Add("Text", _MySQLtable, "blnQualify")
.txtChanges.DataBindings.Add("Text", _MySQLtable, "chrChangeMade")
.txtReasons.DataBindings.Add("Text", _MySQLtable, "chrChangeReason")
.txtResults.DataBindings.Add("Text", _MySQLtable, "chrChangeResult")
.txtOpen.DataBindings.Add("Text", _MySQLtable, "dteOpen")
.txtSubmit.DataBindings.Add("Text", _MySQLtable, "dteSubmit")
.txtApprove.DataBindings.Add("Text", _MySQLtable, "dteApprove")
.txtTrain.DataBindings.Add("Text", _MySQLtable, "dteTrain")
.txtEffective.DataBindings.Add("Text", _MySQLtable, "dteEffective")
.txtClose.DataBindings.Add("Text", _MySQLtable, "dteClose")
End With
Catch SQLExceptionErr As SqlException
MessageBox.Show(SQLExceptionErr.Message, "Access Error. General exception.")
Exit Sub
Catch InvalidOperationExceptionErr As InvalidOperationException
MessageBox.Show(InvalidOperationExceptionErr.Message, "Access Error. Invalid Operaion.")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "Error in Processing SQL", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
Me.Show()
Dim setForm As New nspMasterBase.ChangeRequest
Call setForm.DocumentColorPallete() 'Set form colors
'Populate form
'Assign Change ID
_strChangeID = "CR1000005"
'Assign Process ID
_strProcessID = "PID100000005"
'Assign Title
_strTitle = "Process Guide"
'Assign Revision
_strRevision = "AB"
'Sets format properties for form
With Me
.BackColor = CType(_objFormBackColor, Color)
.ForeColor = CType(_objFormForeColor, Color)
.Font = CType(_objFontDefault, Font)
.StartPosition = FormStartPosition.CenterScreen
.btnProcedure.Enabled = True
.btnReport.Enabled = True
.btnFile.Enabled = True
.btnPrint.Enabled = True
.btnScan.Enabled = True
.btnProcess.Enabled = True
.btnApprove.Enabled = True
.btnTrain.Enabled = True
.btnCancel.Enabled = True
.btnObsolete.Enabled = True
.btnExit.Enabled = True
.btnHelp.Enabled = True
.lblChangeID.BackColor = CType(_objDisableControlColor, Color)
.lblProcessID.BackColor = CType(_objDisableControlColor, Color)
.txtTitle.ForeColor = CType(_objTextForeColor, Color)
.txtTitle.BackColor = CType(_objTextBackColor, Color)
.txtTitle.ReadOnly = False
.txtTitle.TabStop = True
.txtTitle.TabIndex = 0
.txtTitle.Focus()
.txtRevision.ForeColor = CType(_objTextForeColor, Color)
.txtRevision.BackColor = CType(_objDisableControlColor, Color)
.txtRevision.ReadOnly = True
.txtRevision.TabStop = False
.txtProcess.ForeColor = CType(_objTextForeColor, Color)
.txtProcess.BackColor = CType(_objTextBackColor, Color)
.txtProcess.ReadOnly = False
.txtProcess.TabStop = False
.txtProcess.TabIndex = 1
.cmbManager.ForeColor = CType(_objTextForeColor, Color)
.cmbManager.BackColor = CType(_objTextBackColor, Color)
.cmbManager.Enabled = True
.cmbManager.TabStop = False
.cmbOwner.ForeColor = CType(_objTextForeColor, Color)
.cmbOwner.BackColor = CType(_objTextBackColor, Color)
.cmbOwner.Enabled = True
.cmbOwner.TabStop = False
.cmbWhere.ForeColor = CType(_objTextForeColor, Color)
.cmbWhere.BackColor = CType(_objTextBackColor, Color)
.cmbWhere.Enabled = True
.cmbWhere.TabStop = False
.chkQualify.Enabled = True
.chkQualify.TabStop = False
.txtChanges.ForeColor = CType(_objTextForeColor, Color)
.txtChanges.BackColor = CType(_objTextBackColor, Color)
.txtChanges.ReadOnly = False
.txtChanges.TabStop = True
.txtChanges.TabIndex = 2
.txtReasons.ForeColor = CType(_objTextForeColor, Color)
.txtReasons.BackColor = CType(_objTextBackColor, Color)
.txtReasons.ReadOnly = False
.txtReasons.TabStop = True
.txtReasons.TabIndex = 3
.txtResults.ForeColor = CType(_objTextForeColor, Color)
.txtResults.BackColor = CType(_objTextBackColor, Color)
.txtResults.ReadOnly = False
.txtResults.TabStop = True
.txtResults.TabIndex = 4
.txtOpen.ForeColor = CType(_objTextForeColor, Color)
.txtOpen.BackColor = CType(_objDisableControlColor, Color)
.txtOpen.ReadOnly = True
.txtOpen.TabStop = False
.txtSubmit.ForeColor = CType(_objTextForeColor, Color)
.txtSubmit.BackColor = CType(_objDisableControlColor, Color)
.txtSubmit.ReadOnly = True
.txtSubmit.TabStop = False
.txtApprove.ForeColor = CType(_objTextForeColor, Color)
.txtApprove.BackColor = CType(_objDisableControlColor, Color)
.txtApprove.ReadOnly = True
.txtApprove.TabStop = False
.txtTrain.ForeColor = CType(_objTextForeColor, Color)
.txtTrain.BackColor = CType(_objDisableControlColor, Color)
.txtTrain.ReadOnly = True
.txtTrain.TabStop = False
.txtEffective.ForeColor = CType(_objTextForeColor, Color)
.txtEffective.BackColor = CType(_objDisableControlColor, Color)
.txtEffective.ReadOnly = True
.txtEffective.TabStop = False
.txtClose.ForeColor = CType(_objTextForeColor, Color)
.txtClose.BackColor = CType(_objDisableControlColor, Color)
.txtClose.ReadOnly = True
.txtClose.TabStop = False
End With
'Load form Controls with preset data
With Me
.lblChangeID.Text = _strChangeID
.lblProcessID.Text = _strProcessID
.txtTitle.Text = _strTitle
.txtRevision.Text = _strRevision
.cmbManager.SelectedIndex = -1
.cmbOwner.SelectedIndex = -1
.cmbWhere.SelectedIndex = -1
.chkQualify.Checked = False
.txtOpen.Text = CStr(DateTime.Today)
End With
End Sub

Select Case _MyState
Case "Edit"
If Not (ValidateData.ValidateInput()) Then Exit Sub
Try
Dim txtChangeRequest(18) As String
'Update record
txtChangeRequest(1) = lblChangeID.Text
txtChangeRequest(2) = lblProcessID.Text
txtChangeRequest(3) = txtTitle.Text
txtChangeRequest(4) = txtRevision.Text
txtChangeRequest(5) = txtProcess.Text
txtChangeRequest(6) = cmbManager.Text
txtChangeRequest(7) = cmbOwner.Text
txtChangeRequest(8) = cmbWhere.Text
txtChangeRequest(9) = chkQualify.Text
txtChangeRequest(10) = txtChanges.Text
txtChangeRequest(11) = txtReasons.Text
txtChangeRequest(12) = txtResults.Text
txtChangeRequest(13) = txtOpen.Text
txtChangeRequest(14) = txtSubmit.Text
txtChangeRequest(15) = txtApprove.Text
txtChangeRequest(16) = txtTrain.Text
txtChangeRequest(17) = txtEffective.Text
txtChangeRequest(18) = txtClose.Text
_MyBookMark = _MySQLManager.Position
_MySQLManager.EndCurrentEdit()
_MySQLtable.DefaultView.Sort = "chrChangeID"
_MySQLManager.Position = _MyBookMark
Dim ChangeAdapterCommand As New SqlCommandBuilder(_MySQLAdapter)
_MySQLAdapter.Update(_MySQLtable)
Dim ValidateData As New nspMasterBase.ChangeRequest
Dim EndConnect As New nspMasterBase.MasterBaseConnections
EndConnect.BreakConnMasterBase()
Catch ex As Exception
MessageBox.Show("Error saving record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Case "New"
'Dim NewRow As Integer
Dim SavedChangeRequest As String = lblChangeID.Text
Try
_MySQLManager.EndCurrentEdit()
MessageBox.Show("Record saved. ", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
Dim ValidateData As New nspMasterBase.ChangeRequest
Dim EndConnect As New nspMasterBase.MasterBaseConnections
EndConnect.BreakConnMasterBase()
Catch ex As Exception
MessageBox.Show("Error Saving Record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Select
End Sub






gwboolean

Continue reading...
 
Back
Top