Solved

sqldatareader code problem.

Posted on 2006-06-26
9
523 Views
Last Modified: 2008-02-01
I am getting the following error when I run my asp.net 1.1 in vb.net application:

Prepared statement '(@apartmentID int,@rentrange varchar(13),@bedrooms varchar(13),@' expects parameter @offstreet, which was not supplied.

Here is the code I am using:

Dim apartmentid As Integer
        Dim rentrange, bedrooms, bathrooms, pets, smoking, airconditioning, clubhouse, offstreet, fireplace, dishwasher, secureentry, allelectric, wheelchair, laundryroom, washerhookup, sqlsubstrrentrange, sqlsubstrbathrooms, sqlsubstrbedrooms, sqlsubstrsmoking, sqlsubstrpets, sqlsubstrairconditioning, sqlsubstrclubhouse, sqlsubstroffstreet, sqlsubstrfireplace, sqlsubstrdishwasher, sqlsubstrsecureentry, sqlsubstrallelectric, sqlsubstrwheelchair, sqlsubstrlaundryroom, sqlsubstrwasherhookup As String
        rentrange = Request.QueryString("rentrange")
        bathrooms = Request.QueryString("bathrooms")
        bedrooms = Request.QueryString("bedrooms")
        smoking = Request.QueryString("smoking")
        pets = Request.QueryString("pets")
        airconditioning = Request.QueryString("Central Air Conditioning")
        clubhouse = Request.QueryString("clubhouse")
        offstreet = Request.QueryString("Offstreet Parking")
        fireplace = Request.QueryString("fireplace")
        dishwasher = Request.QueryString("dishwasher")
        secureentry = Request.QueryString("Secure Entry")
        allelectric = Request.QueryString("All Electric")
        wheelchair = Request.QueryString("wheelchair access")
        laundryroom = Request.QueryString("laundry room")
        washerhookup = Request.QueryString("Washer/Dryer Hookup")

        If (rentrange = "No Preference") And (bathrooms = "No Preference") And (bedrooms = "No Preference") And (smoking = "No Preference") And (pets = "No Preference") Then
            smoking = "yes"
        End If
        If rentrange = "No Preference" Then
            sqlsubstrrentrange = ""
            If bedrooms = "no preference" Then
                sqlsubstrbedrooms = ""
                If bathrooms = "no preference" Then
                    sqlsubstrbathrooms = ""
                    If pets = "no preference" Then
                        sqlsubstrpets = ""
                        If smoking = "no preference" Then
                            sqlsubstrsmoking = ""
                        Else
                            sqlsubstrsmoking = "where(smoking=@smoking)"
                        End If
                    Else
                        sqlsubstrpets = "where (pets=@pets)"
                    End If
                Else
                    sqlsubstrrentrange = "Where (Case WHEN rent <= 400 then 'Less than $400' WHEN rent < 500 then rent '$401-$499' WHEN rent < 600 then rent '$500-$599' WHEN rent < 700 then rent '$600-$700' else '$700 and more' END = @rentrange)"
                End If
            Else
                sqlsubstrbedrooms = "where(bedrooms=@bedrooms)"
            End If
        Else
            sqlsubstrbathrooms = "where (bathrooms=@bathrooms)"
            If sqlsubstrrentrange = "No Preference" Then
                sqlsubstrrentrange = ""
            Else
                sqlsubstrrentrange = "AND(Case WHEN rent <= 400 then 'Less than $400' WHEN rent < 500 then rent '$401-$499' WHEN rent < 600 then rent '$500-$599' WHEN rent < 700 then rent '$600-$700' else '$700 and more'END = @rentrange"
            End If
            If bedrooms = "Yes" Then
                sqlsubstrbedrooms = "AND (bedrooms=@bedrooms)"
            Else
                sqlsubstrbedrooms = ""
            End If
            If bathrooms = "Yes" Then
                sqlsubstrbathrooms = "AND (Bathrooms=@Bathrooms)"
            Else
                sqlsubstrbathrooms = ""
                If smoking = "Yes" Then
                    sqlsubstrsmoking = "AND (smoking=@smoking)"
                Else
                    sqlsubstrsmoking = ""
                    If pets = "Yes" Then
                        sqlsubstrpets = "AND (pets=@pets)"
                    Else
                        sqlsubstrpets = ""
                        If airconditioning = "Yes" Then
                            sqlsubstrairconditioning = "AND (airconditioning =@airconditioning)"
                        Else
                            sqlsubstrairconditioning = ""
                            If clubhouse = "Yes" Then
                                sqlsubstrclubhouse = "AND (clubhouse= @clubhouse)"
                            Else
                                sqlsubstrclubhouse = ""
                            End If
                            If offstreet = "yes" Then
                                sqlsubstroffstreet = "AND (offstreet=@offstreet)"
                            Else
                                sqlsubstroffstreet = ""
                                If fireplace = "Yes" Then
                                    sqlsubstrfireplace = "AND (fireplace=@fireplace)"
                                Else
                                    sqlsubstrfireplace = ""
                                    If dishwasher = "Yes" Then
                                        sqlsubstrdishwasher = "AND (dishwasher=@dishwasher)"
                                    Else
                                        sqlsubstrdishwasher = ""
                                        If secureentry = "Yes" Then
                                            sqlsubstrsecureentry = "AND (secureentry=@secureentry)"
                                        Else
                                            sqlsubstrsecureentry = ""
                                            If allelectric = "yes" Then
                                                sqlsubstrallelectric = "AND(allelectric=@allelectric)"
                                            Else
                                                sqlsubstrallelectric = ""
                                                If wheelchair = "yes" Then
                                                    sqlsubstrwheelchair = "AND (wheelchair=@wheelchair)"
                                                Else
                                                    sqlsubstrwheelchair = ""
                                                    If laundryroom = "yes" Then
                                                        sqlsubstrlaundryroom = "AND (laundryroom=@laundryroom)"
                                                    Else
                                                        sqlsubstrlaundryroom = ""
                                                        If washerhookup = "yes" Then
                                                            sqlsubstrwasherhookup = "AND(washerhookup=@washerhookup)"

                                                        End If
                                                    End If
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If

        Dim myconnection As SqlConnection
        Dim mycommand As SqlCommand
        myconnection = New SqlConnection("****")
        myconnection.Open()

        mycommand = New SqlCommand("SELECT image, rent, address, link, proppage FROM apartments " & sqlsubstrrentrange & sqlsubstrbathrooms & sqlsubstrbedrooms & sqlsubstrpets & sqlsubstrsmoking & sqlsubstrclubhouse & sqlsubstrairconditioning & sqlsubstroffstreet & sqlsubstrfireplace & sqlsubstrdishwasher & sqlsubstrsecureentry & sqlsubstrallelectric & sqlsubstrwheelchair & sqlsubstrlaundryroom & sqlsubstrwasherhookup, myconnection)

        mycommand.Parameters.Add(New SqlParameter("@apartmentID", SqlDbType.Int))
        mycommand.Parameters("@apartmentID").Value = apartmentid
        mycommand.Parameters.Add(New SqlParameter("@rentrange", SqlDbType.VarChar))
        mycommand.Parameters("@rentrange").Value = rentrange
        mycommand.Parameters.Add(New SqlParameter("@bedrooms", SqlDbType.VarChar))
        mycommand.Parameters("@bedrooms").Value = bedrooms
        mycommand.Parameters.Add(New SqlParameter("@offstreet", SqlDbType.VarChar))
        mycommand.Parameters("@offstreet").Value = offstreet
        mycommand.Parameters.Add(New SqlParameter("@bathrooms", SqlDbType.VarChar))
        mycommand.Parameters("@bathrooms").Value = bathrooms
        mycommand.Parameters.Add(New SqlParameter("@smoking", SqlDbType.VarChar))
        mycommand.Parameters("@smoking").Value = smoking
        mycommand.Parameters.Add(New SqlParameter("@pets", SqlDbType.VarChar))
        mycommand.Parameters("@pets").Value = pets
        mycommand.Parameters.Add(New SqlParameter("@airconditioning", SqlDbType.VarChar))
        mycommand.Parameters("@airconditioning").Value = airconditioning
        mycommand.Parameters.Add(New SqlParameter("@clubhouse", SqlDbType.VarChar))
        mycommand.Parameters("@clubhouse").Value = clubhouse
        mycommand.Parameters.Add(New SqlParameter("@fireplace", SqlDbType.VarChar))
        mycommand.Parameters("@fireplace").Value = fireplace
        mycommand.Parameters.Add(New SqlParameter("@dishwasher", SqlDbType.VarChar))
        mycommand.Parameters("@dishwasher").Value = dishwasher
        mycommand.Parameters.Add(New SqlParameter("@secureentry", SqlDbType.VarChar))
        mycommand.Parameters("@secureentry").Value = secureentry
        mycommand.Parameters.Add(New SqlParameter("@allelectric", SqlDbType.VarChar))
        mycommand.Parameters("@allelectric").Value = allelectric
        mycommand.Parameters.Add(New SqlParameter("@wheelchair", SqlDbType.VarChar))
        mycommand.Parameters("@wheelchair").Value = wheelchair
        mycommand.Parameters.Add(New SqlParameter("@laundryroom", SqlDbType.VarChar))
        mycommand.Parameters("@laundryroom").Value = laundryroom
        mycommand.Parameters.Add(New SqlParameter("@washerhookup", SqlDbType.VarChar))
        mycommand.Parameters("@washerhookup").Value = washerhookup
        Dim reader As SqlDataReader

        reader = mycommand.ExecuteReader()

        'show results in grid
        If reader.HasRows() Then
            lblresults.Visible = False

            DataList1.DataSource = reader

            DataList1.DataBind()

