Binding to listbox / using data access object

6 Apr 2005 3:40 PM

Jordan

I'm trying to teach myself how to use a class to handle all my database
access needs (add, update, delete, etc), as opposed to using spaghetti code
in my aspnet webforms as I have done up to now.

So far my class "CabinetSpec.vb" has Update, Add, and Delete methods that
work perfectly. However, I'm having problems with my "List" function, which
I want to return some rows to fill a ListBox control on my webform. I'm not
sure what the best way to do it is, but I set it up to return a DataSet
object, which I then bind to my ListBox control.

Everytime I add, update or delete a CabinetSpec on the webform, the ListBox
containing the list of CabinetSpecs updates itself using this function:

-------------------------------------------------------
CabinetSpec.vb

Public Function List() As DataSet
    'return a list of RowIDs & Descriptions
    mstrSQL = "SELECT CabinetSpecID, Description FROM CabinetSpecs ORDER BY
Description"
    Dim ds As New DataSet
    Try
        Connect()
        Dim da As New SqlDataAdapter(mstrSQL, objConnection)
        da.Fill(ds, "CabinetSpecs")
    Catch ex As Exception
        Throw ex
    Finally
        Disconnect()
    End Try
    Return ds
End Function
-------------------------------------------------------

Then my webform binds the data to the ListView here:

-------------------------------------------------------
WebForm

    Private Sub Fill_lstSpecs()
        Try
            Dim CS As New CabinetSpec
            With lstSpecs
                .SelectedIndex = -1
                .DataSource = CS.List.Tables(0).DefaultView
                .DataTextField = "Description"
                .DataValueField = "CabinetSpecID"
                .DataBind()
            End With
        Catch ex As Exception
            lblError.Text = ex.Message
        End Try
    End Sub
-------------------------------------------------------

This works, and I can see the ListBox update itself everytime I Add, Update
or Delete a record.
The problem occurs when I leave the page and return - for some reason when
the page loads Fill_lstSpecs() the ListBox no longer shows the most recent
dataset. For example, I add a new CabinetSpec Description "test1".
Fill_lstSpecs() fires and the ListBox now shows "test1". I leave the page
and come back, Fill_lstSpecs() fires again, but now "test1" doesn't appear.

Any diagnosis would be appreciated.

Thanks,
Jordan

17 May 2005 1:54 AM

Ross Donald

Hi,

After you add the new entry to the list box are you saving it back to the
database? It sounds like you are just adding it to the list but not calling
the Add method.

It also could be the order of events as the page loads. If you are filling
the list before adding the record to the database it will have some strange
results.

You could have a look at the ASP.NET output of my code generation tool as an
example of how to separate your data access code from your user interface
pages. See the link in my sig.

--
Ross Donald
..NET Code Generation - http://www.radsoftware.com.au/codegenerator/

Show quoteHide quote

"Jordan" <nospam@bellsouth.net> wrote in message
news:ziT4e.21366$UW6.6619@bignews5.bellsouth.net...
> I'm trying to teach myself how to use a class to handle all my database
> access needs (add, update, delete, etc), as opposed to using spaghetti
> code in my aspnet webforms as I have done up to now.
>
> So far my class "CabinetSpec.vb" has Update, Add, and Delete methods that
> work perfectly. However, I'm having problems with my "List" function,
> which I want to return some rows to fill a ListBox control on my webform.
> I'm not sure what the best way to do it is, but I set it up to return a
> DataSet object, which I then bind to my ListBox control.
>
> Everytime I add, update or delete a CabinetSpec on the webform, the
> ListBox containing the list of CabinetSpecs updates itself using this
> function:
>
> -------------------------------------------------------
> CabinetSpec.vb
>
> Public Function List() As DataSet
>    'return a list of RowIDs & Descriptions
>    mstrSQL = "SELECT CabinetSpecID, Description FROM CabinetSpecs ORDER BY
> Description"
>    Dim ds As New DataSet
>    Try
>        Connect()
>        Dim da As New SqlDataAdapter(mstrSQL, objConnection)
>        da.Fill(ds, "CabinetSpecs")
>    Catch ex As Exception
>        Throw ex
>    Finally
>        Disconnect()
>    End Try
>    Return ds
> End Function
> -------------------------------------------------------
>
> Then my webform binds the data to the ListView here:
>
> -------------------------------------------------------
> WebForm
>
>    Private Sub Fill_lstSpecs()
>        Try
>            Dim CS As New CabinetSpec
>            With lstSpecs
>                .SelectedIndex = -1
>                .DataSource = CS.List.Tables(0).DefaultView
>                .DataTextField = "Description"
>                .DataValueField = "CabinetSpecID"
>                .DataBind()
>            End With
>        Catch ex As Exception
>            lblError.Text = ex.Message
>        End Try
>    End Sub
> -------------------------------------------------------
>
> This works, and I can see the ListBox update itself everytime I Add,
> Update or Delete a record.
> The problem occurs when I leave the page and return - for some reason when
> the page loads Fill_lstSpecs() the ListBox no longer shows the most recent
> dataset. For example, I add a new CabinetSpec Description "test1".
> Fill_lstSpecs() fires and the ListBox now shows "test1". I leave the page
> and come back, Fill_lstSpecs() fires again, but now "test1" doesn't
> appear.
>
> Any diagnosis would be appreciated.
>
> Thanks,
> Jordan
>
>

Thread options