Visual Basic Discussion Boards

I'm afraid that in order to do this you will need to write a printer driver, which cannot be done in managed code. I suggest you write it in C++...

'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd

Hi
I am trying to develop a web Application that will allow a user to search database by
specifying multiple search criteria from the web page.I am using Microsoft SQL Server
desktop Engine and Microsoft Visual studio.Net 2003

The problem i have with my web application is that when i load my web page, specify some criteria and click on search
botton Nothing is shown on the datagrid(dgdresults).

Please kindly go through my code and help me out.I cannot figure out why my web page is not displaying information based
on the search criteria specified.I am convenient working with VB.Net

'Here is the code in my webform2 class

Public Class WebForm2
Inherits System.Web.UI.Page
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 WithEvents cboSearchMethod As System.Web.UI.WebControls.DropDownList
Protected WithEvents lblcriteria1 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria2 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria3 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria4 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria5 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria6 As System.Web.UI.WebControls.Label
Protected WithEvents Message As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents dgdresults As System.Web.UI.WebControls.DataGrid
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents dgdresults1 As System.Web.UI.WebControls.DataGrid
Protected adapterResults As New SqlClient.SqlDataAdapter
Sub UnhandledExceptionHandler()
Try
Catch myException As Exception
Message.Text = ("Exception: " + myException.ToString())
End Try
End Sub

Sub AddCharDropDownCriteria(ByVal cboIn As DropDownList)
Try
'add these values to the combo box passed in.
'i.e. to combo boxes searching against character fields
cboIn.Items.Add("Equals")
cboIn.Items.Add("Starts With")
cboIn.Items.Add("Ends With")
cboIn.Items.Add("Contains")
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub

Sub AddSearchMethod()
Try
'if the form has not been loaded before, then populate the
'search choices in the drop-down list.
If cboSearchMethod.Items.Count = 0 Then
cboSearchMethod.Items.Add(PROD)
cboSearchMethod.Items.Add(SUPP)
End If
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub

Sub AddNumericDropDownCriteria(ByVal cboIn As DropDownList)
Try
'add these values to the combo box passed in.
'i.e. to combo boxes searching against numeric fields
cboIn.Items.Add("Equals")
cboIn.Items.Add("Greater Than")
cboIn.Items.Add("Less Than")
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents cbocriteria1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria2 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria3 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria4 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria5 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria6 As System.Web.UI.WebControls.DropDownList
Protected WithEvents txtcriteria1 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria3 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria4 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria5 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria6 As System.Web.UI.WebControls.TextBox

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Try
'set the title of the form

'populate the search method drop down list
AddSearchMethod()
'set products as the default selected (since this is the
'Products search form
cboSearchMethod.SelectedItem.Text = PROD
'populate the drop-down lists on the Products Group Box
'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:"
'assign the proper label to the group box

Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End If
End Sub

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

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.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
dgdresults.Databind

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
End Class

'Also i have these codes in my clsDatabase 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(strColumnFromTable1)
dcCol2 = _
dsDataSet.Tables(strTable2).Columns(strColumnFromTable2)
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()
Try
Catch myException As Exception

End Try
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
'*********************************************************************
'The purpose of this (very short but important) function is to search for
'the occurrence of single quotes within a string and to replace any
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

End Class

