|
-
Sep 18th, 2018, 02:37 PM
#1
Thread Starter
Fanatic Member
 [RESOLVED] Unable to add record to table
I have been attempting to adapt a method that successfully adds a record to a table. The methods used are below:
The process for this method: The form opens displaying a record from a table. The user can add a new record by clicking btnAddNew, which adds and displays an empty record. The user can then input information then can save the new record by clicking btnSave. In this project those methods work.
Code:
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
Code:
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
Code:
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, I have attempted to adapt this to my own usage in another project, using a different table and database. However, while I am able to modify records, I am unable to add new records. Below is my modifications to the above to fit my own needs.
In this process, the Load event simply displays the form with a few of the controls containing pre-defined values. The user can input data into the displayed form. The user can then click the btnSave button when connects to the table, adds a new record, populates it with the user inputs to the form, then saves it and redisplays the record.
The process is executed without error or exception. However, when the form redisplays it is empty and no record is actually saved. I am unable to see what I am doing wrong here. However, after running through every test I can think of, I just do not see what I have done wrong.
Code:
Private Sub frmChangeRequest_Load(sender As Object, e As EventArgs) Handles MyBase.Load
_MyState = "New" 'Defines Form display and properties for Change Request form
btnSave.Enabled = True
'Point to Help File
hlpChangeRequest.HelpNamespace = Application.StartupPath + "\books.chm" 'Need to create specific for this application.
'Set Form formats and properties
Dim setForm As New nspMasterBase.ChangeRequest
Call setForm.DocumentColorPallete() 'Set Color objects
'Set properties for Change Request 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
'Populate form
'Assign Change ID
_strChangeID = "CR1000005"
'Assign Process ID
_strProcessID = "PID100000005"
'Assign Title
_strTitle = "Process Guide"
'Assign Revision
_strRevision = "AB"
'Assign values to specific form controls
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
Code:
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Select Case _MyState
Case "Edit"
If Not (ValidateData.ValidateInput()) Then Exit Sub
Try
Dim txtChangeRequest(18) As String
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
_strTable = "tblChangeMaster"
'Connection string
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"
'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 to table
_MySQLManager.AddNew()
_MyBookMark = _MySQLManager.Position 'Set pointer
'Load Control values to new record.
With Me
'Load data to table method 1
.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")
''Load data to table Method 2
'.lblChangeID.DataBindings.Add(New Binding("Text", _MySQLtable, "chrChangeID"))
'.lblProcessID.DataBindings.Add(New Binding("Text", _MySQLtable, "chrProcessID"))
'.txtTitle.DataBindings.Add(New Binding("Text", _MySQLtable, "chrTitle"))
'.txtRevision.DataBindings.Add(New Binding("Text", _MySQLtable, "chrRevision"))
'.txtProcess.DataBindings.Add(New Binding("Text", _MySQLtable, "chrChangeObject"))
'.cmbManager.DataBindings.Add(New Binding("Text", _MySQLtable, "chrManager"))
'.cmbOwner.DataBindings.Add(New Binding("Text", _MySQLtable, "chrOwner"))
'.cmbWhere.DataBindings.Add(New Binding("Text", _MySQLtable, "chrWhere"))
'.chkQualify.DataBindings.Add(New Binding("Text", _MySQLtable, "blnQualify"))
'.txtChanges.DataBindings.Add(New Binding("Text", _MySQLtable, "chrChangeMade"))
'.txtReasons.DataBindings.Add(New Binding("Text", _MySQLtable, "chrChangeReason"))
'.txtResults.DataBindings.Add(New Binding("Text", _MySQLtable, "chrChangeResult"))
'.txtOpen.DataBindings.Add(New Binding("Text", _MySQLtable, "dteOpen"))
'.txtSubmit.DataBindings.Add(New Binding("Text", _MySQLtable, "dteSubmit"))
'.txtApprove.DataBindings.Add(New Binding("Text", _MySQLtable, "dteApprove"))
'.txtTrain.DataBindings.Add(New Binding("Text", _MySQLtable, "dteTrain"))
'.txtEffective.DataBindings.Add(New Binding("Text", _MySQLtable, "dteEffective"))
'.txtClose.DataBindings.Add(New Binding("Text", _MySQLtable, "dteClose"))
''Load Data to table Method 3
'_MySQLtable.Rows(_MyBookMark).Item(0) = lblChangeID.Text
'_MySQLtable.Rows(_MyBookMark).Item(1) = lblProcessID.Text
'_MySQLtable.Rows(_MyBookMark).Item(3) = txtTitle.Text
'_MySQLtable.Rows(_MyBookMark).Item(4) = txtRevision.Text
'_MySQLtable.Rows(_MyBookMark).Item(5) = txtProcess.Text
'_MySQLtable.Rows(_MyBookMark).Item(6) = cmbManager.Text
'_MySQLtable.Rows(_MyBookMark).Item(7) = cmbOwner.Text
End With
'Save new row to table
_MySQLManager.EndCurrentEdit()
'_MySQLAdapter.Update(_MySQLtable)
'_MySQLManager.Position = NewRow
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
'Reset display after saving new record
With Me
.BackColor = CType(_objFormBackColor, Color)
.ForeColor = CType(_objFormForeColor, Color)
.Font = CType(_objFontDefault, Font)
.StartPosition = FormStartPosition.CenterScreen
.btnProcedure.Enabled = False
.btnReport.Enabled = False
.btnFile.Enabled = True
.btnPrint.Enabled = True
.btnScan.Enabled = False
.btnProcess.Enabled = False
.btnApprove.Enabled = False
.btnTrain.Enabled = False
.btnCancel.Enabled = False
.btnObsolete.Enabled = False
.btnExit.Enabled = True
.btnSave.Enabled = False
.btnHelp.Enabled = True
.lblChangeID.BackColor = CType(_objDisableControlColor, Color)
.lblProcessID.BackColor = CType(_objDisableControlColor, Color)
.txtTitle.BackColor = CType(_objDisableControlColor, Color)
.txtTitle.ReadOnly = True
.txtTitle.TabStop = False
.txtTitle.TabIndex = 0
'.txtTitle.Focus()
.txtRevision.BackColor = CType(_objDisableControlColor, Color)
.txtRevision.ReadOnly = True
.txtRevision.TabStop = False
.txtProcess.BackColor = CType(_objDisableControlColor, Color)
.txtProcess.ReadOnly = True
.txtProcess.TabStop = False
.cmbManager.BackColor = CType(_objDisableControlColor, Color)
.cmbManager.Enabled = False
.cmbManager.TabStop = False
.cmbOwner.BackColor = CType(_objDisableControlColor, Color)
.cmbOwner.Enabled = False
.cmbOwner.TabStop = False
.cmbWhere.BackColor = CType(_objDisableControlColor, Color)
.cmbWhere.Enabled = False
.cmbWhere.TabStop = False
.chkQualify.Enabled = False
.chkQualify.TabStop = False
.txtChanges.BackColor = CType(_objDisableControlColor, Color)
.txtChanges.ReadOnly = True
.txtChanges.TabStop = False
.txtReasons.BackColor = CType(_objDisableControlColor, Color)
.txtReasons.ReadOnly = True
.txtReasons.TabStop = False
.txtResults.BackColor = CType(_objDisableControlColor, Color)
.txtResults.TabStop = False
.txtResults.ReadOnly = True
.txtOpen.BackColor = CType(_objDisableControlColor, Color)
.txtOpen.ReadOnly = True
.txtOpen.TabStop = False
.txtSubmit.BackColor = CType(_objDisableControlColor, Color)
.txtSubmit.ReadOnly = True
.txtSubmit.TabStop = False
.txtApprove.BackColor = CType(_objDisableControlColor, Color)
.txtApprove.ReadOnly = True
.txtApprove.TabStop = True
.txtTrain.BackColor = CType(_objDisableControlColor, Color)
.txtTrain.ReadOnly = True
.txtTrain.TabStop = False
.txtEffective.BackColor = CType(_objDisableControlColor, Color)
.txtEffective.ReadOnly = True
.txtEffective.TabStop = False
.txtClose.BackColor = CType(_objDisableControlColor, Color)
.txtClose.ReadOnly = True
.txtClose.TabStop = True
End With
Me.Show()
End Select
End Sub
Last edited by gwboolean; Sep 18th, 2018 at 02:45 PM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote