March 30, 2005 at 11:24 am
Ok, I am populating a page with data from four different tables. I sarted out using a datalist and selected all the data I need with a stored procedure. Then I discovered that three of the tables have more than one record for each userID. This caused my datalist to display all of my data over and over again. I would like to use a repeater for the tables that have repeating records but then I am confused on how to display the page if:
-table 1 has only 1 record per userID
-table 2 has more than one record per userID
-table 3 has more than one record per userID
-table 4 has more than one record per userID
...and my page is layed out something like this
some information from table 1 then information from table 3 then some more information from table 1 then information from table 2 then some more information from table 1 then information from table 4 then some more information from table 1
Is there a way to keep all of table 1 in a datalist and then just pop in as asp:repeater for tables 2-4?
this is how I am doing the datalist for table 1
GlobalConnection.Open()
dtrProfile = cmdSelect.ExecuteReader()
dlstProfile.DataSource = dtrProfile
dlstProfile.DataBind()
dtrProfile.Close()
this is how I am doing the code for the asp:repeater
GlobalConnection.Open()
dtrPositions = cmdPositions.ExecuteReader()
dlstPositions.DataSource = dtrPositions
dlstPositions.DataBind()
dtrPositions.Close()
I just want to make sure I am doing this the most efficient way.
March 31, 2005 at 7:20 am
Instead of using a dataReader, you can place the data in Table 1 into a DataSet and then you only need to call that one time and the data in the dataset can be reused until your hearts content without making any additional calls to your SQL Machine. On pages that I have with multiple database calls that populate different forms, I make one stored procedure and have multiple SELECT statments in it and then just dump all of the results into a DataSet and then parse through the tables in the dataset. It saves on connections to the db and is easier for me (at least) to manage.
March 31, 2005 at 8:40 am
Thank you, this sounds like a great solution.
March 31, 2005 at 12:39 pm
I have been working on all morning and I am stuck.
This is my stored procedure
(
@ClientID INT
)
AS
SELECT dbo.User_Information.User_ID, dbo.User_Information.First_Name, dbo.User_Information.Last_Name, dbo.User_Information.Email_1,
dbo.User_Information.Address_1, dbo.User_Information.Address_2, dbo.User_Information.City, dbo.User_Information.State_Province,
dbo.User_Information.Postal_Code, dbo.User_Information.Home_Phone, dbo.User_Information.Work_Phone, dbo.User_Information.Cell_Phone
FROM dbo.User_Information
WHERE dbo.User_Information.User_ID = @ClientID
SELECT User_ID, Company_Name
FROM dbo.MySurvey_Positions
Where dbo.MySurvey_Positions.User_ID = @ClientID
I just did a datagrid in my asp.net page to see what data was stored in my dataset and it only shows the results from the first select statement.
Dim dstLobbyDoc As DataSet = New DataSet
Dim dtrProfile As SqlDataAdapter = New SqlDataAdapter
Dim strGlobal As String = ConfigurationSettings.AppSettings("Global")
Dim GlobalConnection As New SqlConnection(strGlobal)
Dim cmdSelect As New SqlCommand("LobbyDoc", GlobalConnection)
cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add("@ClientID", 208466)
dtrProfile.SelectCommand = cmdSelect
dtrProfile.Fill(dstLobbyDoc, "lobbyDoc")
dgrdProfile.DataSource = dstLobbyDoc
dgrdProfile.DataBind()
Did I miss something?
April 1, 2005 at 11:12 am
You may want to actually go back to using the DataReader on this one, then you can take advantage of the NextResult method of the Datareader.
April 1, 2005 at 11:24 am
You may want to also take out the label in your fill method, there by only calling:
DataAdaptor.Fill(ds)
I believe this will also fill your dataset with all of the results instead of just one table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply