Hello Richard,
Thanks alot for the reply.Actually what prompted me into trying do create a page with such functionality is an example which i saw in a book am studing resently(Beginning Visual Basic .NET Database Programming by Denise Gosnell,Matthew Reynolds & Bill Forgey).The example stated in the book was on windows Application.
I have tried it with windows application and it worked .But i am trying to do it on web application.
This is what i have done so far:
I used two classes "frmsearchproducts and clsDatabase".
After building my web Application with all the web controls and codes behind the page, when i click search botton
and try to display the results on that datagrid, my datagrid will not show, so no result will be displayed.Funny
enough! when i click search under the window application it displays easily.
I will appreciate it if you can correct me where i have gone wrong.
Here is the code behind the page:
Public Class frmSearchProducts
Protected Const PROD = "Products"
Protected Const SUPP = "Suppliers"
Protected Const CONN = "user id=sa;password=;initial " & _
"catalog=Northwind;server=xuzia"
Protected dsData As DataSet
Protected dsResults As DataSet
Protected adapterResults As New SqlClient.SqlDataAdapter
Private Sub cboSearchMethod_SelectedIndexChanged(ByVal sender As _
System.Object, ByVal e As System.EventArgs) Handles _
cbosearchmethod.SelectedIndexChanged
Try
'if the user wants to search by Suppliers, then open the
'Suppliers form.
'otherwise, it will just remain on the Products search as it is
'now.
If cboSearchMethod.Text = SUPP Then
Dim frmSuppliers As New frmSearchSuppliers
frmSuppliers.Show()
End If
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub
Private Sub frmSearchProducts_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
'populate the search method drop down list
AddSearchMethod()
'set products as the default selected (since this is the
'Products search form
cbosearchmethod.Text = PROD
'populate the drop-down lists on the Products
'with the proper values
'Product Id
AddNumericDropDownCriteria(cbocriteria1)
'Product Name
AddCharDropDownCriteria(cbocriteria2)
'Supplier Company Name
AddCharDropDownCriteria(cbocriteria3)
'Category Name
AddCharDropDownCriteria(cbocriteria4)
'Unit Price
AddNumericDropDownCriteria(cbocriteria5)
'Units In Stock
AddNumericDropDownCriteria(cbocriteria6)
'populate the corresponding labels with a
'descriptive label
Lblcriteria1.Text = "Product Id:"
Lblcriteria2.Text = "Product Name:"
Lblcriteria3.Text = "Supplier Company Name:"
Lblcriteria4.Text = "Category Name:"
Lblcriteria5.Text = "Unit Price:"
lblcriteria6.Text = "Units In Stock:"
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub
Function BuildSQLStatement() As String
'************************************************* ********************
'The purpose of this function is to build the SQL statement based
'on the criteria specified by the user on the Products form.
'************************************************* ********************
Try
Dim strSQL As String = ""
Dim strSelectFromCriteria As String = ""
Dim strWhereCriteria As String = ""
Dim blnPriorWhere As Boolean = False
Dim blnNumericField As Boolean = False
Dim clsDb As New clsDatabase
strSelectFromCriteria = _
clsDb.BuildSQLSelectFromClause("Products")
'Check the search criteria and add to the WHERE clause if it was
'specified. Do this for each set of criteria on the form
CheckSearchCriteria(cbocriteria1.SelectedItem.Text , Txtcriteria1.Text, _
"ProductId", strWhereCriteria, blnPriorWhere, "true", _
AddressOf clsDb.BuildSQLWhereClause)
CheckSearchCriteria(cbocriteria2.SelectedItem.Text , Txtcriteria2.Text, _
"ProductName", strWhereCriteria, blnPriorWhere, _
"false", AddressOf clsDb.BuildSQLWhereClause)
CheckSearchCriteria(cbocriteria3.SelectedItem.Text , Txtcriteria3.Text, _
"CompanyName", strWhereCriteria, blnPriorWhere, _
"false", AddressOf clsDb.BuildSQLWhereClause)
CheckSearchCriteria(cbocriteria4.SelectedItem.Text , Txtcriteria4.Text, _
"CategoryName", strWhereCriteria, blnPriorWhere, _
"false", AddressOf clsDb.BuildSQLWhereClause)
CheckSearchCriteria(cbocriteria5.SelectedItem.Text , Txtcriteria5.Text, _
"UnitPrice", strWhereCriteria, blnPriorWhere, _
"true", AddressOf clsDb.BuildSQLWhereClause)
CheckSearchCriteria(cbocriteria6.SelectedItem.Text , Txtcriteria6.Text, _
"UnitsInStock", strWhereCriteria, blnPriorWhere, _
"true", AddressOf clsDb.BuildSQLWhereClause)
'put the SELECT, FROM, and WHERE clauses together into one
'string
strSQL = strSelectFromCriteria & strWhereCriteria
'todo remove this message box after finished testing SQL syntax
MsgBox("The SQL Statement is: " & strSQL)
clsDb = Nothing
Return strSQL
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As _
System.EventArgs) Handles btnsearch.Click
Try
Dim custCB As SqlClient.SqlCommandBuilder = New _
SqlClient.SqlCommandBuilder(adapterResults)
Dim clsdatabase As New clsDatabase
Dim strSQL As String = ""
'Load a data set with the complete Products, Suppliers, and
'categories tables (to be used later as code tables to display
'choices in a list, etc.).
dsData = clsdatabase.LoadCompleteDataSet(CONN)
'Load a data set with the search results based on the criteria
'specified by the user on the form.
strSQL = BuildSQLStatement()
dsResults = clsdatabase.LoadSearchDataSet(CONN, strSQL)
dgdresults.DataSource = dsResults
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub
End Class
Imports System.Data
Imports System.Data.SqlClient
Public Class clsDatabase
Function PopulateDataSetTable(ByVal strConnection As String, ByVal _
strTableName As String, ByVal strSQLorStoredProc As String, _
ByVal blnStoredProcedure As Boolean, _
ByRef dsDataSet As DataSet) As DataSet
'************************************************* ***************
'Create a table in the DataSet and fill it with the specified
'table in the database from calling a stored procedure or
'executing a SQL statement (depending on whether
'blnStoredProcedure is true or false; if true - run stored
'procedure; if false, run SQL statement).
'************************************************* ***************
Try
Dim sqlConn As New SqlClient.SqlConnection(strConnection)
sqlConn.Open()
Dim adapterProducts As New SqlClient.SqlDataAdapter
adapterProducts.TableMappings.Add("Table", strTableName)
Dim cmdTable As SqlClient.SqlCommand = New _
SqlClient.SqlCommand(strSQLorStoredProc, _
sqlConn)
'run stored procedure or SQL statement accordingly
If blnStoredProcedure Then
cmdTable.CommandType = CommandType.StoredProcedure
Else
cmdTable.CommandType = CommandType.Text
End If
adapterProducts.SelectCommand = cmdTable
'fill the data set with the table information as specified in
'the stored procedure or from the results of the SQL statement
adapterProducts.Fill(dsDataSet)
sqlConn.Close()
Return dsDataSet
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Function PopulateDataSetRelationship(ByVal strTable1 As String, ByVal _
strTable2 As String, ByVal strColumnFromTable1 As String, _
ByVal strColumnFromTable2 As String, ByVal _
strRelationshipName As String, ByRef dsDataSet As DataSet) _
As DataSet
'************************************************* ***************
'The purpose of this function is to create a relationship between
'two tables in a dataset.
'************************************************* ***************
Try
Dim drRelation As DataRelation
Dim dcCol1 As DataColumn
Dim dcCol2 As DataColumn
dcCol1 = _
dsDataSet.Tables(strTable1).Columns(strColumnFromT able1)
dcCol2 = _
dsDataSet.Tables(strTable2).Columns(strColumnFromT able2)
drRelation = New System.Data.DataRelation _
(strRelationshipName, dcCol1, dcCol2)
dsDataSet.Relations.Add(drRelation)
Return dsDataSet
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Function LoadCompleteDataSet(ByVal strConnection As String) As DataSet
'************************************************* ***************
'The purpose of this function is to populate a data set with
'the local tables from the Products, Suppliers, and Categories
'tables in the database. This is an example of a data set that
'uses relations among the tables.
'************************************************* ***************
Try
Dim dsData As New DataSet
Dim blnRunStoredProc As Boolean = True
'Create a Products table in the DataSet
dsData = PopulateDataSetTable(strConnection, "Products", _
"spRetrieveProducts", blnRunStoredProc, dsData)
'Create a Suppliers table in the DataSet
dsData = PopulateDataSetTable(strConnection, "Suppliers", _
"spRetrieveSuppliers", blnRunStoredProc, dsData)
'Create a Categories table in the DataSet
dsData = PopulateDataSetTable(strConnection, "Categories", _
"spRetrieveCategories", blnRunStoredProc, dsData)
'Create the relationship between Products and Suppliers tables
dsData = PopulateDataSetRelationship("Suppliers", "Products", _
"SupplierId", "SupplierId", "ProductsVsSuppliers", _
dsData)
'Create the relationship between Products and Categories tables
dsData = PopulateDataSetRelationship("Categories", "Products", _
"CategoryId", "CategoryId", "ProductsVsCategories", _
dsData)
Return dsData
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Sub UnhandledExceptionHandler()
'display an error to the user
End Sub
Function LoadSearchDataSet(ByVal strConnection As String, ByVal strSQL _
As String) As DataSet
'************************************************* ***************
'The purpose of this function is to create and populate a data
'set based on a SQL statement passed in to the function.
'************************************************* ***************
Try
Dim dsData As New DataSet
'call the table in the local dataset "results" since the values
'may be coming from multiple tables.
Dim strTableName As String = "Results"
Dim blnRunStoredProc As Boolean = False
dsData = PopulateDataSetTable(strConnection, strTableName, _
strSQL, blnRunStoredProc, dsData)
'return the data set to the calling procedure
Return dsData
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Function PadQuotes(ByVal strIn As String) As String
Try
PadQuotes = strIn.Replace("'", "''")
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Function BuildSQLWhereClause(ByVal strTableName As String, ByVal _
strQueryOperator As String, ByVal strSearchValue As String, _
ByVal blnPriorWhereClause As Boolean, ByVal strWhereClause As _
String, ByVal blnNumberField As Boolean) As String
'************************************************* *********************
'The purpose of this function is to add the parameters passed in to
'the WHERE clause of the SQL Statement.
'************************************************* *********************
Try
Dim strWhere As String = strWhereClause
Dim strDelimiter1 As String
Dim strDelimiter2 As String
If blnPriorWhereClause = False Then
strWhere = " WHERE "
Else
strWhere = strWhere & " AND "
End If
Select Case strQueryOperator
Case "Equals"
If blnNumberField Then
strDelimiter1 = " = "
strDelimiter2 = ""
Else
strDelimiter1 = " = '"
strDelimiter2 = "' "
End If
Case "Starts With"
strDelimiter1 = " LIKE '"
strDelimiter2 = "%' "
Case "Ends With"
strDelimiter1 = " LIKE '%"
strDelimiter2 = "' "
Case "Contains"
strDelimiter1 = " LIKE '%"
strDelimiter2 = "%'"
Case "Greater Than"
strDelimiter1 = " > "
strDelimiter2 = ""
Case "Less Than"
strDelimiter1 = " < "
strDelimiter2 = ""
End Select
'Add the new criteria to the WHERE clause of the SQL Statement.
'Note that the PadQuotes function is also being called to make
'sure that if the user has a single quote in their search value,
'it will put an additional quote so the database doesn't
'generate an error.
strWhere = strWhere & strTableName & strDelimiter1 & _
PadQuotes(strSearchValue) & strDelimiter2
Return strWhere
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Function BuildSQLSelectFromClause(ByVal strSearchMethod As String) _
As String
'************************************************* *********************
'The purpose of this function is to create the SELECT FROM clause for
'the SQL statement depending on whether the search is for Products
'or Suppliers.
'************************************************* *********************
Try
Dim strSelectFrom As String
Select Case strSearchMethod
Case "Products"
'select the products information and the descriptions
'(Product Name and Category Name) from suppliers and
'categories table.
strSelectFrom = "SELECT p.ProductId as ProductId, " & _
"p.ProductName " & _
"as ProductName, p.SupplierId as SupplierId," & _
"s.CompanyName as CompanyName, p.CategoryId " & _
"as CategoryId, c.CategoryName as CategoryName, " & _
"p.QuantityPerUnit as QuantityPerUnit, " & _
"p.UnitPrice as UnitPrice, p.UnitsInStock " & _
"as UnitsInStock, p.UnitsOnOrder as " & _
"UnitsOnOrder, p.ReorderLevel as " & _
"ReorderLevel, p.Discontinued as " & _
"Discontinued " & _
"FROM Products p " & _
"INNER JOIN Suppliers s ON p.SupplierId = " & _
"s.SupplierId " & _
"INNER JOIN Categories c on p.CategoryId = " & _
"c.CategoryId"
Case "Suppliers"
'since we don't need to join to multiple tables, we can
'just select everything from the suppilers table without
'listing the columns all out specifically.
strSelectFrom = "SELECT * FROM Suppliers"
End Select
Return strSelectFrom
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Function
Delegate Function WhereClauseDelegate(ByVal strFieldName As String, _
ByVal strMatchCriteria As String, _
ByVal strFilterCriteria As String, _
ByVal blnPriorWhere As Boolean, _
ByVal strWhereCriteria As String, _
ByVal blnNumberField As Boolean) As String
Sub CheckSearchCriteria(ByVal strMatchCriteria As String, ByVal _
strFilterCriteria As String, _
ByVal strFieldName As String, ByRef strWhereCriteria _
As String, ByRef blnPriorWhere As Boolean, ByVal _
blnNumberField As Boolean, ByVal BuildWhere As _
WhereClauseDelegate)
'************************************************* **************************
'If the user filled out both a value for match criteria (Starts With, Ends
'With, etc.) and a criteria to search for in the corresponding textbox,
'then that criteria needs to be added to the WHERE clause of the SQL
'statement.
'
'Using an advanced feature called DELEGATION, this function receives a
'pointer to the clsDatabase.BuildSQLWhereClause method and invokes it with
'the Invoke statement below. Delegation really isn't hard to understand â in
'simplest terms, it allows you to pass a method as a parameter and then
'call that method.
'************************************************* **************************
If strMatchCriteria <> "" And strFilterCriteria <> "" Then
strWhereCriteria = BuildWhere.Invoke _
(strFieldName, strMatchCriteria, strFilterCriteria, _
blnPriorWhere, strWhereCriteria, blnNumberField)
blnPriorWhere = True
End If
End Sub
End Class
'Here is the code for the design.
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:label id="lblcriteria1" style="Z-INDEX: 101; LEFT: 32px; POSITION: absolute; TOP: 88px"
runat="server">Label</asp:label><asp:label id="lblcriteria2" style="Z-INDEX: 102; LEFT: 32px; POSITION: absolute; TOP: 128px"
runat="server">Label</asp:label><asp:label id="lblcriteria3" style="Z-INDEX: 103; LEFT: 32px; POSITION: absolute; TOP: 160px"
runat="server">Label</asp:label><asp:label id="lblcriteria4" style="Z-INDEX: 104; LEFT: 32px; POSITION: absolute; TOP: 192px"
runat="server">Label</asp:label><asp:label id="lblcriteria5" style="Z-INDEX: 105; LEFT: 32px; POSITION: absolute; TOP: 224px"
runat="server">Label</asp:label><asp:label id="lblcriteria6" style="Z-INDEX: 106; LEFT: 32px; POSITION: absolute; TOP: 256px"
runat="server">Label</asp:label><asp:dropdownlist id="cbocriteria1" style="Z-INDEX: 107; LEFT: 144px; POSITION: absolute; TOP: 88px"
runat="server" AutoPostBack="True"></asp:dropdownlist><asp:dropdownlist id="cbocriteria2" style="Z-INDEX: 108; LEFT: 144px; POSITION: absolute; TOP: 128px"
runat="server"></asp:dropdownlist><asp:dropdownlist id="cbocriteria3" style="Z-INDEX: 109; LEFT: 144px; POSITION: absolute; TOP: 160px"
runat="server"></asp:dropdownlist><asp:dropdownlist id="cbocriteria4" style="Z-INDEX: 110; LEFT: 144px; POSITION: absolute; TOP: 192px"
runat="server"></asp:dropdownlist><asp:dropdownlist id="cbocriteria5" style="Z-INDEX: 111; LEFT: 144px; POSITION: absolute; TOP: 224px"
runat="server"></asp:dropdownlist><asp:dropdownlist id="cbocriteria6" style="Z-INDEX: 112; LEFT: 144px; POSITION: absolute; TOP: 256px"
runat="server"></asp:dropdownlist><asp:textbox id="txtcriteria1" style="Z-INDEX: 113; LEFT: 288px; POSITION: absolute; TOP: 88px"
runat="server"></asp:textbox><asp:textbox id="txtcriteria2" style="Z-INDEX: 114; LEFT: 288px; POSITION: absolute; TOP: 128px"
runat="server"></asp:textbox><asp:textbox id="txtcriteria3" style="Z-INDEX: 115; LEFT: 288px; POSITION: absolute; TOP: 160px"
runat="server"></asp:textbox><asp:textbox id="txtcriteria4" style="Z-INDEX: 116; LEFT: 288px; POSITION: absolute; TOP: 192px"
runat="server"></asp:textbox><asp:textbox id="txtcriteria5" style="Z-INDEX: 117; LEFT: 288px; POSITION: absolute; TOP: 224px"
runat="server"></asp:textbox><asp:textbox id="txtcriteria6" style="Z-INDEX: 118; LEFT: 288px; POSITION: absolute; TOP: 256px"
runat="server"></asp:textbox><asp:dropdownlist id="cboSearchMethod" style="Z-INDEX: 119; LEFT: 176px; POSITION: absolute; TOP: 40px"
runat="server" AutoPostBack="True"></asp:dropdownlist>
<asp:Label id="Message" style="Z-INDEX: 120; LEFT: 296px; POSITION: absolute; TOP: 40px" runat="server">Label</asp:Label>
<asp:Button id="Button1" style="Z-INDEX: 121; LEFT: 288px; POSITION: absolute; TOP: 288px" runat="server"
Text="Search"></asp:Button>
<asp:DataGrid id="dgdresults" style="Z-INDEX: 122; LEFT: 32px; POSITION: absolute; TOP: 320px"
runat="server"></asp:DataGrid></form>
</body>
Sincere Appreciation
Ktsis
creating a web page with multiple search criteria
