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?

You're using a standard HTML button. Try an <asp:Button> control with OnClick="BtnGo_Click" defined on it. — Tim
– Tim, Commented May 12, 2014 at 20:03
I used the code runat="server" which should have the same effect. But still nothing. :( — SearchForKnowledge
– SearchForKnowledge, Commented May 12, 2014 at 20:08
@Tim WOW... Although I used to runat=server" that didn't do anything but the <asp:Button> fixed it. Thanks... Now can you tell me if you know how to get the strLocation to be what the dropdown value is? — SearchForKnowledge
– SearchForKnowledge, Commented May 12, 2014 at 20:12

Tim · Accepted Answer · 2014-05-12 20:16:32Z

2

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) + "' ..."

answered May 12, 2014 at 20:16

Tim

4,10910 gold badges39 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Why the SQL command is not executing

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related