Results 1 to 30 of 30

Thread: [RESOLVED] Unable to add record to table

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Resolved [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.

  2. #2
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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?

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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?

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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?

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Unable to add record to table

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

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Unable to add record to table

    What you see is what you get.

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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.

  15. #15
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Unable to add record to table

    Quote Originally Posted by gwboolean View Post
    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.

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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.

  17. #17
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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.

  19. #19
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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.

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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?

  21. #21
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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.

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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.

  23. #23
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    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!

  25. #25
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  26. #26
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: [RESOLVED] Unable to add record to table

    tg- why a storedproc?

  27. #27
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  28. #28
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: [RESOLVED] Unable to add record to table

    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.

  29. #29
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: [RESOLVED] Unable to add record to table

    Quote Originally Posted by kpmc View Post
    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  30. #30
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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
  •  



Click Here to Expand Forum to Full Width