?
Solved

I'm a newbie trying to create a simple search form.

Posted on 2005-05-12
12
Medium Priority
?
199 Views
Last Modified: 2010-04-07
Im a newbie to ASP.NET (its my first week with the language). I have a bit of a PHP background to build on however.

I am trying to build a simple search form that returns results (i assume in the form of a datagrid) based on input.
The case at hand is a simple address search. As much information as is entered to seach by will be searched upon, but if a field is left blank, then that is not included in the sql query, or is ignored.
things like address_line1, city, a dropdown for state, and a text field again for zip.

could someone either give me an example, or point me to where there are some?
i have done endless googling on this, but only seem to get the same things all the time, about a simple dropdown and dtagrids. im looking for a whole search form so my googling has been fruitless.

I imagine this is relativly easy for someone with experience, so ill set the points at 125.  if it turns out it is more difficult i have no problem increasing the points for someone who is nice about it :)
0
Comment
Question by:Figment060799
  • 7
  • 5
12 Comments
 
LVL 13

Expert Comment

by:Torrwin
ID: 13991355
Are you using SQL?
0
 

Author Comment

by:Figment060799
ID: 13991421
MS SQL Server yes
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 13995461
Well, here is a search that I use.  It has a list box lbColumns, that lists the columns in a table, and a dropdown list that contains the names of all the tables.  Basically all that a search really is, is a SQL Like statement with your critera substituted in.  For example: "SELECT LAST_NAME FROM ADDRESS_TABLE WHERE address_line1 LIKE '%" & txtaddress.text & "%' AND city LIKE '%" & txtCity.text & "%' AND STATE LIKE '%" & ddlState.SelectedItem.Text & "%' AND ZIP LIKE '%" & ddlZip.SelectedItem.Text & '%"  Where txtAddress, txtCity are textboxes, and ddlState, ddlZip are dropdownlists.

'Setup the Select Statement from lbColumns
        '====================================================================
        Dim SelectString As String = ""
        For Each item In lbColumns.Items
            If item.Selected Then
                SelectString += item.Value & ", "
            End If
        Next

        Dim SelectTemp As Integer = SelectString.LastIndexOf(", ")
        '====================================================================

        'Setup the Like statement from lbColumns
        '====================================================================
        Dim LikeString As String = ""
        For Each item In lbColumns.Items
            If item.Selected Then
                LikeString += item.Value & " LIKE '%" & txtSearch.Text & "%' OR "
            End If
        Next

        Dim LikeTemp As Integer = LikeString.LastIndexOf(" OR ")
        '====================================================================

        'Setup the search command, remove the last ", " from the select string and the last "OR " from the like string
        '====================================================================
        SqlSelectCommand1.CommandText = "SELECT " & SelectString.Remove(SelectTemp, 2) & " FROM bfdbo." & ddlTables.SelectedItem.Value & " WHERE " & _
            LikeString.Remove(LikeTemp, 3)
        '====================================================================


        SqlSelectCommand1.Connection = Me.SqlConnection1
        SqlConnection1.ConnectionString = CS
        SqlDataAdapter1.SelectCommand = SqlSelectCommand1

        dsSearch = New DataSet()

        FillandBind()

        If dsSearch.Tables(0).Rows.Count = 0 Then
            lblMessage.Text = "No results found."
            DataGrid1.Visible = False
        Else
            DataGrid1.Visible = True
        End If
        '====================================================================

Does that make sense, or does anything else need answering?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Figment060799
ID: 13999923
So what it looks like you are doing id defining the sql query in code. seems pretty straightforward enough....
how do you apply it? (rememebr im a newb at asp.net)

is SqlSelectCommand1 a control?
where/what is it that you are setting the .text property of?

(i.e. - where [if any] is the rest?)


here is somethign ive come up with so far...  (and after lots of reading)
============================
sub search_button_click(sender as object, e as eventargs)
      dim arySqlQuery(6)

      if (trim(request.form.get("case_number")) <> "")
            arySqlQuery(0) = "(case_number LIKE '"+ request.form.get("case_number") +"')"
      end if

      if (request.form.get("address_l1") <> "")
            arySqlQuery(1) = "(address_l1 LIKE '"+ request.form.get("address_l1") +"')"
      end if

      if (request.form.get("address_l2") <> "")
            arySqlQuery(2) = "(address_l2 LIKE '"+ request.form.get("address_l2") +"')"
      end if

      if (request.form.get("city") <> "")
            arySqlQuery(3) = "(city LIKE '"+ request.form.get("city") +"')"
      end if

      if (request.form.get("state") <> "")
            arySqlQuery(4) = "(state LIKE '"+ request.form.get("state") +"')"
      end if

      if (request.form.get("zip") <> "")
            arySqlQuery(5) = "(zip LIKE '"+ request.form.get("zip") +"')"
      end if