<asp:label id="lblcriteria1" style="Z-INDEX: 101; LEFT: 32px; POSITION: absolute; TOP: 88px"
="" runat="server">Label<asp:label id="lblcriteria2" style="Z-INDEX: 102; LEFT: 32px; POSITION: absolute; TOP: 128px"
="" runat="server">Label<asp:label id="lblcriteria3" style="Z-INDEX: 103; LEFT: 32px; POSITION: absolute; TOP: 160px"
="" runat="server">Label<asp:label id="lblcriteria4" style="Z-INDEX: 104; LEFT: 32px; POSITION: absolute; TOP: 192px"
="" runat="server">Label<asp:label id="lblcriteria5" style="Z-INDEX: 105; LEFT: 32px; POSITION: absolute; TOP: 224px"
="" runat="server">Label<asp:label id="lblcriteria6" style="Z-INDEX: 106; LEFT: 32px; POSITION: absolute; TOP: 256px"
="" runat="server">Label<asp:dropdownlist id="cbocriteria1" style="Z-INDEX: 107; LEFT: 144px; POSITION: absolute; TOP: 88px"
="" runat="server" autopostback="True"><asp:dropdownlist id="cbocriteria2" style="Z-INDEX: 108; LEFT: 144px; POSITION: absolute; TOP: 128px"
="" runat="server"><asp:dropdownlist id="cbocriteria3" style="Z-INDEX: 109; LEFT: 144px; POSITION: absolute; TOP: 160px"
="" runat="server"><asp:dropdownlist id="cbocriteria4" style="Z-INDEX: 110; LEFT: 144px; POSITION: absolute; TOP: 192px"
="" runat="server"><asp:dropdownlist id="cbocriteria5" style="Z-INDEX: 111; LEFT: 144px; POSITION: absolute; TOP: 224px"
="" runat="server"><asp:dropdownlist id="cbocriteria6" style="Z-INDEX: 112; LEFT: 144px; POSITION: absolute; TOP: 256px"
="" runat="server"><asp:textbox id="txtcriteria1" style="Z-INDEX: 113; LEFT: 288px; POSITION: absolute; TOP: 88px"
="" runat="server"><asp:textbox id="txtcriteria2" style="Z-INDEX: 114; LEFT: 288px; POSITION: absolute; TOP: 128px"
="" runat="server"><asp:textbox id="txtcriteria3" style="Z-INDEX: 115; LEFT: 288px; POSITION: absolute; TOP: 160px"
="" runat="server"><asp:textbox id="txtcriteria4" style="Z-INDEX: 116; LEFT: 288px; POSITION: absolute; TOP: 192px"
="" runat="server"><asp:textbox id="txtcriteria5" style="Z-INDEX: 117; LEFT: 288px; POSITION: absolute; TOP: 224px"
="" runat="server"><asp:textbox id="txtcriteria6" style="Z-INDEX: 118; LEFT: 288px; POSITION: absolute; TOP: 256px"
="" runat="server"><asp:dropdownlist id="cboSearchMethod" style="Z-INDEX: 119; LEFT: 176px; POSITION: absolute; TOP: 40px"
="" runat="server" autopostback="True">
<asp:label id="Message" style="Z-INDEX: 120; LEFT: 296px; POSITION: absolute; TOP: 40px" runat="server">Label
<asp:button id="Button1" style="Z-INDEX: 121; LEFT: 192px; POSITION: absolute; TOP: 288px" runat="server"
="" text="Search">
<asp:datagrid id="dgdresults" style="Z-INDEX: 122; LEFT: 32px; POSITION: absolute; TOP: 320px"
="" runat="server">

Thanks for your website it has helped me alot.
I will appreciate a prompt reply

xuzia

the code is to mutch and i'm not reallly in the mood to study your code in detail, BUT, I don't think you should use delegation here. Delegation is cool thing to use but I won't use it in a web application. Delegation creates extra threads - web applications should be state-less and threads complicates the states.
I know about when you use ArrayList objects accros delegation boundaries, it *resets* the delegation. I don't know why or how or if its a bug. but as soon I used strong-typed arrays everthing worked fine - without changing any other code.

InOut.NET wrote:
I know about when you use ArrayList objects accros delegation boundaries, it *resets* the delegation. I don't know why or how or if its a bug.

I have to correct myself here. I found the reason why the delegation resets. Its because i didn't catch a execption in the delegation method. as soon as i catched the exception, the delegation proceded with the call back.
So maybe its because an exception uccured in your delegation method that the callback was not done without you knowing it. the compiler or JIT does not tell you about a exception within a delegated method if don't catch it explisitlly.

Hi Mike

Thanks for the reply, but i dont know how to go about it using strong-typed arrays.I will appreciate it if you can help me out.

Thanks.

xuzia.

arrays of a primitive data type like int, string, char, bool, etc.

exapmle declarations:

<br />
//array of strings with 10 uninitailized elements:<br />
string[] myArray = new string[10];<br />
<br />
//or array of strings with 2 elements initailized:<br />
string[] myArray = new string[] {"Hello", "there"};

stong-typed only means variables thats not of class-type like

clsMyCustomInteger myInt = new clsCustomInteger(200); //not strong type<br />
int myInt = 200; //strong type