This code is meant to take values from a series of radiobuttonlists and one checkboxlist from "apartments.aspx" and pass them through a sql database.  I appreciate any help you can give me on this.

Jerry Smith
0
Comment
Question by:smith68349
  • 5
  • 4
9 Comments
 
LVL 10

Expert Comment

by:ibost
ID: 16985432
It looks like you declare @ApartmentID but never assign it?

At the top you have:
Dim apartmentid As Integer

And near the bottom you try to assign a parameter but apartmentID has never been initialized with a value:
mycommand.Parameters("@apartmentID").Value = apartmentid


Any chance you could write a stored procedure for this?

If so, what I would do is just assign all the variables.  In the cases where user picks "no preference" then just leave it null.  Your stored proc will take all those values but have defaults of null.

Something like

create procedure GetApartmentList
   @ApartmentID int = null
,  @bathrooms int = null
, etc



then the select is like
select * from MyTable
where
   (@ApartmentID is null or ApartmentID = @ApartmentID) and
   (@bathrooms is null or bathrooms = @bathrooms)

etc


Basically it only filters on a variable when you passed in a value.  If you left it null, it basically will skip over filtering on that.

0
 

Author Comment

by:smith68349
ID: 16988508
I would love to create a stored procedure for this as I think it will be a bit more efficient but I wouldn't know how to refer to it in my code. I haven't been able to figure that part out.

