Solved

A simple database search

Posted on 2006-07-11
7
218 Views
Last Modified: 2008-01-09
Hi there,

I need to do a simple search page, where a user enters in criteria via textboxes.  There are 4 values that can be used by criteria, and it needs to be searchable by any or all.  The search would be then run on a MS-SQL Server.

I can write the SQL easy enough, as it would be as simple as:
SELECT *
FROM Permits
WHERE (
field1=@param1 OR field2 = @param2 OR .... field4 = @param4
)

My problem is in getting the values for the parameters, and then displaying the search results.

I have the 4 textboxes, and I can get their values with a simple .text, and I imagine that the best way to display the results would be with an <asp:GridView>, but how do I tell the gridview to refresh so that it displays the records which meet the criteria, once the user has pressed the search button?
0
Comment
Question by:lunanat
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:ibost
ID: 17083154
Assume you are using a datagrid, here's something that might get you started for a button click event.  Note the query you specified will only filter based on the first field=@param that returns true.  I changed it so the user can specify any field and it will filter on all of them.  When a textbox is left blank, it will not filter on the corresponding field.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
      ' Declare connection and command object
        Dim conn As New SqlClient.SqlConnection("MyConnectionString")
        Dim cmd As New SqlClient.SqlCommand

        ' Add parameters to Command Object.  IF Textbox text is nothing, then
        ' set the parameter to <NULL>.
        Dim p As SqlClient.SqlParameter

        p = New SqlClient.SqlParameter("@Param1", SqlDbType.VarChar)
        p.Value = IIf(TextBox1.Text.Length = 0, DBNull.Value, TextBox1.Text)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@Param2", SqlDbType.VarChar)
        p.Value = IIf(TextBox2.Text.Length = 0, DBNull.Value, TextBox2.Text)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@Param3", SqlDbType.VarChar)
        p.Value = IIf(TextBox3.Text.Length = 0, DBNull.Value, TextBox3.Text)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@Param4", SqlDbType.VarChar)
        p.Value = IIf(TextBox4.Text.Length = 0, DBNull.Value, TextBox4.Text)
        cmd.Parameters.Add(p)

        ' Set up the query.  If user did not specify something in the textbox,
        ' the corresponding parameter will have been set to null.  This query
        ' will skip the parameter when it is null, or otherwise filter by it
        ' when it is not null.
        Dim sql As String
        sql = "SELECT * FROM Permits WHERE " & _
                "(@Param1 IS NULL OR Field1 = @Param1) AND " & _
                "(@Param2 IS NULL OR Field2 = @Param2) AND " & _
                "(@Param3 IS NULL OR Field3 = @Param3) AND " & _
                "(@Param4 IS NULL OR Field4 = @Param4)"


        ' Set up the rest of the command object
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = sql

        ' Declare a datareader and bind to a datagrid on the page
        conn.Open()
        Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()
        DataGrid1.DataSource = rdr
        DataGrid1.DataBind()
        rdr.Close()
        conn.Close()
    End Sub
0
 
LVL 1

Author Comment

by:lunanat
ID: 17085750
You are a genious.  The sample data I have in the database returns perfectly.

If you like, I can mark this question as answered.  Or, there is an option for more points.

It would be quite benefitial if I were able to check the expiry date (which is one of the fields in the returned data) and if the record is past it's expiry date, highlight it with red or change the font to red.

Since I can only give points to one person, that means that anyone else who answers the "bonus question" would be doing it for no points... the original question has been answered already and any answer will be given to ibost.
0
 
LVL 10

Expert Comment

by:ibost
ID: 17085813
you can actually split points between people.  There's an option at the bottom of the page somewhere to do that.

Ok let me think about the problem for a minute...
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Expert Comment

by:ibost
ID: 17086084
Ok again assuming you're using a datagrid...

I take it your query will return an ExpirationDate as part of the results.  And if today's date is greater than the ExpirationDate then you want to change the back color to red...

So what you'll do is probably use the ItemDataBound event for the datagrid:

Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound

   'Check if you're looking at an item or alternating item (row) in the datagrid
   If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then

      'Assume the third column has the date in it, which would be cell(2)
      If CDate(e.Item.Cells(2).Text) < DateTime.Now Then
         e.Item.Cells(2).BackColor = System.Drawing.Color.Red
         'e.Item.BackColor = System.Drawing.Color.Red (if you want the whole row highlighted)
      End If

   End If

End Sub
0
 
LVL 1

Author Comment

by:lunanat
ID: 17091250
I get a build error... it does not like "datagrid1.itemdatabound" in the sub's declaration

I changed 'datagrid1' to the ID of my gridview object.

If I change it to gridview1.databound then the sub's eventarg parameter needs to be changed to a system.eventargs, and then it doesn't have an .item member.

Do I need to import something?
0
 
LVL 10

Accepted Solution

by:
ibost earned 200 total points
ID: 17091617
I'm not sure how gridviews work :(

I think it uses a "rowDataBound" event instead.


Here is a sample I found that I hope you can use:
source:
http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspp/html/GridViewEx03.asp

Excerpt:
Formatting the GridView Based on the Underlying Data
In the two previous examples the GridView's formatting was specified statically. There are times, however, where you might want to alter the formatting based on the data in the GridView. For example, when listing product information you might want to draw the user's eye to products that are out of stock. One way to accomplish this would be to change the background color of those rows with a Units In Stock value of 0.

To accomplish this we need to inspect the data of each of the rows of the GridView and alter the row's BackColor property if the Units In Stock is 0. The GridView offers a RowDataBound event that fires once for each row after the row has been created and bound to the corresponding record of data from the data source control. We can create an event handler for this event that checks to see if Units In Stock is 0 and, if it is, set the BackColor property of the GridView row to Yellow.

To create an event handler for the GridView's RowDataBound event, go to the Design view, click on the GridView, and go to the Properties pane. From the Properties pane, click on the lightning bolt; this will list the GridView's events. Then, in the RowDataBound line, type in the name of the event handler you want to create (I named my event handler productsGridView_RowDataBound). Finally, enter in the following code into the event handler:


Sub productsGridView_RowDataBound(ByVal sender As Object, _
  ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        'determine the value of the UnitsInStock field
        Dim unitsInStock As Integer =  _
          Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, _
          "UnitsInStock"))
        If unitsInStock = 0 Then
            ' color the background of the row yellow
            e.Row.BackColor = Drawing.Color.Yellow
        End If
    End If
End Sub



The event handler starts by checking to see if we are working with a DataRow. Realize that the GridView's RowDataBound event handler fires for each of the rows being created, including the HeaderRow. However, the HeaderRow doesn't have a Units In Stock column with data associated with it, so we are only interested in working with DataRows. The UnitsInStock field from the data bound to the GridView row is obtained through a DataBinder.Eval() call, passing in the data bound to the GridView row (e.Row.DataItem) and the name of the field whose value we are interested in. Finally, a check is made to see if there are zero units in stock. If so, the BackColor property of the row is set to Yellow.
0
 
LVL 1

Author Comment

by:lunanat
ID: 17092383
That did exactly the trick

Thank you very much for your help.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now