Loading data from SQL Server row by row to textboxes
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.
Code:
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
Re: Loading data from SQL Server row by row to textboxes
This will return all movie IDs and titles from movie_table ordered by id.
Code:
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:
Code:
SELECT [movie_ID], [movie_Title] FROM [Movie_Table] ORDER BY [MOVIE_ID] WHERE your condition here
i.e.
Code:
SELECT [movie_ID], [movie_Title] FROM [Movie_Table] ORDER BY [MOVIE_ID] WHERE movie_ID=17;
or
Code:
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
vb Code:
' 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.
Re: Loading data from SQL Server row by row to textboxes
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.