ASP.NET Code in GridView

Question

I'm converting a classic ASP site to ASP.NET (C#) and there are several stored procedures that can be called from one page. Currently in the classic ASP the values such as the stored procedure name and other parameters are passed in the query string. I'm trying to determine a way of doing this in ASP.NET now as I cannot seem to use variables as the stored procedure name. What I tried to do was the following:

<% string sp = Request.QueryString["sp"]; %>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
    SelectCommand="<%= sp%>" SelectCommandType="StoredProcedure">

The second part of this is that of the stored procedures, there can be a different number of parameters needed. In my classic ASP I was passing the parameters over via query string but first checking to see if they were blank and if so, not to attempt to pass them as a parameter to the stored procedure. Here is how I was doing it in classic ASP:

If param1 <> "" then
    cmd.Parameters(i) = param1
    i=i+1
End If
If param2 <> "" then
    cmd.Parameters(i) = param2
    i=i+1
End If
    If param3 <> "" then
    cmd.Parameters(i) = param3
    i=i+1
End If
If param4 <> "" then
    cmd.Parameters(i) = param4
End If

I don't see a way to accomplish this in .NET either since I can't place code within the GridView. How can do accomplish both of these pieces?

Edit: I've solved the first half of my problem by doing the following in my Page_Load event as follows:

protected void Page_Load(object sender, EventArgs e)
{
    sp = Request.QueryString["sp"];
    SqlDataSource1.SelectCommand = sp;
}

Now the only remaining issue is how to only pass parameters with values assigned.

@VitorCanova as stated above, I see thats the case. My question was, how can I accomplish in .NET what I was doing in classic ASP for both of the examples. — aantiix
– aantiix, Commented Apr 15, 2015 at 19:28
It was just a random point in your code. Now I provided something maybe can help. — Vitor Canova
– Vitor Canova, Commented Apr 15, 2015 at 19:32
Have you looked into using LINQ To SQL - Your life will be easier. — PhillyNJ
– PhillyNJ, Commented Apr 15, 2015 at 19:36

aantiix · Accepted Answer · 2015-04-15 20:18:56Z

1

Problem solved. In the code behind:

protected void Page_Load(object sender, EventArgs e)
{
    sp = Request.QueryString["sp"];
    param1 = Request.QueryString["param1"];
    param2 = Request.QueryString["param2"];
    param3 = Request.QueryString["param3"];

    SqlDataSource1.SelectCommand = sp;

    if (param1 != null)
    {
        SqlDataSource1.SelectParameters.Add("param1", param1);
    }
    if (param2 != null)
    {
        SqlDataSource1.SelectParameters.Add("param2", param2);
    }
    if (param3 != null)
    {
        SqlDataSource1.SelectParameters.Add("param3", param3);
    }
}

In the aspx

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
    SelectCommandType="StoredProcedure">
</asp:SqlDataSource>

answered Apr 15, 2015 at 20:18

aantiix

5462 gold badges9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:14:09Z

0

I think this is not exactly what you want but can help:

<asp:SqlDataSource ID="DataSource1" runat="server" SelectCommand="Your SQL"
       ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    <SelectParameters>
        <asp:QueryStringParameter Name="sp" QueryStringField="sp"
            Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

In this case the SQL you provide will end up with the query string sp as a parameter.

Maybe you can do something like this:

EXEC (@sp);

I couldn't test it so take with a grain of salt. But i think you can elaborate more beyond this.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Apr 15, 2015 at 19:31

Vitor Canova

3,9965 gold badges36 silver badges55 bronze badges

Collectives™ on Stack Overflow

ASP.NET Code in GridView

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related