[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!