VB.NET Databinding

Hi

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.

Thanks,
Michael
LVL 1
Xavior2K3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GoodJunCommented:
Try to turn the vieststate of the grid off. call the grid.Databind() for each postback.
0
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?
0
pillbug22Commented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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?

Thanks
0
rushtosachinCommented:
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
                        Try
                            oTextBox.Text = aRow.Item("" & oTextBox.Tag & "")
                        Catch ex As Exception
                            MsgBox(ex.Message)
                        End Try
                    End If
                End If
            End If
        Next oSubControl
    End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

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.