i don't know how to explain it more, i hope it's clear.

Hi everyone

I am trying to create a web site for a friend, using VB & ASP. The main page is an html page, with 2 framesets. The first frameset divides the page into 2, with a standard logo along the top, and the main part of the page below. The second frameset divides the lower section into 3 frames. The left is the classic menu, the middle is the main body of the page, and the far right has an area for other information.

What I can't figure out is when I click on a menu item in the left side, that I get the corresponding content page to display in the middle frame. I know how to do this with basic HTML, but I can't get the syntax for ASP. Can anyone give me a push in the right direction ?

Thanks in advance.

Derek

Hi
I am trying to develop a web Application that will allow a user to search database by
specifying multiple search criteria from the web page.I am using Microsoft SQL Server
desktop Engine and Microsoft Visual studio.Net 2003

The problem i have with my web application is that when i load my web page, specify some criteria and click on search
botton Nothing is shown on the datagrid(dgdresults).

Please kindly go through my code and help me out.I cannot figure out why my web page is not displaying information based
on the search criteria specified.I am convenient working with VB.Net

'Here is the code in my webform2 class

Public Class WebForm2
Inherits System.Web.UI.Page
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 WithEvents cboSearchMethod As System.Web.UI.WebControls.DropDownList
Protected WithEvents lblcriteria1 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria2 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria3 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria4 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria5 As System.Web.UI.WebControls.Label
Protected WithEvents lblcriteria6 As System.Web.UI.WebControls.Label
Protected WithEvents Message As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents dgdresults As System.Web.UI.WebControls.DataGrid
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents dgdresults1 As System.Web.UI.WebControls.DataGrid
Protected adapterResults As New SqlClient.SqlDataAdapter
Sub UnhandledExceptionHandler()
Try
Catch myException As Exception
Message.Text = ("Exception: " + myException.ToString())
End Try
End Sub

Sub AddCharDropDownCriteria(ByVal cboIn As DropDownList)
Try
'add these values to the combo box passed in.
'i.e. to combo boxes searching against character fields
cboIn.Items.Add("Equals")
cboIn.Items.Add("Starts With")
cboIn.Items.Add("Ends With")
cboIn.Items.Add("Contains")
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub

Sub AddSearchMethod()
Try
'if the form has not been loaded before, then populate the
'search choices in the drop-down list.
If cboSearchMethod.Items.Count = 0 Then
cboSearchMethod.Items.Add(PROD)
cboSearchMethod.Items.Add(SUPP)
End If
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub

Sub AddNumericDropDownCriteria(ByVal cboIn As DropDownList)
Try
'add these values to the combo box passed in.
'i.e. to combo boxes searching against numeric fields
cboIn.Items.Add("Equals")
cboIn.Items.Add("Greater Than")
cboIn.Items.Add("Less Than")
Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End Sub

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents cbocriteria1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria2 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria3 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria4 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria5 As System.Web.UI.WebControls.DropDownList
Protected WithEvents cbocriteria6 As System.Web.UI.WebControls.DropDownList
Protected WithEvents txtcriteria1 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria3 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria4 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria5 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtcriteria6 As System.Web.UI.WebControls.TextBox

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Try
'set the title of the form

'populate the search method drop down list
AddSearchMethod()
'set products as the default selected (since this is the
'Products search form
cboSearchMethod.SelectedItem.Text = PROD
'populate the drop-down lists on the Products Group Box
'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:"
'assign the proper label to the group box

Catch
'error handling goes here
UnhandledExceptionHandler()
End Try
End If
End Sub

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

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.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)

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
End Class

'Also i have these codes in my clsDatabase 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(strColumnFromTable1)
dcCol2 = _
dsDataSet.Tables(strTable2).Columns(strColumnFromTable2)
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()
Try
Catch myException As Exception

End Try
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
'*********************************************************************
'The purpose of this (very short but important) function is to search for
'the occurrence of single quotes within a string and to replace any
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

End Class

Thanks for your website it has helped me alot.
I will appreciate a prompt reply

xuzia

Hi, I was wondering if anyone knows how to convert a DataGridColumnStyle type to an interface type. I am converting some code from C# to VB.net and this one error is hanging up my progress.
The code in C# works:
commonCol=col as ICommonColumnStyle;

