-
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.
-
Sep 18th, 2018, 06:10 PM
#2
Re: Unable to add record to table
this is really hard to understand. What is "nspMasterBase"? I assume a class or module? What is "_MySQLManager" Is that a bindingsource? As far as I am concerned you're better off trying this post again without code.
-
Sep 18th, 2018, 06:23 PM
#3
Thread Starter
Fanatic Member
Re: Unable to add record to table
nspMasterbase is nothing more than a Namespace. In the namespace I have a class for each of the Form Class. Each of the classes hold all of the routines, functions, etc. belonging to a specific form. It might also classes for other things. All global variables _MyVariable are declared in a module.
_MySQLManager = the Currency Manager. I really do not understand the Currency Manager, beyond setting it up and attempting to use it the same way and place they do.
How else would you suggest I present the information? I have a process provided to me that works, which I attempted to explain. I modified the process and the process failed. I reviewed and tested the process, to the best of my ability, and was unable to understand why the process failed. I attempted to explain how I modified the original process and what occurred.
So do you understand the process or do I just need to redefine the problem?
Last edited by gwboolean; Sep 18th, 2018 at 06:33 PM.
-
Sep 18th, 2018, 06:37 PM
#4
Thread Starter
Fanatic Member
Re: Unable to add record to table
Also, I am currently looking at using a binding source to add the new record. But I don't know yet if that is going to work for me.
-
Sep 19th, 2018, 01:29 PM
#5
Re: Unable to add record to table
Your code does seem to be extremely over complicated for a simple Add/Edit/Delete system. I couldn't follow the logic at all. This link http://www.vbforums.com/showthread.p...MySQL-using-VS
I posted a simple example of using a bindingsource and binding navigator, check out post #5.
-
Sep 19th, 2018, 01:46 PM
#6
Thread Starter
Fanatic Member
Re: Unable to add record to table
I have actually already adopted the baindingsource you provided and it is providing me with a new record. However, I would really like to understand why the method that I am attempting to use is not working. You already stated that my logic is all off on that. Since I was just guessing at how that process is carried out I am not surprised. Could you perhaps suggest what the logic order should be for that operation?
-
Sep 19th, 2018, 02:24 PM
#7
Re: Unable to add record to table
Well, first off, compare the load events of the two forms... the first one opens a connection to a database and loads data from it, fills a datatable, and then binds it to some textboxes... the second.... doesn't ... it does some UI stuff that should really just be set at design time and be done with... there is absolutely no database activity at all in there.
The save button... there's so much going on in there, there's also a lot of noise, a lot of code that's commented out that doesn't need to be there... again, it looks nothing like what's going on in the first form... what's with the string array that doesn't appear to do anything? And if you want to use DataBinding, use it, but use it from the start (line in the first form), not at the last second (like in the second form)...
-tg
-
Sep 19th, 2018, 03:35 PM
#8
Thread Starter
Fanatic Member
Re: Unable to add record to table
So what you are saying is that it is a mess. So here is what I want to do. I want to start with a display of nothing more than empty controls (I don't even need to open the database yet (they should not yet be bound). Some of the controls will already have values. I then wanted the user to be able to input/modify values of some, but not all, of the displayed controls. That was supposed to be the load event
After the user is done, I want the database opened, a record added and then populated with the control values, then redisplayed. That was supposed to be the save event.
That was the plan, which I obviously did not even come close to. So is that even possible with the method that I sliced an diced until it morphed into a mixture of night soil and filth?
-
Sep 19th, 2018, 08:52 PM
#9
Re: Unable to add record to table
Yeah I'm not sure why you would want to use binding for something like that. If all you want is empty controls and some controls with default values and then after the user enters some data, add a new record to the database using the controls, then what's the purpose of a bindingsource? You keep using the term "then redisplayed", why would it need to be redisplayed? It's already displayed. All you need is the controls, parameters and an Insert command.
-
Sep 19th, 2018, 09:01 PM
#10
Thread Starter
Fanatic Member
Re: Unable to add record to table
The redisplay is so that all of the inputs are disabled after the . But it is nothing that is important at this point. I never really wanted to work with binding controls, although I am no longer apposed to that, it was just offered to me.
add a new record to the database using the controls
Sounds good to me. How is that different from just adding a record, then filling it with the values from the controls?
-
Sep 19th, 2018, 09:09 PM
#11
Re: Unable to add record to table
Sounds good to me. How is that different from just adding a record, then filling it with the values from the controls?
It's not, here's an example of what I'm talking about,
Code:
Private Sub SaveDataButton_Click(sender As Object, e As EventArgs) Handles SaveDataButton.Click
Dim cmd As New SqlCommand("Insert into Books (BookName, Author) Values (@BName, @Auth)", con)
cmd.Parameters.AddWithValue("@BName", BNameTextBox.Text)
cmd.Parameters.AddWithValue("@Auth", AuthTextBox.Text)
cmd.ExecuteNonQuery()
End Sub
btw - I just did that real quick, all data access code should be in Try/Catch structure and this way would be better,
Code:
Private Sub SaveDataButton_Click(sender As Object, e As EventArgs) Handles SaveDataButton.Click
Try
Using cmd As New SqlCommand("Insert into Books (BookName, Author) Values (@BName, @Auth)", con)
cmd.Parameters.AddWithValue("@BName", BNameTextBox.Text)
cmd.Parameters.AddWithValue("@Auth", AuthTextBox.Text)
cmd.ExecuteNonQuery()
End Using
Catch ex as Exception
MessageBox.Show( ex.ToString)
End Try
End Sub
Last edited by wes4dbt; Sep 19th, 2018 at 09:15 PM.
-
Sep 19th, 2018, 09:11 PM
#12
Thread Starter
Fanatic Member
Re: Unable to add record to table
I should have already asked. Does this method use the currency manager?
-
Sep 19th, 2018, 09:19 PM
#13
Re: Unable to add record to table
What you see is what you get.
-
Sep 19th, 2018, 09:28 PM
#14
Thread Starter
Fanatic Member
Re: Unable to add record to table
OK, I now recognize the code. I see that is from the database that got me into all of this mess in the first place.
-
Sep 19th, 2018, 09:32 PM
#15
Re: Unable to add record to table
Originally Posted by gwboolean
OK, I now recognize the code. I see that is from the database that got me into all of this mess in the first place.
No it's not, I just wrote that code. It has nothing to do with the code you posted. It's just an example.
-
Sep 23rd, 2018, 11:56 AM
#16
Thread Starter
Fanatic Member
Re: Unable to add record to table
I have been doing a lot of comparing, but still no cigar. However, I have decided that I should only do one method at a time and have decided to drop the databinding (for now), until I can get this figured out with why I am unable to add a record using the Currency Manager method. It seems to me that I just do not fully understand (understatement) the process and am just not seeing the proper sequence/steps in the process.
At this point I am merely attempting to duplicate with my database what the other application did with it's database. I still haven't got this adding of a record straight, but everything else (modification/deletion/saving) is working smoothly.
-
Sep 23rd, 2018, 02:20 PM
#17
Re: Unable to add record to table
Yeah, you really don't seem to understand how to use a bindingsource. Don't know what has you so fixed on the Currency Manager, adding a record using a bindingsource is simply bindingsource1.AddNew.
Don't know why your having problems adding a record now, would need to see your current code.
-
Sep 23rd, 2018, 02:45 PM
#18
Thread Starter
Fanatic Member
Re: Unable to add record to table
Fixed on the Currency manager because that is the only method I have found and understand (just barely enough) to make work. Using the bindingsource appears to look like a method that I previously unsuccessfully attempted. I did make it work, and will go back and take another look at that, as well as a couple of other methods, but for now, I am taking baby steps with what I can make work.
I got it worked out with adding a new record, once I got the sequencing all cleaned up. However, it took a little longer because I had to find and fix a couple of relationships in the database that I had forgotten about while going through this misery.
Now I can adapt it to the way I want to use it. I have to say, this is even harder than working with the datasets and bindingsources I used to use.
-
Sep 23rd, 2018, 08:41 PM
#19
Re: [RESOLVED] Unable to add record to table
gw, you have officially baffled me. You literally are going over the hills and through the woods.... and across the river and digging a tunnel, boarding a 9PM flight, landing in the bronx to take a train to the bus station to get to grandmothers house.
-
Sep 24th, 2018, 10:55 AM
#20
Thread Starter
Fanatic Member
Re: [RESOLVED] Unable to add record to table
You now can imagine how I have felt, and continue to feel, throughout this process. OK, help me a little and I can probably figure out how to explain what it took to fix this damn thing.
By the way, I thought I was just going to the store. How was I to know I would end up at grandmothers house?
-
Sep 24th, 2018, 10:58 AM
#21
Re: [RESOLVED] Unable to add record to table
If you ended up at the store it was by accident. Your code definitely has you going to grandmas house.
-
Sep 24th, 2018, 11:13 AM
#22
Thread Starter
Fanatic Member
Re: [RESOLVED] Unable to add record to table
That code was not the end result. Although all the words are there, it turns out that I needed sentences. As you said, the way I had it setup, it took me to grandma's house. So what I did was setup the code like theirs was, which worked for me. But it still went to grandma's house.
Now I am in the process, again, of trying to adapt the method to work the way I want to use it, which would present the user with a new record, partially filled, that the user could complete and then save, instead of the user having an existing record displayed to the user, which he/she must then create a new record over the existing record.
-
Sep 24th, 2018, 11:59 AM
#23
Re: [RESOLVED] Unable to add record to table
Here's how I'd do it... you can follow this or not... but this is how I'd do it... first, it doesn't sound like there is a need for databinding. So let's just set that aside.
1) Create a stored procedure that accepts the data through parameters and inserts it into the table.
2) Create the form - at design time, you can setup any of the default values or visuals
3) From the save button, create a command object, set the command text to the sproc name, the command type to stored procedure, add the parameters and their values (use .Parameters.AddWithValue method, it's the quickest/easiest))
4) Then simply .ExecuteNonQuery the command object ...
That's really the simplest way to do that. From there you can reset your form, close it, or leave it (I'd disable the save button so that they don't try to save a duplicate record though) open.
There's no need to get a current record or anything. Just open a form, get the data, and save it.
-tg
-
Sep 24th, 2018, 12:06 PM
#24
Thread Starter
Fanatic Member
Re: [RESOLVED] Unable to add record to table
Sounds really good! As soon as I can figure all of that out and fully understand it, that is exactly what I am going to do. Actually, I am beginning to understand what is going on here and why I have been unable to get this up and running. My problem now appears to be with the things I keep trying to do with breaking this process up to use in the manner I described.
By the way, that husky in your picture looks almost exactly like my old Jack of nearly 40 years ago. If he is anything like Jack he is hell on wheels!
-
Sep 24th, 2018, 01:35 PM
#25
Re: [RESOLVED] Unable to add record to table
Yeah, he's a handfull and then some... he's not actually a husky... he's a Malamute... that pic is from when he was 4 months old... he's now 13 months old and 95 pounds.... and he knows it....
-tg
-
Sep 24th, 2018, 02:01 PM
#26
Re: [RESOLVED] Unable to add record to table
-
Sep 24th, 2018, 02:18 PM
#27
Re: [RESOLVED] Unable to add record to table
MEh, I guess no reason in particular other than that's the way I'd do it. Some of it is habit, some of it is personal preference. I'd prefer to separate my data access and my business stuff, and resist putting SQL directly in my code... stored procedures are easier for me to deal with and shuttle data to/from than having convoluted SQL right in my VB code. Again, that's the way *I'd* do it... Admittedly part of this stems from having to build old school queries with string concatenation which was pretty bad back in the day... things have gotten better with literals... but I'm also usually in an enterprise type environment where sprocs are the norm.
kpmc - why not sprocs?
-tg
-
Sep 24th, 2018, 02:22 PM
#28
Re: [RESOLVED] Unable to add record to table
Biggest reason... I would have to talk to our DBA.
Then I would also feel I have a blacksheep out there.
-
Sep 24th, 2018, 02:38 PM
#29
Re: [RESOLVED] Unable to add record to table
Originally Posted by kpmc
Biggest reason... I would have to talk to our DBA.
haha... yeah, then I could see an argument for not doing that... I've actually been fortunate (or unfortunate depending on how you want to look at it) that the shops I've been in the devs are also the "DBAs" ... not to say we didn't have DBAs, but that they are there to more support us and the clients, and so don't usually get in the way like they often do at other organizations. Only once have I been at a place where they do, but that was to protect client production data, but even then interaction was largely automated so the process was painless and as simple as submitting a ticket and waiting for the job to run.
-tg
-
Sep 24th, 2018, 02:56 PM
#30
Re: [RESOLVED] Unable to add record to table
I've always lived by 1 simple rule.
"No conflict is so great that it cant be avoided"
(...Which generally doesn't apply when challenging people in various forums)
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
|