ASP.NET Tutorial/ADO.net Database/OleCommand
OleCommand and OleParameter
<source lang="csharp">
<%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.OleDb" %> <script runat="server">
void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { OleDbConnection myConnection = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="+ Server.MapPath("EmployeeDatabase.mdb;")); try{ string CommandTextPublisher = "SELECT ID, FirstName FROM Employee"; OleDbCommand myCommandPublishers = new OleDbCommand(CommandTextPublisher, myConnection); myConnection.Open(); DropDownList1.DataSource = myCommandPublishers.ExecuteReader(); DropDownList1.DataTextField = "FirstName"; DropDownList1.DataValueField = "ID"; DropDownList1.DataBind(); DropDownList1.Items.Insert(0, new ListItem("-- All Publishers --", "0")); } catch (Exception ex){ throw(ex); } finally{ myConnection.Close(); } } } void ApplyFilter_Click(Object sender, EventArgs e) { OleDbConnection myConnection = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="+ Server.MapPath("EmployeeDatabase.mdb;")); try{ string CommandTextBooks = "SELECT Book.BookTitle, Publisher.PublisherName FROM Book INNER JOIN Publisher on Book.BookPublisherID = Publisher.PublisherID WHERE ?=0 OR Book.BookPublisherID=? ORDER BY Book.BookTitle"; OleDbCommand myCommandBooks = new OleDbCommand(CommandTextBooks, myConnection); OleDbParameter myParameter1 = new OleDbParameter(); myParameter1.ParameterName = "@pub1"; myParameter1.OleDbType = OleDbType.Integer; myParameter1.Value = DropDownList1.SelectedValue; myCommandBooks.Parameters.Add (myParameter1); OleDbParameter myParameter2 = new OleDbParameter(); myParameter2.ParameterName = "@pub2"; myParameter2.OleDbType = OleDbType.Integer; myParameter2.Value = DropDownList1.SelectedValue; myCommandBooks.Parameters.Add (myParameter2); myConnection.Open(); DataGrid1.DataSource = myCommandBooks.ExecuteReader(); DataGrid1.DataBind(); } catch (Exception ex){ throw(ex); } finally{ myConnection.Close(); } }
</script> <html> <head> </head> <body>
<form runat="server"> Select a Publisher: <asp:DropDownList id="DropDownList1" runat="server"></asp:DropDownList> <asp:Button id="Button1" onclick="ApplyFilter_Click" runat="server" Text="Show Titles"></asp:Button> <asp:datagrid id="DataGrid1" runat="server" EnableViewState="False"> <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle> <ItemStyle backcolor="#DEDFDE"></ItemStyle> </asp:datagrid> </form>
</body> </html></source>