Also, another question... I currently have the choices set as varchar. Would it be better to set them as int?  How would I get the rentrange set in my stored procedure? And finally, refer to it in my sqldatareader?
0
 
LVL 10

Expert Comment

by:ibost
ID: 16988573
Stored procedures are really easy to refer to in code.  You would just set the CommandType property of the SqlCommand to CommandType.StoredProcedure.  You'd add all the parameters the same way then execute it.  The benefit is you could have all the logic to build your select statement inside the stored proc.

        Dim myCmd As New SqlClient.SqlCommand
        myCmd.CommandType = CommandType.StoredProcedure
        myCmd.CommandText = "MyProcedureName"

        myCmd.Parameters.Add("@Param1", SqlDbType.Bit)
        myCmd.Parameters("@Param1").Value = SomeValue

        myCmd.Parameters.Add("@Param2", SqlDbType.Int)
        myCmd.Parameters("@Param2").Value = SomeOtherValue


As for the bulk of your params such as "pets", "clubhouse", "fireplace", i assume these are either yes or no.  So they would be good to define in your database as the sqltype "bit" (1 or 0).  For things like bathrooms and bedrooms, define those as INTs in the db.

For rent range, what I think would be better is that you store the monthly rent for each unit in the db as an int type.  Then you could have two textboxes where the use could type in the minimum and maximum rent values (or you could use drop down lists with some predefined values).  If they type in nothing then you have a case where you'll either want default values in the textboxes or you could pass nulls to the stored proc

Then your select statement could say
where
       rent >= @MinimumRent
and rent <= @MaximumRent

this assumes they always type something in or the min textbox defaults to 0 and the max textbox defaults to something like 5000

If you wanted to pass nulls to the proc
where
      (@MinimumRent is null or rent >= @MinimumRent)
and (@MaximumRent is null or rent <= @MaximumRent)




0
 
LVL 10

Expert Comment

by:ibost
ID: 16988700
I'm not too sure how you have everything laid out but to make the sp easier I'll assume you have textboxes for min and max rent:
<asp:TextBox id="tbxMinimumRent" runat="server"></asp:TextBox>
<asp:TextBox id="tbxMaximumRent" runat="server"></asp:TextBox>

