S
Stephan Bour
I need to implement a simple search function to lookup order information in
a SQLserver table. The code below works fine for a search to the ³OrderID²
column. However, I¹d like to offer the user the ability to select 3 possible
columns to search from. I added a dropdownlist to select the column. My
question is, how do I dynamically modify the SQL command <String selectCmd =
"select * from Orders where OrderID = @OrderID";> to take the input from the
dropdownlist?
Also, can I assign a different Value to <asp:listitem>Order
ID</asp:listitem> than ³Order ID²? I¹d like to display the parameter with a
space but have its Value = OrderID (no space) since that¹s the column name
in the SQL table.
Thank you,
Stephan.
<%@ Page Language="C#" ContentType="text/html" ResponseEncoding="iso-8859-1"
%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
public void GetOrder(Object sender, EventArgs E)
{
String selectCmd = "select * from Orders where OrderID = @OrderID";
SqlConnection myConnection = new SqlConnection("server =
'LMM-MB-DATA,25886';Database = 'Olig
rder'; Trusted_Connection=Yes");
SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd,
myConnection);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@OrderID",
SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@OrderID"].Value =
SearchString.Text;
DataSet ds = new DataSet();
myCommand.Fill(ds, "Orders");
MyDataGrid.DataSource= ds.Tables["Orders"].DefaultView;
MyDataGrid.DataBind();
}
</script>
<body style="font: 10pt verdana">
<form runat="server">
<p> Search previously placed order by:
<asp:dropdownlist ID="SearchSelect" runat="server">
<asp:listitem>Order ID</asp:listitem>
<asp:listitem>Last name</asp:listitem>
<asp:listitem>Comments</asp:listitem>
</asp:dropdownlist>
<asp:textbox id="SearchString" MaxLength="50" runat="server"
TextMode="SingleLine" />
</p>
<input type="submit" OnServerClick="GetOrder" Value="Get Orders"
runat="server"/><p>
<ASPataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</form>
</body>
</html>
a SQLserver table. The code below works fine for a search to the ³OrderID²
column. However, I¹d like to offer the user the ability to select 3 possible
columns to search from. I added a dropdownlist to select the column. My
question is, how do I dynamically modify the SQL command <String selectCmd =
"select * from Orders where OrderID = @OrderID";> to take the input from the
dropdownlist?
Also, can I assign a different Value to <asp:listitem>Order
ID</asp:listitem> than ³Order ID²? I¹d like to display the parameter with a
space but have its Value = OrderID (no space) since that¹s the column name
in the SQL table.
Thank you,
Stephan.
<%@ Page Language="C#" ContentType="text/html" ResponseEncoding="iso-8859-1"
%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
public void GetOrder(Object sender, EventArgs E)
{
String selectCmd = "select * from Orders where OrderID = @OrderID";
SqlConnection myConnection = new SqlConnection("server =
'LMM-MB-DATA,25886';Database = 'Olig
rder'; Trusted_Connection=Yes");SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd,
myConnection);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@OrderID",
SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@OrderID"].Value =
SearchString.Text;
DataSet ds = new DataSet();
myCommand.Fill(ds, "Orders");
MyDataGrid.DataSource= ds.Tables["Orders"].DefaultView;
MyDataGrid.DataBind();
}
</script>
<body style="font: 10pt verdana">
<form runat="server">
<p> Search previously placed order by:
<asp:dropdownlist ID="SearchSelect" runat="server">
<asp:listitem>Order ID</asp:listitem>
<asp:listitem>Last name</asp:listitem>
<asp:listitem>Comments</asp:listitem>
</asp:dropdownlist>
<asp:textbox id="SearchString" MaxLength="50" runat="server"
TextMode="SingleLine" />
</p>
<input type="submit" OnServerClick="GetOrder" Value="Get Orders"
runat="server"/><p>
<ASP
ataGrid id="MyDataGrid" runat="server"Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</form>
</body>
</html>