Using ASP.net variable in SQL query

Question

Still in the newbie stage and have a project at work. I've searched google and read till my eye's have bleed. There has to be something small I'm overlooking. I'm needing to pass a variable "dNum" into the SQL query. If I don't put in the "SelectParameters" it'll tell me I need to declare the scalar variable, which makes sense. I can't get it to pick up the variable.. the default value does work.

Any pointers? Thanks in advance.

<asp:TextBox ID="dNumber" runat="server"></asp:TextBox><asp:Button ID="Button1" runat="server" Text="Go!" OnClick="runQuery" />    
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
           ConnectionString="<%$ ConnectionStrings:Door_Clearance %>" 

       SelectCommand="SELECT a.Name, c.Name, c.Description 
        FROM [SWHSystem].[dbo].[Door] AS a
        JOIN [SWHSystem].[dbo].[ClearanceItem] AS b ON a.ObjectID=b.DoorID
        JOIN [SWHSystem].[dbo].[Clearance] AS c ON b.ClearanceID=c.ObjectID
           WHERE a.Name LIKE @dNum1">    


        <SelectParameters> <asp:SessionParameter Name="dNum1"  
        SessionField="dNum"  
         DefaultValue='%1500%'/></SelectParameters>
        </asp:SqlDataSource>

In the .cs file..

protected void runQuery(object sender, EventArgs e)  
    {   
        string dNum = "'%" + dNumber.Text + "%'";  
        //SqlDataSource1.SelectParameters.Add("dNum1", dNum);  
        SqlDataSource1.SelectParameters["dNum1"].DefaultValue = "dNum";  

    }

Try using a ControlParameter instead and have it reference the dNumber control. Currently you are using a SessionParameter which will try to fetch yhe value from the Session. — user1429080
– user1429080, Commented Nov 24, 2015 at 19:41
Tried several variations and never could get it to work.. end up moving it to the .cs code and it's working finally. — E. Smith
– E. Smith, Commented Nov 28, 2015 at 16:04

morgb · Accepted Answer · 2015-11-25 19:16:02Z

2

First, you should use a stored procedure for your query:

CREATE PROCEDURE dbo.SelectDoor
    @dNum1 NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

SELECT a.Name, c.Name, c.Description 
        FROM [SWHSystem].[dbo].[Door] AS a
        JOIN [SWHSystem].[dbo].[ClearanceItem] AS b ON a.ObjectID=b.DoorID
        JOIN [SWHSystem].[dbo].[Clearance] AS c ON b.ClearanceID=c.ObjectID
           WHERE a.Name LIKE '%'+@dNum1+'%'
END;

Next, call the stored procedure in your cs file something like this:

using (SqlConnection connection = new SqlConnection(<your connection string>))
{
    using (SqlCommand scmd = new SqlCommand("[dbo].[SelectDoor]", connection))
    {
        connection.Open();
        scmd.CommandType = CommandType.StoredProcedure;
        scmd.Parameters.AddWithValue("@dNum1 ", dNumber.Text);
        //do something
        connection.Close();                   
    }
}

You can also connect your datasource in the markup something like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Door_Clearance %>" SelectCommand="SelectDoor" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="dNumber" Name="dNum1" PropertyName="Text" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Then you can just databind whatever control uses this datasource in the codebehind (.cs) file.

edited Nov 25, 2015 at 19:16

answered Nov 24, 2015 at 18:46

morgb

2,3322 gold badges14 silver badges14 bronze badges

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

2 Comments

Khazratbek Over a year ago

when you changing some programming style with another, show it fully please. You also have to show attaching this procedures result to sqldatasource and databind, caz' may be he don't know exactly how to do that.

E. Smith Over a year ago

@morgb. I did end up moving all the code to the .cs file. I had most of my files from class so wasn't as time consuming as I expected. I didn't use a stored procedure though just left the query string for mobility reasons. At this time it's working great. Thanks! .. Will look at posting the final code on Monday.

Collectives™ on Stack Overflow

Using ASP.net variable in SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related