Loading data from SQL Server row by row to textboxes

[k80sg]

Hi everyone, I am trying to retrieve data row by row from my SQL Server and load them into my respective textboxes, I was doing the below code but of course it doesn't work as the For Each loop will load every single textboxes with each data retrieved, ran out of ideas. Appreciate if someone can give me a boost here. Thanks.

   Private Sub retrieve_Data()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        ' Dim icounter As Integer = 1
        Try
            con.ConnectionString = "Data Source=HPEnvy-HP; Initial Catalog=Cinema; User Id=sa; Password=8034615h;"
            con.Open()
            cmd.Connection = con

            cmd.CommandText = "SELECT [movie_ID], [movie_Title] FROM [Movie_Table] ORDER BY [MOVIE_ID] "

            Dim lrd As SqlDataReader = cmd.ExecuteReader()

            While lrd.Read()

                Dim reader As String = lrd(1).ToString
                Dim arrLoad As New ArrayList


                arrLoad.Add(lrd(1).ToString)
                For i = 0 To arrLoad.Count - 1
                    For Each cCtrl As Control In Panel1.Controls
                        If TypeOf cCtrl Is TextBox Then
                            Dim txtBox As New TextBox
                            txtBox = cCtrl
                            txtBox.Text = arrLoad.Item(i)
                        End If
                    Next
                Next

    End While


        Catch ex As Exception
            MessageBox.Show("Error while retrieving records on table..." & ex.Message, "Load Records")
        Finally
            con.Close()
        End Try
    End Sub

[cicatrix]

This will return all movie IDs and titles from movie_table ordered by id.

SELECT [movie_ID], [movie_Title] FROM [Movie_Table] ORDER BY [MOVIE_ID]

if you need to get only one record, you should provide WHERE clause to your query:

SELECT [movie_ID], [movie_Title] FROM [Movie_Table] ORDER BY [MOVIE_ID] WHERE your condition here

i.e.

SELECT [movie_ID], [movie_Title] FROM [Movie_Table] ORDER BY [MOVIE_ID] WHERE movie_ID=17;

or

SELECT [movie_ID], [movie_Title] FROM [Movie_Table] ORDER BY [MOVIE_ID] WHERE movie_Title='Batman returns';

This section doen't make any sense to me.
I'll comment it, perhaps you'll get more sense out of it

' lrd(1) would be movie_Title so reader will have the movie title from the current record
Dim reader As String = lrd(1).ToString' i.e. "Armageddon"
Dim arrLoad As New ArrayList
arrLoad.Add(lrd(1).ToString)' You add this title to the array list (by the way, ArrayList is Obsolete, use List or Dictionary
 
 
' You iterate through each textbox on the Panel
' And you do it with each record in the Movie_Table, i.e. if you have 500 movie titles there
' then the following cycle will repeat 500 times:
For Each cCtrl As Control In Panel1.Controls
    If TypeOf cCtrl Is TextBox Then
        Dim txtBox As New TextBox ' dont use New here, you don't need it.
        txtBox = cCtrl
        txtBox.Text = arrLoad.Item(i) ' Each textbox on the panel will have the same value.
    End If
Next

On the second look, I gather that you want to create a textbox for an each movie title. What will you do if you have 1,000 movies, 10,000?

I suggest doing the same thing with DataGridView. It will be more consistend and easier for you.

[jmcilhinney]

I would suggest populating a DataTable, either using a DataAdapter or a DataReader. You can then bind that to a BindingSource and then bind that to one or more controls.

If you want to display multiple records at a time then you should do as suggested and use a DataGridView. Alternatively, you can add as many TextBoxes and/or other controls as required to display ONE record at a time.

You can navigate through the records using the BindingSource, e.g. MovePrevious and MoveNext methods. You can also provide a UI for navigation using a BindingNavigator.

It's also worth noting that, if you use the Data Sources window, all this will be done for you simply by dragging a table onto the form.

If you really want to do it for yourself, follow the CodeBank link in my signature and check out my Retrieving & Saving Data thread to see how to populate the DataTable, if you need it.