And other stuff where user selects would be drop down lists like:
<asp:dropdownlist id="ddlBathrooms" runat="server">
      <asp:ListItem Value="-1" Selected="True">No Preference</asp:ListItem>
      <asp:ListItem Value="1">1</asp:ListItem>
      <asp:ListItem Value="2">2</asp:ListItem>
      <asp:ListItem Value="3">3</asp:ListItem>
</asp:dropdownlist>

<asp:dropdownlist id="ddlFireplace" runat="server">
      <asp:ListItem Value="-1" Selected="True">No Preference</asp:ListItem>
      <asp:ListItem Value="1">Yes</asp:ListItem>
      <asp:ListItem Value="0">No</asp:ListItem>
</asp:dropdownlist>

So notice that when user has no preference, I have set the list item value to a flag of -1.

Now you'll have some event, probably a button click where you can fire off a call to your stored proc:
        Dim cmd As New SqlClient.SqlCommand
        Dim conn As New SqlClient.SqlConnection("***")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "GetApartmentList"
        cmd.Connection = conn

        Dim p As SqlClient.SqlParameter

        p = New SqlClient.SqlParameter("@MinimumRent", SqlDbType.Int)
        p.Value = IIf(tbxMinimumRent.Text.Length = 0, DBNull.Value, tbxMinimumRent.Text)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@MaximumRent", SqlDbType.Int)
        p.Value = IIf(tbxMaximumRent.Text.Length = 0, DBNull.Value, tbxMaximumRent.Text)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@Bathrooms", SqlDbType.Int)
        p.Value = IIf(ddlBathrooms.SelectedValue = -1, DBNull.Value, ddlBathrooms.SelectedValue)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@Fireplace", SqlDbType.Bit)
        p.Value = IIf(ddlFireplace.SelectedValue = -1, DBNull.Value, ddlFireplace.SelectedValue)
        cmd.Parameters.Add(p)

        'Add rest of the paramter values in the same way


        Dim rdr As SqlClient.SqlDataReader

        conn.Open()
        rdr = cmd.ExecuteReader()
        'Bind to a datagrid or do something with the reader here
        rdr.Close()
        conn.Close()


Now for the stored proc itself, it would be created by using something like this:

create procedure GetApartmentList
      @MinimumRent       int       = null -- saying '= null' means it will default to null value if not specified
,      @MaximumRent      int      = null
,      @Bathrooms      int      = null
,      @Fireplace      bit      = null
-- etc... put in rest of parameters
as

select *
from ApartmentTable

where
      (@MinimumRent       is null or ApartmentTable.Rent >= @MinimumRent)
and      (@MaximumRent       is null or ApartmentTable.Rent <= @MaximumRent)
and      (@Bathrooms      is null or ApartmentTable.Bathrooms >= @Bathrooms)
and      (@Fireplace      is null or ApartmentTable.Fireplace = @Fireplace)
-- etc etc
GO


I hope that helps - didn't actually test this code ;-)

-Ian
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:smith68349
ID: 16988921
I have been using a radiobuttonlist for rent, bathroom, bedrooms, smoking, and pets.  I agree it makes a lot of sense to use a textbox for the rent as the sp will be much easier to create that way.  However, I have been using a checkbox list for the features such as air conditioning, clubhouse, wheelchair access, etc.  I could use that same philosophy for the radiobuttonlists and the one checkbox list couldn't I?

Also, in my select statement on the code I started out with, I was hoping to get the image, true rent amount, address of the property, and a link to a page that will tell more about the property and download an application.  I am familiar with that part but the request.querystring and response.redirect are new to me.

I appreciate the help I am getting on this matter. I am about an hour away from an epiphany. :)

Jerry
0
 

Author Comment

by:smith68349
ID: 16989314
I tried to create the stored procedure based on the information you gave me. It is giving me syntax errors as well as telling me to "declare variables".  I'm even more lost now than I was before I sat down tonight. :)
0
 
LVL 10

Expert Comment

by:ibost
ID: 16993997
Ok let's forget about the stored proc for now and just build the query inline.  I sort of got sidetracked and forgot you were passing on the querystring (I had envisioned all the controls at the top and a datagrid on the same page)

Ok so let's say you have webform1 which is where the user selects all the stuff and then you response.redirect to webform2 and pass a querystring.  Webform2 is where you build and run the query.

