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

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 :)
Figment060799Asked:
Who is Participating?
 
TorrwinConnect With a Mentor Commented:
You could try putting this at the top of your page:

Imports System.Data.SqlClient
0
 
TorrwinCommented:
Are you using SQL?
0
 
Figment060799Author Commented:
MS SQL Server yes
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
TorrwinCommented:
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
 
Figment060799Author Commented:
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
 
TorrwinCommented:
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
 
Figment060799Author Commented:
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
 
TorrwinCommented:
Hmm, can you post the code thats giving you that error?
0
 
Figment060799Author Commented:
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
 
TorrwinCommented:
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
 
Figment060799Author Commented:
I think perhaps i made a mistake not thinking to say that the server is runnign asp.net 2.0
0
 
TorrwinCommented:
Hmm, I don't know if that has anything to do with it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.