none
Unable to save record to table RRS feed

  • Question

  • Method one

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

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

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

        Private Sub frmChangeRequest_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'Point to Health File
            hlpChangeRequest.HelpNamespace = Application.StartupPath + "\books.chm" 'Need to create specific for this application.
    
            _strTable = "tblChangeMaster"
            _MyState = "New"
            'Define connection
            Dim strConnection As String = "Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=aspnet-MasterBase3.0;Integrated Security=SSPI;AttachDBFilename=F:\SiTechMasterBase1.2\DataBase\MasterBase\MasterBase3.0.mdf"
            'Set Form formats and properties
            Try
                'Connect to database
                _MasterBaseConnection = New SqlConnection(strConnection)
                _MasterBaseConnection.Open()
                'Connection query
                'MyQuery = CStr(qryChangeRequestByChangeID(MyQuery))
                'Set Command object
                _MySQLCommand = New SqlCommand("SELECT * FROM tblChangeMaster WHERE chrChangeID = " & "'" & _strChangeID & "'", _MasterBaseConnection)
                'Set data adapter/table
                _MySQLAdapter = New SqlDataAdapter
                _MySQLAdapter.SelectCommand = _MySQLCommand
                _MySQLtable = New DataTable()
                _MySQLAdapter.Fill(_MySQLtable)
                _MasterBaseClose = True
                'Establishe currency Manager
                _MySQLManager = DirectCast(Me.BindingContext(_MySQLtable), CurrencyManager)
                'Add new row
                _MyBookMark = _MySQLManager.Position
                _MySQLManager.AddNew()
                'Load Data to table
                With Me
                    .lblChangeID.DataBindings.Add("Text", _MySQLtable, "chrChangeID")
                    .lblProcessID.DataBindings.Add("Text", _MySQLtable, "chrProcessID")
                    .txtTitle.DataBindings.Add("Text", _MySQLtable, "chrTitle")
                    .txtRevision.DataBindings.Add("Text", _MySQLtable, "chrRevision")
                    .txtProcess.DataBindings.Add("Text", _MySQLtable, "chrChangeObject")
                    .cmbManager.DataBindings.Add("Text", _MySQLtable, "chrManager")
                    .cmbOwner.DataBindings.Add("Text", _MySQLtable, "chrOwner")
                    .cmbWhere.DataBindings.Add("Text", _MySQLtable, "chrWhere")
                    .chkQualify.DataBindings.Add("Text", _MySQLtable, "blnQualify")
                    .txtChanges.DataBindings.Add("Text", _MySQLtable, "chrChangeMade")
                    .txtReasons.DataBindings.Add("Text", _MySQLtable, "chrChangeReason")
                    .txtResults.DataBindings.Add("Text", _MySQLtable, "chrChangeResult")
                    .txtOpen.DataBindings.Add("Text", _MySQLtable, "dteOpen")
                    .txtSubmit.DataBindings.Add("Text", _MySQLtable, "dteSubmit")
                    .txtApprove.DataBindings.Add("Text", _MySQLtable, "dteApprove")
                    .txtTrain.DataBindings.Add("Text", _MySQLtable, "dteTrain")
                    .txtEffective.DataBindings.Add("Text", _MySQLtable, "dteEffective")
                    .txtClose.DataBindings.Add("Text", _MySQLtable, "dteClose")
                End With
            Catch SQLExceptionErr As SqlException
                MessageBox.Show(SQLExceptionErr.Message, "Access Error.  General exception.")
                Exit Sub
            Catch InvalidOperationExceptionErr As InvalidOperationException
                MessageBox.Show(InvalidOperationExceptionErr.Message, "Access Error.  Invalid Operaion.")
                Exit Sub
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error in Processing SQL", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End Try
            Me.Show()
            Dim setForm As New nspMasterBase.ChangeRequest
            Call setForm.DocumentColorPallete() 'Set form colors
            'Populate form
            'Assign Change ID
            _strChangeID = "CR1000005"
            'Assign Process ID
            _strProcessID = "PID100000005"
            'Assign Title
            _strTitle = "Process Guide"
            'Assign Revision
            _strRevision = "AB"
            'Sets format properties for form
            With Me
                .BackColor = CType(_objFormBackColor, Color)
                .ForeColor = CType(_objFormForeColor, Color)
                .Font = CType(_objFontDefault, Font)
                .StartPosition = FormStartPosition.CenterScreen
                .btnProcedure.Enabled = True
                .btnReport.Enabled = True
                .btnFile.Enabled = True
                .btnPrint.Enabled = True
                .btnScan.Enabled = True
                .btnProcess.Enabled = True
                .btnApprove.Enabled = True
                .btnTrain.Enabled = True
                .btnCancel.Enabled = True
                .btnObsolete.Enabled = True
                .btnExit.Enabled = True
                .btnHelp.Enabled = True
                .lblChangeID.BackColor = CType(_objDisableControlColor, Color)
                .lblProcessID.BackColor = CType(_objDisableControlColor, Color)
                .txtTitle.ForeColor = CType(_objTextForeColor, Color)
                .txtTitle.BackColor = CType(_objTextBackColor, Color)
                .txtTitle.ReadOnly = False
                .txtTitle.TabStop = True
                .txtTitle.TabIndex = 0
                .txtTitle.Focus()
                .txtRevision.ForeColor = CType(_objTextForeColor, Color)
                .txtRevision.BackColor = CType(_objDisableControlColor, Color)
                .txtRevision.ReadOnly = True
                .txtRevision.TabStop = False
                .txtProcess.ForeColor = CType(_objTextForeColor, Color)
                .txtProcess.BackColor = CType(_objTextBackColor, Color)
                .txtProcess.ReadOnly = False
                .txtProcess.TabStop = False
                .txtProcess.TabIndex = 1
                .cmbManager.ForeColor = CType(_objTextForeColor, Color)
                .cmbManager.BackColor = CType(_objTextBackColor, Color)
                .cmbManager.Enabled = True
                .cmbManager.TabStop = False
                .cmbOwner.ForeColor = CType(_objTextForeColor, Color)
                .cmbOwner.BackColor = CType(_objTextBackColor, Color)
                .cmbOwner.Enabled = True
                .cmbOwner.TabStop = False
                .cmbWhere.ForeColor = CType(_objTextForeColor, Color)
                .cmbWhere.BackColor = CType(_objTextBackColor, Color)
                .cmbWhere.Enabled = True
                .cmbWhere.TabStop = False
                .chkQualify.Enabled = True
                .chkQualify.TabStop = False
                .txtChanges.ForeColor = CType(_objTextForeColor, Color)
                .txtChanges.BackColor = CType(_objTextBackColor, Color)
                .txtChanges.ReadOnly = False
                .txtChanges.TabStop = True
                .txtChanges.TabIndex = 2
                .txtReasons.ForeColor = CType(_objTextForeColor, Color)
                .txtReasons.BackColor = CType(_objTextBackColor, Color)
                .txtReasons.ReadOnly = False
                .txtReasons.TabStop = True
                .txtReasons.TabIndex = 3
                .txtResults.ForeColor = CType(_objTextForeColor, Color)
                .txtResults.BackColor = CType(_objTextBackColor, Color)
                .txtResults.ReadOnly = False
                .txtResults.TabStop = True
                .txtResults.TabIndex = 4
                .txtOpen.ForeColor = CType(_objTextForeColor, Color)
                .txtOpen.BackColor = CType(_objDisableControlColor, Color)
                .txtOpen.ReadOnly = True
                .txtOpen.TabStop = False
                .txtSubmit.ForeColor = CType(_objTextForeColor, Color)
                .txtSubmit.BackColor = CType(_objDisableControlColor, Color)
                .txtSubmit.ReadOnly = True
                .txtSubmit.TabStop = False
                .txtApprove.ForeColor = CType(_objTextForeColor, Color)
                .txtApprove.BackColor = CType(_objDisableControlColor, Color)
                .txtApprove.ReadOnly = True
                .txtApprove.TabStop = False
                .txtTrain.ForeColor = CType(_objTextForeColor, Color)
                .txtTrain.BackColor = CType(_objDisableControlColor, Color)
                .txtTrain.ReadOnly = True
                .txtTrain.TabStop = False
                .txtEffective.ForeColor = CType(_objTextForeColor, Color)
                .txtEffective.BackColor = CType(_objDisableControlColor, Color)
                .txtEffective.ReadOnly = True
                .txtEffective.TabStop = False
                .txtClose.ForeColor = CType(_objTextForeColor, Color)
                .txtClose.BackColor = CType(_objDisableControlColor, Color)
                .txtClose.ReadOnly = True
                .txtClose.TabStop = False
            End With
            'Load form Controls with preset data
            With Me
                .lblChangeID.Text = _strChangeID
                .lblProcessID.Text = _strProcessID
                .txtTitle.Text = _strTitle
                .txtRevision.Text = _strRevision
                .cmbManager.SelectedIndex = -1
                .cmbOwner.SelectedIndex = -1
                .cmbWhere.SelectedIndex = -1
                .chkQualify.Checked = False
                .txtOpen.Text = CStr(DateTime.Today)
            End With
        End Sub
    
            Select Case _MyState
                Case "Edit"
                    If Not (ValidateData.ValidateInput()) Then Exit Sub
                    Try
                        Dim txtChangeRequest(18) As String
                        'Update record
                        txtChangeRequest(1) = lblChangeID.Text
                        txtChangeRequest(2) = lblProcessID.Text
                        txtChangeRequest(3) = txtTitle.Text
                        txtChangeRequest(4) = txtRevision.Text
                        txtChangeRequest(5) = txtProcess.Text
                        txtChangeRequest(6) = cmbManager.Text
                        txtChangeRequest(7) = cmbOwner.Text
                        txtChangeRequest(8) = cmbWhere.Text
                        txtChangeRequest(9) = chkQualify.Text
                        txtChangeRequest(10) = txtChanges.Text
                        txtChangeRequest(11) = txtReasons.Text
                        txtChangeRequest(12) = txtResults.Text
                        txtChangeRequest(13) = txtOpen.Text
                        txtChangeRequest(14) = txtSubmit.Text
                        txtChangeRequest(15) = txtApprove.Text
                        txtChangeRequest(16) = txtTrain.Text
                        txtChangeRequest(17) = txtEffective.Text
                        txtChangeRequest(18) = txtClose.Text
                        _MyBookMark = _MySQLManager.Position
                        _MySQLManager.EndCurrentEdit()
                        _MySQLtable.DefaultView.Sort = "chrChangeID"
                        _MySQLManager.Position = _MyBookMark
                        Dim ChangeAdapterCommand As New SqlCommandBuilder(_MySQLAdapter)
                        _MySQLAdapter.Update(_MySQLtable)
                        Dim ValidateData As New nspMasterBase.ChangeRequest
                        Dim EndConnect As New nspMasterBase.MasterBaseConnections
                        EndConnect.BreakConnMasterBase()
                    Catch ex As Exception
                        MessageBox.Show("Error saving record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Try
                Case "New"
                    'Dim NewRow As Integer
                    Dim SavedChangeRequest As String = lblChangeID.Text
                    Try
                        _MySQLManager.EndCurrentEdit()
                        MessageBox.Show("Record saved. ", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Dim ValidateData As New nspMasterBase.ChangeRequest
                        Dim EndConnect As New nspMasterBase.MasterBaseConnections
                        EndConnect.BreakConnMasterBase()
                    Catch ex As Exception
                        MessageBox.Show("Error Saving Record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Try
            End Select
        End Sub
    


    gwboolean

    Sunday, September 16, 2018 6:42 PM

Answers

  • Hi,

    try my code,it can insert data

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim now As DataRow = DTAnnuaire.NewRow()
            now("Id") = txtAuthorID.Text.ToString
            now("Name") = txtAuthorName.Text.ToString
            now("Age") = txtAuthorContact.Text.ToString
            now("Uid") = txtAuthorNotes.Text.ToString
            DTAnnuaire.Rows.Add(now)
            SQLDaAnnuaire.Update(DTAnnuaire)
            MsgBox("Insert successful")
        End Sub
    End Class

    complete code:

    Imports System.Data.SqlClient
    Public Class Form1
        Dim SQLDaAnnuaire As SqlDataAdapter
        Dim CommandeSQLSelect As String
        'Private AnnuaireBindingSource As New BindingSource
        Dim DTAnnuaire As DataTable = New DataTable
        Dim i = 0
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            SQLDaAnnuaire = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(SQLDaAnnuaire)
            SQLDaAnnuaire.Fill(DTAnnuaire)
            Me.BindingSource1.DataSource = DTAnnuaire
    
    
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim now As DataRow = DTAnnuaire.NewRow()
            now("Id") = txtAuthorID.Text.ToString
            now("Name") = txtAuthorName.Text.ToString
            now("Age") = txtAuthorContact.Text.ToString
            now("Uid") = txtAuthorNotes.Text.ToString
            DTAnnuaire.Rows.Add(now)
            SQLDaAnnuaire.Update(DTAnnuaire)
            MsgBox("Insert successful")
        End Sub
    End Class

    Best regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by gwboolean Wednesday, September 19, 2018 5:47 PM
    Wednesday, September 19, 2018 5:42 AM
  • By the way, thanks for sticking through this mess with me.  I truly appreciate your extended effort on getting me this far.  Sorry about that.  

    The issues I still have are with things, like filling related tables, which I have not yet started on.  At this point I just needed to figure out how to add a record using these methods.  

    Oh, by the way, if you have nothing more important to do, perhaps you might explain how I should have applied the logic to make the currency manager method work?  Really though, thanks!


    gwboolean

    • Marked as answer by gwboolean Sunday, September 23, 2018 7:50 PM
    Wednesday, September 19, 2018 6:54 PM

All replies

  • Working with these new methods are really trying my capabilities.  It took me awhile, but I was able to figure out how to modify a record that fits the form I am working with..

    The screen displays the selected record.  I then edit those controls that allow it and then click the save button, which runs the method below.


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

    After the code is executed, the form is redisplayed showing the record as it now exists.  So I figured that creating and saving a new record might be easier for me than getting here with modifying a record...….. But alas, here I am telling you my sad story.

    Below is the method that I thought would create a new record, populated it with certain data and then allow the user to add certain other data.  The code fully executes, but there is no record created.  I am sure that I am missing just some small, but fundamental thing, but I cannot see what it is.

                Case "New"
                    Try
                        _MyState = "View"
                        _MyBookMark = _MySQLManager.Position
                        _MySQLManager.AddNew()
                        Dim txtChangeRequest(18) As String
                        'Update record
                        txtChangeRequest(1) = lblChangeID.Text
                        txtChangeRequest(2) = lblProcessID.Text
                        txtChangeRequest(3) = txtTitle.Text
                        txtChangeRequest(4) = txtRevision.Text
                        txtChangeRequest(5) = txtProcess.Text
                        txtChangeRequest(6) = cmbManager.Text
                        txtChangeRequest(7) = cmbOwner.Text
                        txtChangeRequest(8) = cmbWhere.Text
                        txtChangeRequest(9) = chkQualify.Text
                        txtChangeRequest(10) = txtChanges.Text
                        txtChangeRequest(11) = txtReasons.Text
                        txtChangeRequest(12) = txtResults.Text
                        txtChangeRequest(13) = txtOpen.Text
                        txtChangeRequest(14) = txtSubmit.Text
                        txtChangeRequest(15) = txtApprove.Text
                        txtChangeRequest(16) = txtTrain.Text
                        txtChangeRequest(17) = txtEffective.Text
                        txtChangeRequest(18) = txtClose.Text
                        Dim NewRow As Integer
                        'UpDate Table
                        _MySQLAdapter.Update(_MySQLtable)
                        Dim ChangeAdapterCommand As New SqlCommandBuilder(_MySQLAdapter)
                        _MySQLtable.DefaultView.Sort = "chrChangeID"
                        NewRow = _MySQLtable.DefaultView.Find(NewRow)
                        _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()
                        Dim SetForm As New nspMasterBase.ChangeRequest
                        Call SetForm.SetState(_MyState)
                    Catch ex As Exception
                        MessageBox.Show("Error Saving Record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Try


    gwboolean

    • Merged by Alex Li-MSFT Monday, September 17, 2018 5:54 AM same case
    Wednesday, September 12, 2018 8:08 PM
  • One thing is, you need to declare the command builder prior to updating, and what is _MySQLmanager? is that a bindingsource?


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Also, what exception are you receiving? 

    You may want to take a look at DataBinding methods on applicable controls and drop the array. 

    • Edited by Gtripodi Wednesday, September 12, 2018 8:23 PM
    Wednesday, September 12, 2018 8:20 PM
  • _MySQLManager is the CurrencyManager

    When I Call the update after declaring the command builder it throws an exception at the line below.

    NewRow = _MySQLtable.DefaultView.Find(NewRow)

    It would be my guess that this is because there is no new row.

    What might you be suggesting in the place of the databinding methods?  This one worked pretty well for modifying the data.  But I am not wedded to it.


    gwboolean

    Wednesday, September 12, 2018 8:41 PM
  • Did Karen not go over this with you already? 

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, September 13, 2018 3:43 AM
  • She might well have.  I do not recall.  If so, I obviously did not understand it and am trying a go at it again.

    gwboolean

    Thursday, September 13, 2018 5:07 AM
  • Actually, Karen has gone over this with me.  However, it was with datasets and binding sources.

    gwboolean

    Thursday, September 13, 2018 5:08 AM
  • Actually, Karen has gone over this with me.  However, it was with datasets and binding sources.

    gwboolean

    ok. I dont know all the specifics, but I see a dataset as a container for multiple datatables, you can add datatables to a dataset and fill those tables within the dataset, or you can fill the datatables and then add them to the dataset. Typically the bindingsource.Datasource = the datatable weather it is in the dataset or not. If Karen has you going on bindingsource that is a step in the right direction. 

    Looking at your code, I have mostly questions and not alot of answers. It looks like you intend to use that array as a param for adding a row to a datatable. Instead you should either bind the textboxs to the bindingsource, or add a list item to the bindingsource, which will then properly add the row to the datatable. Then you call endedit on the bindingsource, create the command builder, then call update from the adapter on the table.

    What youre doing with the find, CurrenceyManager, and array are just making me nervous. 


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, September 13, 2018 6:46 PM
  • What I am doing is manipulating my database without the use of datasets or binding sources.  The tables are connected to the database using methods I have been learning from a text on the subject.  I am coding what I want to do with the tables and data directly from the code instead of through datasets and binding sources.

    The examples in the text I am learning this from have a method for saving a newly created record that I am unable duplicate.  Perhaps you might suggest to me what I might tell you about the source of my methods that would be helpful in understanding what the hell I am doing.  Certainly I am not clear on much of it.  It took me quite awhile to figure out how to overwrite a record.


    gwboolean

    Thursday, September 13, 2018 7:18 PM
  • ok, well I have no idea what your code is supposed to do.

    I can only tell you what I would do, and that is to abandon it, but that may not be the best advice.

    Why do you not want to use dataset or datatables?


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, September 13, 2018 7:43 PM
  • There are problems that I have had using datasets.  However, the primary reason was that it has been suggested that it is a better way to work with the data.  Unless someone can point me to a better approach to using connections and direct code than the methods I am attempting use, this is what I have to work with.

    My problems almost always lie in something I have missed in whatever the methodology I am working with.


    gwboolean

    Thursday, September 13, 2018 7:56 PM
  • Is nspMasteBase a class?



    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, September 13, 2018 8:04 PM
  • nspMasterBase is a namespace.  I use it to hold all of the classes used by the form that are not events.  Additionally, many of those classes hold routines and functions that I use.

    gwboolean

    Thursday, September 13, 2018 8:09 PM
  • Hi,

    This is a demo I wrote, one button to update data and another button to insert data.

    Do you want to modify the data or insert data now?

    Imports System.Data.SqlClient
    Public Class Form1
        Dim SQLDaAnnuaire As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private AnnuaireBindingSource As New BindingSource
        Dim DTAnnuaire As DataTable = New DataTable
        Dim i = 0
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            SQLDaAnnuaire = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(SQLDaAnnuaire)
            SQLDaAnnuaire.Fill(DTAnnuaire)
            Me.BindingSource1.DataSource = DTAnnuaire
            Me.BindingNavigator1.BindingSource = Me.BindingSource1
            Me.txtAuthorID.DataBindings.Add(New Binding("Text", Me.BindingSource1, "id"))
    
            Me.txtAuthorName.DataBindings.Add(New Binding("Text", Me.BindingSource1, "Name"))
    
            Me.txtAuthorContact.DataBindings.Add(New Binding("Text", Me.BindingSource1, "Age"))
    
            Me.txtAuthorNotes.DataBindings.Add(New Binding("Text", Me.BindingSource1, "Uid"))
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                DTAnnuaire.Rows(i).Item(0) = txtAuthorID.Text.ToString
                DTAnnuaire.Rows(i).Item(1) = txtAuthorName.Text.ToString
                DTAnnuaire.Rows(i).Item(2) = txtAuthorContact.Text.ToString
                DTAnnuaire.Rows(i).Item(3) = txtAuthorNotes.Text.ToString
                Me.BindingSource1.EndEdit()
                SQLDaAnnuaire.Update(DTAnnuaire)
                MsgBox("Update successful")
    
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
        Private Sub BindingNavigatorMoveNextItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMoveNextItem.Click
            i += 1
        End Sub
    
        Private Sub BindingNavigatorMovePreviousItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMovePreviousItem.Click
            i -= 1
        End Sub
    
        Private Sub BindingNavigatorMoveLastItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMoveLastItem.Click
            i = DTAnnuaire.Rows.Count - 1
    
        End Sub
        Private Sub BindingNavigatorMoveFirstItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMoveFirstItem.Click
            i = 0
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim now As DataRow = DTAnnuaire.NewRow()
            now("Id") = txtAuthorID.Text.ToString
            now("Name") = txtAuthorName.Text.ToString
            now("Age") = txtAuthorContact.Text.ToString
            now("Uid") = txtAuthorNotes.Text.ToString
            DTAnnuaire.Rows.Add(now)
            SQLDaAnnuaire.Update(DTAnnuaire)
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 17, 2018 7:25 AM
  • That looks a whole lot like how I used to add rows to a table.  I never thought of going back and looking at methods I used to use.  Not too bright of me, but I will get right on this and see if I can adapt it to what I am trying to do.

    Thanks for the help.  I will let you know how it works out.


    gwboolean

    Monday, September 17, 2018 3:58 PM
  • Alex, you are aware that is not efficient code?

    IE: you dont need any of this sort of thing:

               DTAnnuaire.Rows(i).Item(0) = txtAuthorID.Text.ToString
                DTAnnuaire.Rows(i).Item(1) = txtAuthorName.Text.ToString
                DTAnnuaire.Rows(i).Item(2) = txtAuthorContact.Text.ToString
                DTAnnuaire.Rows(i).Item(3) = txtAuthorNotes.Text.ToString

    Also, if youre going to have a bindingsource you should be adding rows to the table with the bindingsource list items.

    IE

    With BindingSource
      .AddNew
      DirectCast(BindingSource.Current,DataRowView)("Col")="Value"
      .EndEdit
    End With
    Then calling the update. The only reason you would ever need to insert this list item like this is if the column is not bound to the bindingsource/textbox


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi



    • Edited by Gtripodi Monday, September 17, 2018 7:26 PM
    Monday, September 17, 2018 7:03 PM
  • Alex, you are aware that is not efficient code?

    IE: you dont need any of this sort of thing:

               DTAnnuaire.Rows(i).Item(0) = txtAuthorID.Text.ToString
                DTAnnuaire.Rows(i).Item(1) = txtAuthorName.Text.ToString
                DTAnnuaire.Rows(i).Item(2) = txtAuthorContact.Text.ToString
                DTAnnuaire.Rows(i).Item(3) = txtAuthorNotes.Text.ToString

    Also, if youre going to have a bindingsource you should be adding rows to the table with the bindingsource list items.

    IE

    With BindingSource
      .AddNew
      DirectCast(BindingSource.Current,DataRowView)("Col")="Value"
      .EndEdit
    End With
    Then calling the update. The only reason you would ever need to insert this list item like this is if the column is not bound to the bindingsource/textbox


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi



    Thank you very much.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 19, 2018 1:53 AM
  • OK, I think I did what was suggested.  Here is what I tried.  The code executes with no errors from the save event.  However, no record is added.  It appears to me that I am lacking an update() for that, and the only way I know how to do that is with a dataset, which I do not have. 

    While I can make a dataset and do a dataset.update(), that was not my intent and it seems to me that I should be able to accomplish this without the use of a dataset.

                        _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)
                        'Set Binding Source
                        Dim MyBindingSource = tblChangeRequestBindingSource
                        _MasterBaseClose = True
    
    
                       With tblChangeRequestBindingSource
                            .DataSource = _MySQLtable
                            .AddNew()
    
                            DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrChangeID") = lblChangeID
                            DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrProcessID") = lblProcessID
                            DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrTitle") = txtTitle
                            DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrRevision") = txtRevision
    
    .EndEdit()


    gwboolean

    Wednesday, September 19, 2018 2:09 AM
  • youre thinking wrong.

    you would need to use sql command with parameters otherwise.

    DataSet does not have update method, that belongs to the dataadapter


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, September 19, 2018 2:14 AM
  • I have thought wrong many times.  And probably will in the future.

    So, since I obviously have misinterpreted the suggestion, what should I have done?  Or let me rephrase that.  What do I need to think correctly?


    gwboolean


    • Edited by gwboolean Wednesday, September 19, 2018 2:25 AM
    Wednesday, September 19, 2018 2:25 AM
  • Hi,

    try to add code:

     Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(_MySQLAdapter)

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 19, 2018 2:52 AM
  • YOu need a command builderwith argument of your adapter. you then need adapter.update with argument of your datatable.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, September 19, 2018 2:53 AM
  • Not sure I get that.  I understand declaring sqlCommandBuilder.  So where and how do I use that and is that to be used with the method now under discussion?

    gwboolean

    Wednesday, September 19, 2018 3:14 AM
  • Not sure I get that.  I understand declaring sqlCommandBuilder.  So where and how do I use that and is that to be used with the method now under discussion?

    gwboolean

    Hi,

    SQLCommandBuild is responsible for generating SQL statements for updating the database.

    _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

    Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(_MySQLAdapter)

    _MySQLAdapter.SelectCommand = _MySQLCommand _MySQLtable = New DataTable() _MySQLAdapter.Fill(_MySQLtable) 'Set Binding Source Dim MyBindingSource = tblChangeRequestBindingSource _MasterBaseClose = True With tblChangeRequestBindingSource .DataSource = _MySQLtable .AddNew() DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrChangeID") = lblChangeID DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrProcessID") = lblProcessID DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrTitle") = txtTitle DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrRevision") = txtRevision .EndEdit()

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 19, 2018 3:29 AM
  • I setup like this and still no record.

    gwboolean

    Wednesday, September 19, 2018 3:48 AM
  • Hi,

    You don't seem to  update.

    code:

    _MySQLAdapter.update(_MySQLtable)

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 19, 2018 5:10 AM
  • OK, I had tried that.  However, I get an exception at that line.

    gwboolean

    Wednesday, September 19, 2018 5:37 AM
  • Hi,

    try my code,it can insert data

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim now As DataRow = DTAnnuaire.NewRow()
            now("Id") = txtAuthorID.Text.ToString
            now("Name") = txtAuthorName.Text.ToString
            now("Age") = txtAuthorContact.Text.ToString
            now("Uid") = txtAuthorNotes.Text.ToString
            DTAnnuaire.Rows.Add(now)
            SQLDaAnnuaire.Update(DTAnnuaire)
            MsgBox("Insert successful")
        End Sub
    End Class

    complete code:

    Imports System.Data.SqlClient
    Public Class Form1
        Dim SQLDaAnnuaire As SqlDataAdapter
        Dim CommandeSQLSelect As String
        'Private AnnuaireBindingSource As New BindingSource
        Dim DTAnnuaire As DataTable = New DataTable
        Dim i = 0
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            SQLDaAnnuaire = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(SQLDaAnnuaire)
            SQLDaAnnuaire.Fill(DTAnnuaire)
            Me.BindingSource1.DataSource = DTAnnuaire
    
    
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim now As DataRow = DTAnnuaire.NewRow()
            now("Id") = txtAuthorID.Text.ToString
            now("Name") = txtAuthorName.Text.ToString
            now("Age") = txtAuthorContact.Text.ToString
            now("Uid") = txtAuthorNotes.Text.ToString
            DTAnnuaire.Rows.Add(now)
            SQLDaAnnuaire.Update(DTAnnuaire)
            MsgBox("Insert successful")
        End Sub
    End Class

    Best regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by gwboolean Wednesday, September 19, 2018 5:47 PM
    Wednesday, September 19, 2018 5:42 AM
  • OK, I had tried that.  However, I get an exception at that line.

    gwboolean

    How much sense does it make to post there is an exception but not mention what exception?

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, September 19, 2018 12:44 PM
  • It makes no sense because there was no message provided.  This sits in a Try/Catch and it moved to the catch when executing that line.  The only message is the one I put there and I know it doesn't tell me anything.

    gwboolean

    Wednesday, September 19, 2018 3:53 PM
  • ok, comment out the try/catch block.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, September 19, 2018 3:56 PM
  • Ahhh, Bach!

    Here is the message.



    gwboolean

    Wednesday, September 19, 2018 4:03 PM
  • Yikes!!!!! Something just happened.  The chrChangeID column was not being loaded.  So anyway, corrected that and I am getting a new record.  However, all of the columns that were filled were filled with System.Windows...…  

    I have had that happen in the past, but do not remember what is going on when that happens.


    gwboolean

    Wednesday, September 19, 2018 4:10 PM
  • where you are doing this...

    DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrChangeID") = lblChangeID
    DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrProcessID") = lblProcessID
    DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrTitle") = txtTitle
    DirectCast(tblChangeRequestBindingSource.Current, DataRowView).Item("chrRevision") = txtRevision

    You are not using the .Text value, you are actually setting the column value as the object type string, which is actually being inferred. At the very top of your code, even before the Imports, add "Option Strict On"

    Additionally, with the above mentioned textboxs, you do not need to set values like this if they are bound to the bindingsource.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, September 19, 2018 5:25 PM
  • Yeah, I fixed that by gong from = Control, to = CStr(Control.Text).  However, I am back to throwing an exception at the .Update().  I am trying to figure out why that is so.

    So What value setting approach would you suggest?


    gwboolean


    • Edited by gwboolean Wednesday, September 19, 2018 5:33 PM
    Wednesday, September 19, 2018 5:32 PM
  • I still have a few issues to workout, but this does save a record now.  However, I would still like to understand why the method using the currency manager does not work?  It seems like it should.

    gwboolean

    Wednesday, September 19, 2018 5:47 PM
  • So I go through an extended effort to get you going, then you mark an inefficient post as the answer? 

    ...anyway... you're welcome...


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, September 19, 2018 6:05 PM
  • By the way, thanks for sticking through this mess with me.  I truly appreciate your extended effort on getting me this far.  Sorry about that.  

    The issues I still have are with things, like filling related tables, which I have not yet started on.  At this point I just needed to figure out how to add a record using these methods.  

    Oh, by the way, if you have nothing more important to do, perhaps you might explain how I should have applied the logic to make the currency manager method work?  Really though, thanks!


    gwboolean

    • Marked as answer by gwboolean Sunday, September 23, 2018 7:50 PM
    Wednesday, September 19, 2018 6:54 PM
  • I finally got this mess cleaned up.  As expected, it turned out to be a sequencing error as I tried to adapt the method to my own usage. 

    I went back and set my database up to work like the examples I was working from and finally got the sequencing worked out and have it working.  Now I can add/delete/modify data in the table.  Now I can adapt the method to my own usage.

    Thanks for the assistance.  I have to say that this method is harder than using the datasets and bindingsources I used to use.  I sure hope there is some benefit from this.


    gwboolean

    Sunday, September 23, 2018 7:50 PM