locked
How to get data from SqlDataSource to populate non- data controls RRS feed

  • Question

  • User-1453299906 posted

    Hi there,

    I have SqlDataSource and GridView defined and the the GridView can display data properly. However I have some labels and textboxes need to display certain field values from the same SqlDataSource.

    From MSDN, they have examples showing by casting the (SqlDataReader)SqlDsInvoiceHead.Select() method, for instance, you may retrieve those data like this:

    txbxInvoiceNum.Text = sqlDR["InvoiceNum"].ToString();

    I have succeeded in doing that, but those assignments only work when the web page posted back. They do not work when putting within the if (!Page.IsPostBack) block.

    protected void Page_Load(object sender, EventArgs e) 
    { 
            if (!Page.IsPostBack) 
            { 
                    using (SqlDataReader sqlDr = (SqlDataReader)SqlDsInvoice.Select(DataSourceSelectArguments.Empty)) 
                    { 
                            if (sqlDr.Read()) 
                            { 
                                    litInvoiceNum.Text = sqlDr["InvoiceNumber"].ToString(); 
                                    tbxInvoiceDate.Text = sqlDr["InvoiceDate"].ToString(); 
                                    lblAccountNum.Text = sqlDr["AccountNumber"].ToString(); 
                            } 
                    } 
            } 
    }

    (Please not that this same piece of code would work under the else { } block.)

    Can anyone confirm if and how I can assign SqlDataSource data values to non- data controls at first page load, please?

    Thanks!

    Wednesday, January 2, 2013 5:01 PM

Answers

  • User2034251845 posted

    This code looks good to me. Do you have enable Viewstate. What is happend the first time your grid and your other controls get the information, hoever if you have a button which generate a postback the whole page cylce is going to start but your page wont use the data reader again so only the controls with view state are going to keep the information.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 2, 2013 5:57 PM
  • User-1453299906 posted

    Whoa, I was not aware that some cheeky people could mark their own reply "as answer" while their contributions were just repeating others. Better mark the real contributors to make sure...

    Gridview with SqlDataSource controls are a perfect solutions for many business situations and for rapid development, especially since .Net 4. They become so flexible and feature-rich. If you have been using SqlCommand and all sort of code behind programming manually for the last decade, you would have a hard time to adopt these new controls. You should learn better.

    Thanks to sinedyip and oned_gk for inspiring me to look harder into the viewstate and the control's html code. I was planning to create a clean project with just the Gridview, SqlDataSource, and Textbox controls to prove to you guys the controls were problematic, but then it actually worked - the Textbox did get data from the SqlDataSource in the first load! So I looked back to locate the differences between the codes and found the extra caching definitions in my original SqlDataSource control code. I deleted them to make it the same as the new code but it still didn't work. As then I have seen the SqlDataSource did work with Textboxes, I had more confident to make it work.

    Finally, I removed the whole SqlDataSource control object and recreated it with default settings and my original project worked! I added back the cache settings and it still worked. That's weird. However, over the years I have found a few times controls added using Visual Studio's Designer graphical interfaces would introduce hidden codes that break mysteriously in unexpected situations. I think this is just another case of that sort.

    So thanks everyone. I would say this is just a one time Designer glitch. The SqlDataSource control is fine.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 13, 2013 10:27 AM