But whenI try to convert it to VB.net:
commonCol=Ctype(col, ICommonColumnStyle)

It gives me a runtime error saying that the specified cast is not valid. I have tried
try
commonCol = Ctype(col, ICommonColumnStyle)
catch
commonCol = Nothing
end try

that will eliminate the error, but the problem of col never being converted exists, it will always set commonCol = nothing and I need it to convert the value. Any ideas on this would be greatly appreciated. Thank you very much and have a good one!!

eatwork

I have a dropdown list with about 1000 employees

If someone is looking for "john smith", i would like them to be able to type "j" which would narrow it down to the "j"'s and then "o" which would narrow it down further to just the "jo"'s currently when i type "j" then "o" it will narrow it down to the employees with "o" as the first letter of their name. Is there a way to do this with a dropdownlist control? or does anyone know of a custom control out there?

Thank you in advance

Colum

DropdownList have that property just begin type name example "John". Press J letter the u get the items which begins with J letter and so on.

thanks for your reply i understand that it has this property to one level as you said but as there are many employees, about 150 j's i would like to narrow now further,

note: I have seen this done before

Good does employee name have any unique value???
If so i give u one idea...
add one textbox near DropdownList and write employee code (which is unique) when you leave from textbox or when u press enter key employee name have to display in dropdownlist as selected employee.

example:
EmpCode NameLName
45KL John Smith
45KM John Kerry

when u write in textbox empcode 45KL and press enter key selexcted item in dropdown list should be John Smith

The soultion can be found by follwing the link below

http://www.mredkj.com/vbnet/AutoCompleteDropdownlist.html[^]

thank you all for your responses

Dude it's solution too but what i suggest you is more simple than that example which i saw.
But anyway it's just my idea.

yea its a simple idea but the user of the system will not know every employee code from memory so this is much easier

Hi everybody
Please can you tell me the methode allows to check if a file is existing in a directory "X"
if it is then its ok if not
i want to create it with some statements in

please if you know write me the code & explain as much as you can
thank you Smile | :)

try to be good if you can't be the best

You could do something like this example

Dim fs As System.IO.FileStream
Dim scrFile As System.IO.StreamWriter

strFileName = "some path and filename"

 
If Not IO.File.Exists(strFileName) Then
    fs = New System.IO.FileStream(strFileName, IO.FileMode.OpenOrCreate,   IO.FileAccess.Write)
else
    'Just open file here 
Endif

code is posted "as is" an is just an example.

You could check out the IO namespace
http://msdn2.microsoft.com/en-us/library/system.io.aspx[^]

http://msdn2.microsoft.com/en-us/library/system.io.filestream.aspx[^]

this will properly give you a good idea which statements you need

It's my opinion that nothing is impossible, it's "just" at matter of enough knowledge, time and money.

Hi to everyone, Well i have some saves created by my appication!!When my application is open i go file open open and my save it's opens!!!Now my problem is when i try to open this save with right click and "open with" it doesn't opens my application!!!My problem is that i want this saves to be opened by an other application and displays me my first application with the saved values!!!!

Does anyone knows how can i do that!!!
I try to made it with "Shell()" and "sendkeys" but i made nothing!!!

Thanks for ur time.....

giotis

%SystemRoot%\Explorer.exe /e,/idlist,%I,%L

we can send this command to explore.exe to open specific folder through double clicking in folder .
or after doubleclicking in folder, window send this command to explorer

similarly i want to receive parameters in my program. how can i do it?
please help me..

Hu ,
please i have an application in vb.net
i wanna recuperate some data from the database but somefields there are empty then when i get them in the textboxes they gave errors do you have a solution ?

try to be good if you can't be the best

Mohammed Amine wrote:
somefields there are empty then when i get them in the textboxes they gave errors do you have a solution ?

What do you mean by "empty"? Do you mean it is an empty string? Or do you mean it is a null value?

What error message do you get?

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

i meant a null value , when you have a null value in a field in the database & you try to get this field to your application in a textbox it dosen't want to

try to be good if you can't be the best

Then check if it is null before attempting to populate the text box. A null from the database will be a System.DBNull.Value

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

with the try catch isn't it man ??
thank you thank you very much

try to be good if you can't be the best