Now on webform1, you'll have some textboxes for min and max rent.  If the user leaves them blank we know they have no preference about rent.  This assumes the database table for your apartments stores and integer value for how much the rent is for each unit, for instance 500, 999, 650, etc:
<asp:TextBox id="tbxMinRent" runat="server"></asp:TextBox>
<asp:TextBox id="tbxMaxRent" runat="server"></asp:TextBox>

You also have radio button lists, say for things like # of bathrooms, bedrooms, etc.  By default we will have the "No Preference" option selected and a value of -1 which is a flag indicating the user does not care how many the apartment has:
<asp:RadioButtonList id="rblBathrooms" runat="server">
      <asp:ListItem Selected="True" Value="-1">No Preference</asp:ListItem>
      <asp:ListItem Value="1">1</asp:ListItem>
      <asp:ListItem Value="2">2</asp:ListItem>
      <asp:ListItem Value="3">3</asp:ListItem>
</asp:RadioButtonList>

<asp:RadioButtonList id="rblBedrooms" runat="server">
      <asp:ListItem Value="-1" Selected="True">No Preference</asp:ListItem>
      <asp:ListItem Value="1">1</asp:ListItem>
      <asp:ListItem Value="2">2</asp:ListItem>
      <asp:ListItem Value="3">3</asp:ListItem>
</asp:RadioButtonList>

Finally, you have a checkbox list indicating yes or no for some other amenities.  Generally, if user checks the box that would mean they do want that amenity (for instance, a fireplace).  However if user does not check the box it probably means they don't care, (for instance user does not specifically want a fireplace or clubhouse, but probably wouldn't be opposed if the unit actually came with it).  Perhaps a distinction could be made for Smoking, however, where if the user selects smoking they want a smoking unit and if they do not check smoking then they probably do NOT want a unit that allows smoking (or has been smoked in):
<asp:CheckBoxList id="cblAmenities" runat="server">
      <asp:ListItem Value="Fireplace">Fireplace</asp:ListItem>
      <asp:ListItem Value="Smoking">Smoking</asp:ListItem>
      <asp:ListItem Value="Clubhouse">Clubhouse</asp:ListItem>
</asp:CheckBoxList>

Finally, on webform1 there is a button that the user clicks to run the search.  This button will validate the page and redirect to webform2 after forming the querystring:
<asp:Button id="Button1" runat="server" Text="Button"></asp:Button>

In the code behind for webform1, you will have a function for the button click.  First we will see if user typed anything in the rent textboxes.  If user typed nothing we will assign the -1 (no preference) flag.  Otherwise we assign whatever value user typed in.  Next we assign the values for bathrooms and bedrooms.  Again if "no preference" was selected, the -1 flag gets assigned.  Finally, we'll declare some booleans for the checkboxlist (which i called cblAmenities).  Then we loop through the listitems and check each one for whether it is true or false, then assign as appropriate.  Lastly, we response.redirect to webform2 and formulate the querystring that will get passed along:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim minRent As Integer = IIf(tbxMinRent.Text.Length = 0, -1, tbxMinRent.Text)
        Dim maxRent As Integer = IIf(tbxMaxRent.Text.Length = 0, -1, tbxMaxRent.Text)

        Dim bathrooms As Integer = rblBathrooms.SelectedValue
        Dim bedrooms As Integer = rblBedrooms.SelectedValue

        'Check box list stuff
        Dim Fireplace As Boolean
        Dim Clubhouse As Boolean
        Dim Smoking As Boolean

        Dim i As Integer 'Counter for looping
        For i = 0 To cblAmenities.Items.Count - 1
            If cblAmenities.Items(i).Text = "Fireplace" Then
                Fireplace = cblAmenities.Items(i).Selected
            End If

            If cblAmenities.Items(i).Text = "Clubhouse" Then
                Clubhouse = cblAmenities.Items(i).Selected
            End If

            If cblAmenities.Items(i).Text = "Smoking" Then
                Smoking = cblAmenities.Items(i).Selected
            End If

        Next

        Response.Redirect("webform2.aspx?" & _
            "MinRent=" & minRent.ToString() & _
            "&MaxRent=" & maxRent.ToString() & _
            "&Bathrooms=" & bathrooms.ToString() & _
            "&Bedrooms=" & bedrooms.ToString() & _
            "&Fireplace=" & Fireplace.ToString() & _
            "&Clubhouse=" & Clubhouse.ToString() & _
            "&Smoking=" & Smoking.ToString())
    End Sub


