[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

---

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.

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?

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.

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.

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?

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

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?

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.

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.

Quote:

---

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?

Quote:

---

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

---

I should have already asked. Does this method use the currency manager?

What you see is what you get.

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.

Quote:

---

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.

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.

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.

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.

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.

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?

If you ended up at the store it was by accident. Your code definitely has you going to grandmas house.

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.

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

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!

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

tg- why a storedproc?

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

Quote:

---

kpmc - why not sprocs?

---

Biggest reason... I would have to talk to our DBA.

Then I would also feel I have a blacksheep out there.

Quote:

---

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

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)