All replies

  • User2034251845 posted

    This code looks good to me. Do you have enable Viewstate. What is happend the first time your grid and your other controls get the information, hoever if you have a button which generate a postback the whole page cylce is going to start but your page wont use the data reader again so only the controls with view state are going to keep the information.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 2, 2013 5:57 PM
  • User-1716253493 posted

    Are you using other control value as parameter like textbox text? If yes, you will not get it work in if (!ispostback) condition because the text is still empty. So you need to remove if (!ispostback).

    Wednesday, January 2, 2013 7:33 PM
  • User-1453299906 posted

    I forgot to tell clearly the error - the SqlDR is NULL with the above code when the page is first loaded. It can only get data from the SqlDsInvoice object during subsequent post backs. My other GridView controls get the data from the SAME SqlDsInvoice object properly at first load, as it should be.

    Apparently, the SqlDataSource object (SqlDsInvoice) is initialized some time after the page_load event. I am not sure when the initialization takes place exactly. I have tried with other page_events but none worked.

    Therefore the more precise question is - where can I call the SqlDataReader object so that it can get data from the SqlDataSource object and display values on the page first loading?

    Wednesday, January 2, 2013 11:32 PM
  • User2034251845 posted

    I didnt get very well what is happen with your issue. Did you try on LoadComplete event?

    and you could try something like:

    if(SqlDsInvoice.Select(DataSourceSelectArguments.Empty != null))

    fill your controls...

    Thursday, January 3, 2013 12:35 AM
  • User-1716253493 posted

    Usualy i do like this

                DataTable dt = ((DataView)SqlDsInvoice.Select(DataSourceSelectArguments.Empty)).Table;
                litInvoiceNum.Text = dt.Rows[0]["InvoiceNumber"].ToString();
                tbxInvoiceDate.Text = dt.Rows[0]["InvoiceDate"].ToString();
                lblAccountNum.Text = dt.Rows[0]["AccountNumber"].ToString();


    or

                DataTable dt = ((DataView)SqlDsInvoice.Select(DataSourceSelectArguments.Empty)).Table;
                litInvoiceNum.Text = dt.Rows[0][0].ToString();
                tbxInvoiceDate.Text = dt.Rows[0][1].ToString();
                lblAccountNum.Text = dt.Rows[0][2].ToString();

    Thursday, January 3, 2013 1:16 AM
  • User3866881 posted

    Hello,

    I suggest you using SqlCommand instead of a SqlDataSource, something like this below:

    protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { using(SqlCommand cmd = new SqlCommand("Your SQL Select statement",new SqlConnection("Connection String"))) { cmd.Connection.Open(); if (sqlDr.Read()) { litInvoiceNum.Text = sqlDr["InvoiceNumber"].ToString(); tbxInvoiceDate.Text = sqlDr["InvoiceDate"].ToString(); lblAccountNum.Text = sqlDr["AccountNumber"].ToString(); } } } }
    Thursday, January 3, 2013 2:31 AM
  • User-1453299906 posted

    Yes, I have tried LoadComplete( ), same result - null return.

    The thing is - SqlDsInvoice.Select(DataSourceSelectArguments.Empty) actually returns null in all the page_events I have tried.

    Thursday, January 3, 2013 10:35 AM
  • User-1716253493 posted
    can you post your SqlDsInvoice html code?
    Thursday, January 3, 2013 10:39 AM
  • User-1453299906 posted

    Yes, I have tried using DataView as well. It's the same error.

    It is not the SqlDataReader or DataView objects' problem. It is that SqlDsInvoice.Select(DataSourceSelectArguments.Empty) returns null in code behind when the page first load. The SqlDsInvoice control itself should not be a problem since the GridView links to this SqlDsInvoice control display data correctly on page first load.

    Thursday, January 3, 2013 10:40 AM
  • User-1453299906 posted

    Yes, I know I can use SqlCommand object as I have been using them more often than SqlDataSource objects. The thing is I have data loaded with this SqlDsInvoice and displayed properly in a GridView. I don't want to use another call with SqlCommand to the server to retrieve the same data set again. However, if I cannot solve this mystery today, I might just go ahead to submit an extra SqlCommand call. Thanks.

    Thursday, January 3, 2013 10:45 AM
  • User-1453299906 posted

    Here you go, the SqlDataSource HTML code:

    <asp:SqlDataSource ID="SqlDsInvoice" runat="server" ConnectionString="<%$ ConnectionStrings:SQL01Connection %>" SelectCommand="INV_GetInvoice" SelectCommandType="StoredProcedure" DataSourceMode="DataReader" CacheDuration="60" CacheExpirationPolicy="Sliding">
    	<SelectParameters>
    		<asp:QueryStringParameter Name="InvoiceNum" QueryStringField="InvId" Type="Int32" />
    	</SelectParameters>
    </asp:SqlDataSource>
    

    Thursday, January 3, 2013 10:51 AM
  • User3866881 posted

    If you insist using SqlDataSource……It returns you a DataTable instead of a DataReader。

    The Select method returns a DataView object if the DataSourceMode property is set to the DataSet value. The Select method returns a IDataReader object if the DataSourceMode property is set to the DataReader value. Close the IDataReader object when you have finished reading the data.

    For more you can refer this:

    DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);    
    int reorderedProducts = (int)dv.Table.Rows[0][0];

    Thursday, January 3, 2013 9:06 PM
  • User-1453299906 posted

    You made it sound like using SqlDataSource is evil... :)

    Thanks, but I have already tested the DataView approach before and after oned-gk suggested yesterday. It failed with the same reason I stated afterward.

    It might have something to do with my querystring supplying the parameters to the SqlDataSource ... need some more tests.

    Friday, January 4, 2013 12:56 PM
  • User3866881 posted

    You made it sound like using SqlDataSource is evil... :)

    Not absolute, but for you situation, it might be.

    Friday, January 4, 2013 10:34 PM
  • User-1453299906 posted

    Whoa, I was not aware that some cheeky people could mark their own reply "as answer" while their contributions were just repeating others. Better mark the real contributors to make sure...

    Gridview with SqlDataSource controls are a perfect solutions for many business situations and for rapid development, especially since .Net 4. They become so flexible and feature-rich. If you have been using SqlCommand and all sort of code behind programming manually for the last decade, you would have a hard time to adopt these new controls. You should learn better.

    Thanks to sinedyip and oned_gk for inspiring me to look harder into the viewstate and the control's html code. I was planning to create a clean project with just the Gridview, SqlDataSource, and Textbox controls to prove to you guys the controls were problematic, but then it actually worked - the Textbox did get data from the SqlDataSource in the first load! So I looked back to locate the differences between the codes and found the extra caching definitions in my original SqlDataSource control code. I deleted them to make it the same as the new code but it still didn't work. As then I have seen the SqlDataSource did work with Textboxes, I had more confident to make it work.

    Finally, I removed the whole SqlDataSource control object and recreated it with default settings and my original project worked! I added back the cache settings and it still worked. That's weird. However, over the years I have found a few times controls added using Visual Studio's Designer graphical interfaces would introduce hidden codes that break mysteriously in unexpected situations. I think this is just another case of that sort.

    So thanks everyone. I would say this is just a one time Designer glitch. The SqlDataSource control is fine.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 13, 2013 10:27 AM