lunanat
asked on
A simple database search
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?
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?
ASKER
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.
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.
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...
Ok let me think about the problem for a minute...
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(By Val sender As Object, ByVal e As System.Web.UI.WebControls. DataGridIt emEventArg s) 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.AlternatingIt em 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
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(By
'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.AlternatingIt
'Assume the third column has the date in it, which would be cell(2)
If CDate(e.Item.Cells(2).Text
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did exactly the trick
Thank you very much for your help.
Thank you very much for your help.
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("M
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("@P
p.Value = IIf(TextBox1.Text.Length = 0, DBNull.Value, TextBox1.Text)
cmd.Parameters.Add(p)
p = New SqlClient.SqlParameter("@P
p.Value = IIf(TextBox2.Text.Length = 0, DBNull.Value, TextBox2.Text)
cmd.Parameters.Add(p)
p = New SqlClient.SqlParameter("@P
p.Value = IIf(TextBox3.Text.Length = 0, DBNull.Value, TextBox3.Text)
cmd.Parameters.Add(p)
p = New SqlClient.SqlParameter("@P
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