Why the SQL command is not executing

Question

I have the following code in my C# code-behind page to execute on button press:

String cString;
    SqlConnection Conn;
    protected void Page_Load(object sender, EventArgs e)
    {

    cString = "Data Source=server;Initial Catalog=database;User Id=user12;Password=pass12;";
    }

    protected void btnGo_Click(object sender, EventArgs e) {
        Conn = new SqlConnection(cString);
        Conn.Open();
        string sqlCode = 
        "DECLARE @strLocation varchar(200) SET @strLocation = 'Ridge Hill' SELECT [content_title] AS [Physician Name], CAST([content_html] AS XML).query('/root/Physicians/picture/img') AS [Image], CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office1/a') AS [Office1], CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office2/a') AS [Office2], CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office3/a') AS [Office3], CAST([content_html] AS XML).query('/root/Physicians/OfficeLocations/office4/a') AS [Office4] FROM [datbase].[dbo].[content] WHERE    [folder_id] = '188' AND (content_html LIKE '%'+@strLocation+'%') ORDER BY [content_title]";
        /* EXECUTE AND DISPLAY THE DATA IN THE ASP PAGE */


        using(SqlCommand command = new SqlCommand(sqlCode, Conn)) {
            command.CommandType = CommandType.Text;

            using (SqlDataReader reader = command.ExecuteReader()) {
                rptContent.DataSource = reader;
                rptContent.DataBind();
            }
        }
    }

The following code in my ASPX page:

<select ID="slcLocation" runat="server" ClientIDMode="Static">
    <option value="noSelect" DISABLED SELECTED>-= Select a Location =-</option>
    <option value="all">All Location</option>
    <option value="Westchester">Westchester Avenue</option>
    <option value="Theall Road">Theall Road</option>
    <option value="Davis Avenue">Davis Avenue</option>
    <option value="Market Street">Market Street</option>
    <option value="Ridge">Ridge</option>
    <option value="Overhill Road">Overhill Road</option>
    <option value="Westchester Avenue">Westchester Avenue</option>
</select>
<input type=button ID="btnGo" ClientIDMode="Static" value="Go" />
<br />
<div style="padding-left: 45px;">
    <asp:Repeater runat="server" ID="rptContent">
        <HeaderTemplate>
            <table border="0" ID="tblInfo" style="display: none;" ClientIDMode="Static">
                <tr>
                    <td>Physician Name</td>
                    <td></td>
                    <td>Office1</td>
                    <td>Office2</td>
                    <td>Office3</td>
                    <td>Office4</td>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td><%# Eval("Physician Name").ToString() %></td>
                <td><%# Eval("Image").ToString() %></td>
                <td><%# Eval("Office1").ToString() %></td>
                <td><%# Eval("Office2").ToString() %></td>
                <td><%# Eval("Office3").ToString() %></td>
                <td><%# Eval("Office4").ToString() %></td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>
</div>

Two questions:

• How can I change this query line: SET @strLocation = 'Ridge Hill' to use the VALUE from the Dropdown box?
• When I click the GO button, nothing happens :/ How can I fix it?

Answer 1

You need to change your dropdownlist to an ASP:DropDownList with ListItems in it.

Then you'd replace 'RIDGE HILL' with '" + slcLocation.SelectedItem.Value (or Text) + "' ..."