end sub
=====================================
the thinking there is i can just join the array into the sql string w/out having to search the end of the string.....

but again though... where do i apply this power for good (not evil!) :-D
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 14003382
Ok, here is how you execute your query:

Dim myCommand as new SqlClient.SqlCommand
Dim myAdapter as new SqlClient.SqlDataAdapter
Dim myConnection as new SqlClient.SqlConnection
Dim myQuery as String
Dim DataSet1 as New Dataset

myConnection.ConnectionString = "Server=SERVER_IP_ADDRESS;initial catalog=DATABASE_NAME;uid=USER_ID;pwd=USER_PASSWORD;"
myCommand.Connection = myConnection

myQuery = "Your SQL Query"

myCommand.CommandText = myQuery
myAdapter.SelectCommand = myCommand

Try
     myAdapter.Fill(Dataset1, "Results")
Catch
     lblMessage.text = Err.Description
End Try

DataGrid1.Datasource = Dataset1
DataGrid1.DataBind()

The ".text" is for your controls.  Say you have a textbox named txtCustomerName that you want to find out what the user typed in.  The code myString = txtCustomerName.text would place the entered data into your string variable.  This also works for labels, and several other controls.
0
 

Author Comment

by:Figment060799
ID: 14014011
it makes sense to look at it except that im getting this error:

Compiler Error Message: BC30002: Type 'SqlClient.SqlCommand' is not defined.


how do i define it?
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 14014321
Hmm, can you post the code thats giving you that error?
0
 

Author Comment

by:Figment060799
ID: 14015512
This is what I have so far, with your last example integrated
==========================================

sub search_button_click(sender as object, e as eventargs)
      dim arySqlQuery() as string                                          'array containing the search criteria
      dim aryFormPost(6)                                                      'array containing the possable search criteria from the form post
      dim strFormPost as string                                          'individual form post variable names
      dim strSqlQuery as string                                          'the complete SQL query
      Dim myCommand as new SqlClient.SqlCommand
      Dim myAdapter as new SqlClient.SqlDataAdapter
      Dim myConnection as new SqlClient.SqlConnection
      dim i                                                                        'standard iterator variable

      'initialize the form post array [tweak: there must be a better way]
      aryFormPost(0) = "case_number"
      aryFormPost(1) = "address_l1"
      aryFormPost(2) = "address_l2"
      aryFormPost(3) = "city"
      aryFormPost(4) = "state"
      aryFormPost(5) = "zip"

      'connection string modified to protect the server
      myConnection.ConnectionString = "Server=SERVER_IP_ADDRESS;initial catalog=DATABASE_NAME;uid=USER_ID;pwd=USER_PASSWORD;"
      myCommand.Connection = myConnection

      'Create a general select statement
      strSqlQuery = "SELECT propertyid, case_number, address_line1, address_line2, city, state, zip FROM [property]"

      'Cycle through the aryFormPost predefined string array and populate arySqlQuery with the appropriate search values
      for each strFormPost in aryFormPost
            if (trim(request.form.get(strFormPost)) <> "")
                  if isnothing(arySqlQuery) then
                        redim arySqlQuery(0)
                  else
                        redim preserve arySqlQuery(ubound(arySqlQuery)+1)
                  end if

                  arySqlQuery(ubound(arySqlQuery)) = "(" & strFormPost & " LIKE '"+ request.form.get(strFormPost) +"')"
            end if
      next

      'If we created arySqlQuery above then join the array elements and add it to the query
      if not isnothing(arySqlQuery) then
            strSqlQuery += " WHERE (" + join(arySqlQuery, " AND ") + ")"
      end if
      'response.write(strSqlQuery)

      myCommand.CommandText = strSqlQuery
      myAdapter.SelectCommand = myCommand

      Try
            myAdapter.Fill(Dataset1, "Results")
      Catch
            lblMessage.text = Err.Description
      End Try

      DataGrid1.Datasource = Dataset1
      DataGrid1.DataBind()
end sub
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 14018501
hmm, that looks fine to me, you shouldn't have to define the command other than you already have.  

Do you get the same error if you just try a simple query like:
strSQLQuery = "SELECT CUSTOMER_ID FROM CUSTOMER"

If you don't get the error when trying the above, then it would be a problem with the query (commandtext).
0
 

Author Comment

by:Figment060799
ID: 14019836
I think perhaps i made a mistake not thinking to say that the server is runnign asp.net 2.0
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 14036543
Hmm, I don't know if that has anything to do with it.
0
 
LVL 13

Accepted Solution

by:
Torrwin earned 500 total points
ID: 14045341
You could try putting this at the top of your page:

Imports System.Data.SqlClient
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

839 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