Web.config values passed to sp for gridview

frankm9639 · May 16th, 2009, 06:44 PM

Hi, I want to grab two values from the web.config and pass them into a stored procedure along with the values from two dropdownlists. Here is what I have in mind but it doesn't seem to work. I want the user to select both ddl values, then hit the go button and the stored procedure is called with four parameters and populates the gridview. Also, will this autopopulate the gridview? I don't want it to, if I use the front-end to set this up, how is the gridview load called? Thanks.

Here is the front-end.

Code:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="sproc_Report"SelectCommandType="StoredProcedure"
DataSourceMode="DataSet">
<SelectParameters>
<asp:ParameterName="AString"Type="String"/>
<asp:ParameterName="BString"Type="String"/>
<asp:ControlParameterControlID="ddl_A"DefaultValue=""Name="my_id"PropertyName="SelectedValue"Type="String"/>
<asp:ControlParameterControlID="ddl_B"DefaultValue=""Name="my_area"PropertyName="SelectedValue"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>
<asp:GridViewID="GridView1"runat="server"
DataSourceID="SqlDataSource1"
AllowPaging="true"
AutoGenerateColumns="true">
</asp:GridView>

Here is the code from the back-end:

Code:

protectedvoid Page_Load(object sender, System.EventArgs e)
{
String AString = ConfigurationManager.AppSettings["str_db_name"];
String BString = ConfigurationManager.AppSettings["str_db_transact_name"];

Lee Dumond · May 17th, 2009, 01:40 PM

The code isn't finished. You seem to be retrieving the values from web.config correctly, but you have not set the parameters in your SqlDataSource to those values.

Code:

protected void Page_Load(object sender, System.EventArgs e)
{
   String AString = ConfigurationManager.AppSettings["str_db_name"];
   String BString = ConfigurationManager.AppSettings["str_db_transact_name"];
 
   SqlDataSource1.SelectParameters["AString"].DefaultValue = AString;
   SqlDataSource1.SelectParameters["BString"].DefaultVaue = BString;
}

frankm9639 · May 17th, 2009, 05:38 PM

Thanks! Do I do the same for the two controlparameters below in the back-end code? Also, I want the gridview to be empty until the user clicks the go button. Should I put all of the back-end code into the buttonclick event instead of pageload?

Code:

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="sproc_Report"SelectCommandType="StoredProcedure"
DataSourceMode="DataSet">
<SelectParameters>
<asp:ParameterName="AString"Type="String"/>
<asp:ParameterName="BString"Type="String"/>
<asp:ControlParameterControlID="ddl_A"DefaultValue=""Name="my_id"PropertyName="SelectedValue"Type="String"/>
<asp:ControlParameterControlID="ddl_B"DefaultValue=""Name="my_area"PropertyName="SelectedValue"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>

Lee Dumond · May 17th, 2009, 08:35 PM

Yes, just don't set the DataSourceID of the GridView declaratively, Instead, set the DataSourceID in the button's click event handler, along with the two parameters derived from web.config.

There should be no need to touch the two ControlParameters, as they will be getting their values from controls that are already on the page.

Code:

protected void btnGo_Click(object sender, EventArgs e)
{
   GridView1.DataSourceID = "SqlDataSource1";
      
   String AString = ConfigurationManager.AppSettings["str_db_name"];
   String BString = ConfigurationManager.AppSettings["str_db_transact_name"];
 
   SqlDataSource1.SelectParameters["AString"].DefaultValue = AString;
   SqlDataSource1.SelectParameters["BString"].DefaultVaue = BString;
}

frankm9639 · May 17th, 2009, 09:24 PM

I'm kinda new to this, so I was wondering if you can clear my head for me.

I think you're saying, that the two asp:Parameter's need code-behind code to handle getting values to them, but the two asp:ControlParameter's do not require any code-behind code because the controls pre-exist on the page already and the two web.config values do not pre-exist on the page. The web.config values must be provided, and the control values are "automatically" supplied.

I'm used to setting up a SqlDataReader and calling ExecuteReader and doing a DataBind. The method I'm trying now is new to me. Do I have to do something to fill the GridView? Or is it automatic in this method with the buttonclick event?

Thanks for your help!

Lee Dumond · May 17th, 2009, 10:20 PM

Quote:

Originally Posted by frankm9639

I think you're saying, that the two asp:Parameter's need code-behind code to handle getting values to them, but the two asp:ControlParameter's do not require any code-behind code because the controls pre-exist on the page already and the two web.config values do not pre-exist on the page. The web.config values must be provided, and the control values are "automatically" supplied.

Yep.

Quote:

I'm used to setting up a SqlDataReader and calling ExecuteReader and doing a DataBind. The method I'm trying now is new to me. Do I have to do something to fill the GridView? Or is it automatic in this method with the buttonclick event?

Thanks for your help!

When you use a data source like SqlDataSource for example, the binding takes place automatically. The GridView will fill itself with the values from the data source control. If you set the DataSourceID in the markup, it will happen when the page loads. You said you didn't want to do that, but wanted to only fill the GridView on a button click. My suggestion was to set the DataSourceID in the button click. That way, when the page first loads the GridView will be empty, because it has no data source. When you click the button and the page posts back, it will pick up the DataSourceID that is set in that event handler, and then when the page loads again from that postback it will fill the GridView.

Go ahead and try it. See what happens.

frankm9639 · May 19th, 2009, 06:54 PM

Great! Thanks for your help. :)

Lee Dumond · May 19th, 2009, 09:24 PM

Quote:

Originally Posted by frankm9639

Great! Thanks for your help. :)

You're welcome.