• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1286
  • Last Modified:

VB.NET Databinding


I have tens of thousands of records on a SQL Server database, and I wish to create an interface so an end user can edit the records and move back and forth between them,   very similarly to the interface in MS Access.  The end user will have the software locally and it will access the data on a DB Server over the internet.

I’m not sure how to approach the situation.  I tried to use the SqlDataAdapter and set the SelectCommand to return all the records, and then fill a dataset with the results and then bind each of my textboxes to a field in that dataset.

This works fine, however the time it takes first to initialise and display the first record, and then the time it takes to move back and forth between records is unacceptable even over a 256k link.

I then tried to manually generate a SQL statement to return the desired record when and where it is needed (i.e., move to next or last record).  This gives a much better response time, however when I fill my dataset with the record that the SQL statement returns, I the data bindings don’t refresh.  

I have checked and the correct data is in the dataset but it seems that the bindings aren’t picking up the change and updating the values in the textboxes.  I have tried to refresh the bindings using the CurrencyManager but it still does not work.  Is it to do with the Fill method that I'm using with the adapter?

I was wondering if there is another way of approaching the problem, or perhaps there is something I was doing wrong in my 2 attempts.  Either way is acceptable, but if I use the later one, how could I make the bound textboxes reflect the changes in the dataset?

Your help will be much appreciated.

1 Solution
Try to turn the vieststate of the grid off. call the grid.Databind() for each postback.
Xavior2K3Author Commented:
Sorry i might not have made myself clear im using textboxes instead of datagrids.  How could i refresh the textboxes to display the changed dataset?
Your button for next/previous would have to re-bind the textboxes each time.

I know you mentioned a slow response time for returning so many records at once - have you thought about displaying only the top-level (very basic, but record-specific identifiable) information, then letting the user select a record to show the details of that record?

That way, you are basically filtering the information, not pulling back 30 fields for each record when the user really only needs all 30 for the 1 record.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Xavior2K3Author Commented:
Thanks for the response, one problem i have though is that when the user selects a record they want to view more details about, i would get that particular record from the database and repopulate the dataset that the textboxes are bound to.  This is all ok but when i populate the dataset with the new data in, the textboxes dont change to the new data.  When i try to re-bind them it says they are already bound from when i bound them the first time.  Would i have to somehow clear the old bind and re-bind it again? If so how? Otherwise any suggestions?

i think u r concerned abt rebind the text boxes....do 1 thing.....put database field name in tag each of the text boxes...it's just one time task.....then you can use the following function....you just need to pass FORM NAME  AND DATATABLE AND IT'S ROW  POSITION (populate it n pass it whenever needed) as an argument.

        Public Sub BindControlsToTaggedData(ByRef Form_Name As Form, ByRef pors As DataTable, ByVal RowPOs As Int16)
        Dim oSubControl As Control
        Dim Field_Name As String
        Dim aRow As DataRow
        Dim aCol As DataColumn
        Dim oTextBox As TextBox
        For Each oSubControl In Form_Name.Controls
            If oSubControl.Controls.Count > 0 Then
                If TypeName(oSubControl) = "TextBox" Then
                    oTextBox = oSubControl
                    aRow = pors.Rows(RowPOs)
                    If oTextBox.Tag <> "" And oTextBox.Tag <> "N" Then
                            oTextBox.Text = aRow.Item("" & oTextBox.Tag & "")
                        Catch ex As Exception
                        End Try
                    End If
                End If
            End If
        Next oSubControl
    End Sub
Xavior2K3Author Commented:
Thanks for that works like a charm.  I did have to edit it as the first if statment inside the for loop resulted in false so i got rid of it.  What was its purpose?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now