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?
