[RESOLVED] [2005] Updating a BindingSource

[So(u)rcerer]

Hello again everyone.

In my project, I'm using a Bindingnavigator to be able to navigate through all the records in an Access table. This is what I did:

Imports System.Data
Imports System.Data.OleDb

Public Class frmPlay
    Private CDsBS As BindingSource
    Private CDsDS As DataSet

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Create a BindingSource for all the BindingNavigators to use
        CDsBS = New BindingSource()

        CDsBS.AllowNew = True

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        CDsDS = RetrieveCDs()
        If CDsDS IsNot Nothing Then

            ' Associate the DataSet with the BindingSource.
            Me.CDsBS.DataMember = "CDs"
            Me.CDsBS.DataSource = CDsDS

            ' Associate the BindingNavigators with the BindingSource
            Me.bnPlayCD.BindingSource = Me.CDsBS

            ' Bind the form controls
            Me.txtPCDArtist.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.CDsBS, "CDArtist", True))
            Me.txtPCDTitle.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.CDsBS, "CDTitle", True))
            Me.cboPCDType.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.CDsBS, "CDType", True))
            Me.cboPCDListType.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.CDsBS, "CDListType", True))

        End If
    End Sub

    Public Function RetrieveCDs() As DataSet

        Try

            ' Retrieve CD data from database into a DataSet
            ' Build a connnection string to the database
            Dim CDsCSB As New OleDbConnectionStringBuilder
            CDsCSB.DataSource = ".\Play.mdb"
            CDsCSB.Provider = "Microsoft.Jet.OLEDB.4.0"

            ' Prepare a DataSet to receive the CD data
            Dim CDsDS As New DataSet("CDs")

            ' Open connection to the Play database
            Using CDsCon As New OleDbConnection(CDsCSB.ConnectionString)

                CDsCon.Open()

                ' Retrieve CD data
                Dim command As New OleDbCommand( _
                  "SELECT * FROM [CDs]", CDsCon)

                Using drCDs As OleDbDataReader = command.ExecuteReader()

                    CDsDS.Load( _
                     drCDs, _
                     LoadOption.OverwriteChanges, _
                     New String() {"CDs"})

                End Using

                ' Close the connection to the database
                CDsCon.Close()
            End Using


            Return CDsDS
        Catch err As OleDbException

            MessageBox.Show(err.Message, "OLE DB Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return Nothing
        End Try

    End Function
    Private Sub btnPCDUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPCDUpdate.Click
        CDsBS.EndEdit()
        CDsDS.AcceptChanges()
    End Sub

End Class

Currently, it does show all the fields and values present in the table.
The problem is, after I have clicked the Add New button on the BindingNavigator, and entered the info, and clicked Update, it does not save the record into the table.

What must I do, just to be able to complete the adding record process?
Can anyone help?

[dglienna]

Not close the connection after you read it:

                ' Close the connection to the database
                CDsCon.Close()

move it down to AFTER you update the table

[So(u)rcerer]

Thanx for your reply.
I tried what you suggested, it didn't work. The risk of having a DB connection open the whole time, it may lead to SQL injection attacks, isn't it?

I tried using this in my Update button:

   Private Sub btnPCDUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPCDUpdate.Click

        Dim CDUpdateCom As New OleDbCommand
        ' Retrieve CD data from database into a DataSet
        ' Build a connnection string to the database
        Dim CDsCSB As New OleDbConnectionStringBuilder
        CDsCSB.DataSource = ".\Play.mdb"
        CDsCSB.Provider = "Microsoft.Jet.OLEDB.4.0"

        ' Prepare a DataSet to receive the CD data
        'Dim CDsDS As New DataSet("CDs")

        ' Open connection to the Play database
        Dim CDsCon As New OleDbConnection(CDsCSB.ConnectionString)

        CDsCon.Open()

        CDUpdateCom.Connection = CDsCon
        CDUpdateCom.CommandType = CommandType.Text
        CDUpdateCom.CommandText = "Insert Into CDs (CDArtist, CDTitle, CDType, CDListType) Values (@Artist, @Title, @Type, @ListType)"

        Dim Artist As OleDbParameter = CDUpdateCom.Parameters.Add("@Artist", OleDbType.VarChar, 100)
        Artist.Value = txtPCDArtist.Text

        Dim Title As OleDbParameter = CDUpdateCom.Parameters.Add("@Title", OleDbType.VarChar, 100)
        Title.Value = txtPCDTitle.Text

        Dim Type As OleDbParameter = CDUpdateCom.Parameters.Add("@Type", OleDbType.VarChar, 50)
        Type.Value = cboPCDType.Text

        Dim ListType As OleDbParameter = CDUpdateCom.Parameters.Add("@ListType", OleDbType.VarChar, 100)
        ListType.Value = cboPCDListType.Text

        Try
            CDUpdateCom.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

        CDsCon.Close()
        CDsDS = RetrieveCDs()

    End Sub

And, nothing, ziltch, nada, nothing gets inserted.

This is supposed to be a quick & simple app, and I was supposed to have it finished already, this is all that prevents me from going on to a more current project, and I cannot do proper testing if something doesn't work!

[So(u)rcerer]

OK, I had enough of VB 2005's pathetic data capabilities. They suck!

I ended up spending the last 20 minutes completing the adding, editing & updating, and deleting of records in VB.NET 2003. And guess what, it works beautifully.

Lesson learnt:
next time I want to do any manipulation of database related stuff, I will do it in VB 2003, and definitely not in VB 2005.

Thanx anyways!