Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the pubs database with SQL Server and save it as a ‘.aspx’ page.
This code will, upon loading the page, fill a DropDown List with the States from the authors table. Then, based on the search criteria, will search for all authors from the state chosen, displaying all items in a Datagrid.
<%@ Page Debug=”False” Trace=”False” %>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SqlClient” %>
<html>
<script language=”VB” runat=”server”>
Sub Page_Load(Sender As Object, E As EventArgs)
if not Page.IsPostBack then
doData()
end if
End Sub
Sub doQuery(Source as Object, E as EventArgs)
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
Dim strState as string
strState=state.SelectedItem.Text
‘label1.text=”State queried is : ” & strState
Label1.Text=”You chose: ” + strState
Dim MySQL as string
MySQL=”select * from Authors where state=’” & strState & “‘”
panel1.visible=”true”
label2.text=MySQL
MyConnection = New SqlConnection(“server=localhost;uid=userid;pwd=pwd;database=pubs;”)
MyCommand = New SqlDataAdapter(MySQL, MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, “Authors”)
MyDataGrid.DataSource=ds.Tables(“Authors”).DefaultView
MyDataGrid.DataBind()
End Sub
Sub doData()
Dim strConn as string =”server=localhost;database=pubs;uid=sa;pwd=password”
Dim Conn as new SQLconnection(strConn)
Dim strSQL as string =”select distinct state from authors”
Dim Cmd as New SQLCommand(strSQL,Conn)
Conn.Open()
state.DataSource = Cmd.ExecuteReader()
state.DataBind()
End Sub
</script>
<body>
<h3><font face=”Verdana”>Search & Database – Display Results in a DataGrid Control</font></h3>
<Form id=”form” runat=”server”>
<asp:DropDownList id=”state” datatextfield=”state” runat=”server” />
<asp:Button id=”button” Text=”Select State” onclick=”doQuery” runat=”server” />
</Form>
<asp:Panel ID=”Panel1″ runat=”server” visible=”False”>
<hr>
<div align=”center”><b><i>Search Results</i></b>
<asp:Label ID=”label1″ runat=”server” /><br>
<asp:Label ID=”label2″ runat=”server” /><p>
<ASP:DataGrid 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”
/>
</div>
</asp:Panel>
</body>
</html>