On to webform2 where we run the query.  I added a basic datagrid called DataGrid1 to this page.
<asp:datagrid id="DataGrid1" runat="server"></asp:datagrid>


In the code-behind for webform2, I have the Page_Load function.  First is to get the values off the querystring.  Next I declare the sql objects I need.  Instead of a stored procedure, I am just formulating the select statement in this function.  Note for most of the parameters, if the user has "no preference" i assign it a value of NULL (by using dbnull.value).  In the actual select statement's "where" clause, I go through each filter item and check - Is the variable null?  That means the user had no preference so I move on to the next item.  If the variable is not null, the user must have a preference so I check that the record meets the criteria.  Finally I open my connection, execute the datareader, and then bind the results to my basic datagrid.

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Get values from querystring
        Dim minRent As Integer = CType(Request.QueryString("MinRent"), Integer)
        Dim maxRent As Integer = CType(Request.QueryString("MaxRent"), Integer)
        Dim bathrooms As Integer = CType(Request.QueryString("Bathrooms"), Integer)
        Dim bedrooms As Integer = CType(Request.QueryString("Bedrooms"), Integer)
        Dim fireplace As Boolean = CType(Request.QueryString("Fireplace"), Boolean)
        Dim clubhouse As Boolean = CType(Request.QueryString("Clubhouse"), Boolean)
        Dim smoking As Boolean = CType(Request.QueryString("Smoking"), Boolean)

        'declare Connection and datareader
        Dim conn As New SqlClient.SqlConnection("My Connection String Goes Here")
        Dim rdr As SqlClient.SqlDataReader

        'declare command object
        Dim cmd As New SqlClient.SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.Connection = conn

        'add parameters
        Dim p As SqlClient.SqlParameter

        p = New SqlClient.SqlParameter("@minRent", SqlDbType.Int)
        p.Value = IIf(minRent = -1, DBNull.Value, minRent)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@maxRent", SqlDbType.Int)
        p.Value = IIf(maxRent = -1, DBNull.Value, maxRent)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@bathrooms", SqlDbType.Int)
        p.Value = IIf(bathrooms = -1, DBNull.Value, bathrooms)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@bedrooms", SqlDbType.Int)
        p.Value = IIf(bedrooms = -1, DBNull.Value, bedrooms)
        cmd.Parameters.Add(p)

        'for fireplace and clubhouse, if user did not check box, assign
        'null to the parameter because it likely means "no preference"
        p = New SqlClient.SqlParameter("@fireplace", SqlDbType.Bit)
        p.Value = IIf(fireplace = False, DBNull.Value, True)
        cmd.Parameters.Add(p)

        p = New SqlClient.SqlParameter("@clubhouse", SqlDbType.Bit)
        p.Value = IIf(clubhouse = False, DBNull.Value, True)
        cmd.Parameters.Add(p)

        'Unlike fireplace and clubhouse, if user does not check
        'smoking then they probably are looking for non-smoking
        'apartments.
        p = New SqlClient.SqlParameter("@smoking", SqlDbType.Bit)
        p.Value = smoking
        cmd.Parameters.Add(p)

        cmd.CommandText = "select * from Apartments where " & _
            "(@minRent is null or rent >= @minRent) and " & _
            "(@maxRent is null or rent <= @maxRent) and " & _
            "(@bathrooms is null or bathrooms >= @bathrooms) and " & _
            "(@bedrooms is null or bedrooms >= @bedrooms) and " & _
            "(@fireplace is null or fireplace = @fireplace) and " & _
            "(@clubhouse is null or clubhouse = @clubhouse) and " & _
            "(smoking = @smoking)"

        'run query and bind to a datagrid
        conn.Open()
        rdr = cmd.ExecuteReader()
        DataGrid1.DataSource = rdr
        DataGrid1.DataBind()
        rdr.Close()
        conn.Close()

    End Sub



Well I hope that helps a little bit more.  Let me know if you have more questions or run into problems

-Ian
0
 

Author Comment

by:smith68349
ID: 16994998
Thanks for all of your help. I really apreciate it.  I was able to get what I wanted based on the advice you gave me. Now, one more final question to make this really easy:  How do I close this out so you get credit?
0
 
LVL 10

Accepted Solution

by:
ibost earned 500 total points
ID: 16995011
hehe I think you just scroll along and click "Accept" on whatever comment you want to accept then assign a grade.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

11 Experts available now in Live!

Get 1:1 Help Now