Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

Show different results based on user's choices. This is driving me nuts.

Greetings again, Mates

I am helping a friend build a website for his limousine company.

Potential users are to go to his website, choose their preferred transportation services and are charged based on their choices.

First, a user determines whether s/he wants service from some location to the airport

OR from airport to some location.

OR round trip service from airport to some location and back or vice versa.

Finally, if a user decides to be driven with a Sports Utility Vehicle, an additional $12.00 is added.

So, to recap, ONE WAY service for example has a chage of $50.00

If it is round trip service, the amount becomes $100.00.

If preferred vehicle is SUV, another $12.00 is added.

Tax and gratuities or tips are included in the total.

So far, regardless of what I do, the user is getting charged as though s/he selected SUV as preferred service.

I know this is probably based on the order of my IF statement.

Can someone please assist with this?

Thanks a lot in advance

        Dim StrSQL As String = ""
        Dim rs As OleDbDataReader
        Dim val As String = tripType.SelectedItem.Value
        Dim suv As String = vpreferred.SelectedItem.Value
        'Response.Write(val)
        'Response.End()
        If suv = "SportUtilityVehicle" Then
            If ((val = "one_way_to_airport") Or (val = "one_way_from_airport") Or (val = "one_way_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*(10/100)) AS Discount, (fare-Discount) AS NewFare, (Newfare*(7/100)) AS tax, (newFare*(20/100)) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100)) + 12) AS total FROM Rates "
                StrSQL += " Where city = '" & lblPreviewfromCityState.Text & "' or city =  '" & lblPreviewDropOff.Text & "'"
                'Response.Write(StrSQL)
                'Response.End()
            End If
        ElseIf suv = "SportUtilityVehicle" Then
            If ((val = "round_trip_airport") Or (val = "round_trip_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*(10/100)) AS Discount, (fare-Discount) * 2 AS NewFare, (Newfare*(7/100)) AS tax, (newFare*(20/100)) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100)) + 12) AS total FROM Rates "
                StrSQL += " Where city = '" & lblPreviewDropOff.Text & "'"
            End If
        ElseIf suv <> "SportUtilityVehicle" Then
            If ((val = "round_trip_airport" Or val = "round_trip_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*10/100) AS Discount, (fare-Discount) * 2 AS NewFare, (Newfare*7/100) AS tax, (newFare*20/100) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100))) AS total FROM Rates"
                StrSQL += " Where r.city = '" & lblPreviewfromCityState.Text & "' "
            End If
        ElseIf suv <> "SportUtilityVehicle" Then
            If ((val = "one_way_to_airport" Or val = "one_way_from_airport" Or val = "one_way_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*10/100) AS Discount, (fare-Discount) AS NewFare, (Newfare*7/100) AS tax, (newFare*20/100) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100))) AS total FROM Rates "
                StrSQL += " Where city = '" & lblPreviewfromCityState.Text & "' or city =  '" & lblPreviewDropOff.Text & "'"
                'Response.Write(StrSQL)
                'Response.End()
            End If
        ElseIf suv <> "SportUtilityVehicle" Then
            If ((val = "hourly") And (suv = "SportUtilityVehicle")) Then
                StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, (s.total+12) as total from hourlyQRY s "
                StrSQL += " WHERE s.Hourly = @hourly AND s.carTypes = 'SportUtilityVehicle' "
                'Response.Write(StrSQL)
                'Response.End()
            End If
            Else
                StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, s.total from hourlyQRY s "
                StrSQL += " WHERE s.Hourly = @hourly AND s.carTypes = '" & vpreferred.SelectedValue & "' "
                'Response.Write(StrSQL)
                'Response.End()

            Dim conn As New OleDbConnection(connStr)
            Dim cmd As New OleDbCommand(StrSQL, conn)

            cmd.Parameters.AddWithValue("@hourly", HourlyCharter.SelectedValue)



            'Now open connection to the db
            conn.Open()

            'open recordset to receive db values
            rs = cmd.ExecuteReader()

            ' This acts like the (Not RecordSource.Eof) in ASP 3.0 to loop and retrieve records.
            While rs.Read()

                Dim tipValue As Decimal = rs("tip")
                Dim totValue = rs("total")
                Dim tp As String = [String].Format("{0:C}", tipValue)
                Dim tot As String = [String].Format("{0:C}", totValue)
                lblTip.Text = tp
                lblTotal.Text = tot

            End While
        End If

Open in new window

0
sammySeltzer
Asked:
sammySeltzer
  • 10
  • 10
  • 2
  • +1
1 Solution
 
Ioannis ParaskevopoulosCommented:
Hello,

There is an error in your logic. Have a look at this. It states that
Introduces a condition to be tested if the previous conditional test has failed.
.

Now in your example, lets take the first two cases where you have :
        If suv = "SportUtilityVehicle" Then
            ....
        ElseIf suv = "SportUtilityVehicle" Then
            ....

Open in new window


If the first case succeeds, it will do whatever is in there and then it will go to End If, nevere getting to the second case. If the first case fails, then the second will fail too as they are exactly the same. So, having it this way it will never work as expected.

You should have it like:
        If suv = "SportUtilityVehicle" Then
            If ((val = "one_way_to_airport") Or (val = "one_way_from_airport") Or (val = "one_way_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*(10/100)) AS Discount, (fare-Discount) AS NewFare, (Newfare*(7/100)) AS tax, (newFare*(20/100)) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100)) + 12) AS total FROM Rates "
                StrSQL += " Where city = '" & lblPreviewfromCityState.Text & "' or city =  '" & lblPreviewDropOff.Text & "'"
                'Response.Write(StrSQL)
                'Response.End()
            ElseIf ((val = "round_trip_airport") Or (val = "round_trip_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*(10/100)) AS Discount, (fare-Discount) * 2 AS NewFare, (Newfare*(7/100)) AS tax, (newFare*(20/100)) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100)) + 12) AS total FROM Rates "
                StrSQL += " Where city = '" & lblPreviewDropOff.Text & "'"
            End If
        ElseIf suv <> "SportUtilityVehicle" Then
            If ((val = "round_trip_airport" Or val = "round_trip_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*10/100) AS Discount, (fare-Discount) * 2 AS NewFare, (Newfare*7/100) AS tax, (newFare*20/100) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100))) AS total FROM Rates"
                StrSQL += " Where r.city = '" & lblPreviewfromCityState.Text & "' "
            ElseIf  ((val = "one_way_to_airport" Or val = "one_way_from_airport" Or val = "one_way_no_airport")) Then
                StrSQL = "SELECT city, fare, (fare*10/100) AS Discount, (fare-Discount) AS NewFare, (Newfare*7/100) AS tax, (newFare*20/100) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100))) AS total FROM Rates "
                StrSQL += " Where city = '" & lblPreviewfromCityState.Text & "' or city =  '" & lblPreviewDropOff.Text & "'"
                'Response.Write(StrSQL)
                'Response.End()
            ElseIf ((val = "hourly") And (suv = "SportUtilityVehicle")) Then
                StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, (s.total+12) as total from hourlyQRY s "
                StrSQL += " WHERE s.Hourly = @hourly AND s.carTypes = 'SportUtilityVehicle' "
                'Response.Write(StrSQL)
                'Response.End()
            End If
        Else
            StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, s.total from hourlyQRY s "
            StrSQL += " WHERE s.Hourly = @hourly AND s.carTypes = '" & vpreferred.SelectedValue & "' "
            'Response.Write(StrSQL)
            'Response.End()

            Dim conn As New OleDbConnection(connStr)
            Dim cmd As New OleDbCommand(StrSQL, conn)

            cmd.Parameters.AddWithValue("@hourly", HourlyCharter.SelectedValue)



            'Now open connection to the db
            conn.Open()

            'open recordset to receive db values
            rs = cmd.ExecuteReader()

            ' This acts like the (Not RecordSource.Eof) in ASP 3.0 to loop and retrieve records.
            While rs.Read()

                Dim tipValue As Decimal = rs("tip")
                Dim totValue = rs("total")
                Dim tp As String = [String].Format("{0:C}", tipValue)
                Dim tot As String = [String].Format("{0:C}", totValue)
                lblTip.Text = tp
                lblTotal.Text = tot

            End While
        End If

Open in new window


This would be a working solution, but i am more fond of testing against Booleans or Enums instead of Strings. I would suggest you would have your suv variable as a boolean (I guess you have a checkbox) and your val as Enum. Then you could have Select Case, which i think it would be easier to read. Still, this is just a thought.

Giannis
0
 
QlemoC++ DeveloperCommented:
jyparask, that still is nonsense. if x = y ... elseif x<> y ... else. ... will never execute the else.
0
 
Ioannis ParaskevopoulosCommented:
Qlemo, you arevery right. Not to mention that it seems that the end if seems to be missplaced in the initial code and on my implementation.

As you commented, we must advise the asker that there is still no point in the Else statement in both his code or mine, as suv could either be a "SportUtilityVehicle" (=) or not (<>). The else would never be executed so it should be removed. If there are other cases then they should be properly described in the if statements.

Thanks for the correction, i just missed it.

Giannis
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aikimarkCommented:
My approach would be to construct the SQL, based on the different conditions.  For instance,
* the FROM clause (and SQL template) depends on whether the rental is by the hour or not.  
* the *2 multiplier depends on the one-way/round-trip nature of the rental.
* the +12 premium depends on the SUV nature of the vehicle type
* the WHERE clause depends on multiple conditions.

The hourly where clause, as written, doesn't make sense, since you are pulling your data from an hourlyQRY source.  Why would you need to add a condition in the WHERE clause to limit to hourly rates?
0
 
sammySeltzerAuthor Commented:
aikimark and Qlemo,

How would you two have written it?

I really appreciate all help.

As for the hourlyQRY, on the DB, it is used to calculate fare, discount, tax, tip and generate total.

So, here, I am filtering the results based on how many hours user wishes to use the service and what type of vehicle s/he wants.

On the front end, everything is dropdownlist.
0
 
aikimarkCommented:
Something like this
        Dim StrSQL As String = ""
        Dim strWHERE As String = ""
        Dim rs As OleDbDataReader
        Dim val As String = tripType.SelectedItem.Value
        Dim suv As String = vpreferred.SelectedItem.Value
        'Response.Write(val)
        'Response.End()
        If val = "hourly" Then
            StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, (s.total+^SUV^) as total from hourlyQRY s "
            strWHERE = " WHERE s.Hourly = @hourly"
            If suv = "SportUtilityVehicle" Then
                strWHERE += " AND s.carTypes = 'SportUtilityVehicle'"
            Else
                strWHERE += " AND s.carTypes = '" & vpreferred.SelectedValue & "'"
            End If
        Else
            StrSQL = "SELECT city, fare, (fare*(10/100)) AS Discount, (fare-Discount) * ^RT^ AS NewFare, (Newfare*(7/100)) AS tax, (newFare*(20/100)) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100)) + ^SUV^) AS total FROM Rates "
            strWHERE = " Where city In('" & lblPreviewfromCityState.Text & "' , '" & lblPreviewDropOff.Text & "')"
        End If
        If suv = "SportUtilityVehicle" Then
            StrSQL = Replace(StrSQL, "^SUV^","12")
        Else
            StrSQL = Replace(StrSQL, "^SUV^","0")
        End If
        If val Like "round_trip_*airport" Then
            StrSQL = Replace(StrSQL, "^RT^","2")
        Else
            StrSQL = Replace(StrSQL, "^RT^","1")
        End If

        Dim conn As New OleDbConnection(connStr)
        Dim cmd As New OleDbCommand(StrSQL & strWHERE, conn)

        If val = "hourly" Then
            cmd.Parameters.AddWithValue("@hourly", HourlyCharter.SelectedValue)
        End If
        
        'Now open connection to the db
        conn.Open()

        'open recordset to receive db values
        rs = cmd.ExecuteReader()

        ' This acts like the (Not RecordSource.Eof) in ASP 3.0 to loop and retrieve records.
        While rs.Read()

            Dim tipValue As Decimal = rs("tip")
            Dim totValue = rs("total")
            Dim tp As String = [String].Format("{0:C}", tipValue)
            Dim tot As String = [String].Format("{0:C}", totValue)
            lblTip.Text = tp
            lblTotal.Text = tot

        End While

Open in new window

0
 
sammySeltzerAuthor Commented:
Very superior and elegant code.

However, it is not calculating correctly.

The only part that works correctly is one way trip FROM and TO airport.

Everything else is showing same cost.

Example, there was a one way trip TO  or FROM airport that is set at $57.16 without SUV.

With SUV, it still shows same amount.

With round trip, it still shows same amount.

A silly question, what does ^ do?
0
 
aikimarkCommented:
The ^__^ strings are place holders for values you will assign with the Replace() functions.

Post the generated SQL along with the settings in effect when the SQL was generated.
0
 
sammySeltzerAuthor Commented:
Here is a perfect example of what is going on.

First the SQL:

SELECT city, fare, (fare*(10/100)) AS Discount, (fare-Discount) * 1 AS NewFare, (Newfare*(7/100)) AS tax, (newFare*(20/100)) AS tip, (Newfare + (Newfare*(7/100)) + (newFare*(20/100)) + 12) AS total FROM Rates 

Open in new window


The genereated sql is based on following settings:

1.Round Trip Involving Airport
2.From City of Dacula to Airport
3. The rate is supposed to be $57.15
Original fare of $50.00
Minus 10% discount which is $5.00
Add 7% tax which $3.15
Add 20% tip which is $9.00.

All online purchases get 10%.
All purchases are taxed 7%
All purchases accrue a total of 20% gratuity (tip)

And you get a total of $57.15 for ONE WAY trip TO or FROM.

That amount doubles to $114.30 if ROUND trip.

Then another $12.00 if user's choice is SUV.

As you can see, only the SUV filter is working in the SQL query above.

Second, when I run it in SSMS, I see some strange results.

First the fare amount is correct
The newfare appears to be correct
Grand total is not correct but $12 is added when SUV is selected and not added when SUV is not selected.

So, SUV bit works fine.

However, discount amount is 0, tax is 0 and tip is 0 even though the total amount is correct.

Thanks so much
0
 
aikimarkCommented:
What are the values of tripType.SelectedItem.Value and vpreferred.SelectedItem.Value?
0
 
sammySeltzerAuthor Commented:
TripType should actually be tripType.SelectedValue. Same with vpreferred.

So,
tripType:

one_way_to_airport
one_way_from_airport
round_trip_airport
one_way_no_airport
round_trip_no_airport
hourly

vpreferred:
Sedan
SportUtilityVehicle
Stretch Limo : 6 Passenger
Stretch Limo : 8 Passenger
Stretch Limo : 10  Passenger
Stretch Limo : 12  Passenger


AND just for brevity:

<select name="tripType" id="tripType" class="select " onchange="ShowHideDiv();">
	<option value="">--Select One--</option>
	<option value="one_way_to_airport">One-way trip TO Airport</option>
	<option value="one_way_from_airport">One-way trip FROM Airport</option>
	<option selected="selected" value="round_trip_airport">Round trip involving Airport</option>
	<option value="one_way_no_airport">One-way trip NOT involving Airport</option>
	<option value="round_trip_no_airport">Round trip NOT involving Airport</option>
	<option value="hourly">Hourly/Charter</option>

</select>

Open in new window



   <select name="vpreferred" id="vpreferred" class="select ">
	<option value="">--Select One--</option>
	<option selected="selected" value="Sedan">Lincoln Town Car (Sedan Black - 4 passenger)</option>
	<option value="SportUtilityVehicle">Sport Utility Vehicle</option>
	<option value="Stretch Limo : 6 Passenger">Stretch Limo : 6 Passenger</option>
	<option value="Stretch Limo : 8 Passenger">Stretch Limo : 8 Passenger</option>
	<option value="Stretch Limo : 10  Passenger">Stretch Limo : 10  Passenger</option>
	<option value="Stretch Limo : 12  Passenger">Stretch Limo : 12  Passenger</option>

</select>

Open in new window


UPDATE:

I just wanted to add that if I switched the code to CTE using the following code below, new fare calculates correctly, Discount calculates and displays correctly, tax calculates and displays correctly and tip calculates and displays correctly but the total is calculating but not correctly.

It may be the order of the precedence that is causing it not to calculate correctly.

For instance, if the fare is $50.00, and tax and tip is added for one way trip, result comes out correctly as $57.15. If SUV is used, it comes out as $69.15 (plus $12.00). This is correct.

However, the total is coming out as $126.15 instead of $156.30).

Can this be tweaked?

        Dim StrSQL As String = ""
        Dim strWHERE As String = ""
        Dim rs As SqlDataReader
        Dim val As String = tripType.SelectedItem.Value
        Dim suv As String = vpreferred.SelectedItem.Value
        'Response.Write(val)
        'Response.End()
        If val = "hourly" Then
            StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, (s.total+^SUV^) as total from hourlyQRY s "
            strWHERE = " WHERE s.Hourly = @hourly"
            If suv = "SportUtilityVehicle" Then
                strWHERE += " AND s.carTypes = 'SportUtilityVehicle'"
            Else
                strWHERE += " AND s.carTypes = '" & vpreferred.SelectedValue & "'"
            End If
        Else
            StrSQL = "with CTE_Rates as " & _
"( " & _
 "   Select DISTINCT " & _
            "City, " & _
       "     Fare " & _
     "       FROM Rates " & _
") " & _
"SELECT  " & _
"City, " & _
"Fare,  " & _
"Fare * 0.1 AS Discount,  " & _
"Fare * 0.9 AS NewFare,  " & _
"Fare * 0.063 * ^val^ AS Tax,  " & _
"Fare * 0.18 * ^val^ AS Tip, " & _
"(Fare * 1.143*^val^)+^SUV^ AS Total  " & _
"FROM CTE_Rates "
            strWHERE = " Where city In('" & lblPreviewfromCityState.Text & "' , '" & lblPreviewDropOff.Text & "')"
        End If
        If suv = "SportUtilityVehicle" Then
            StrSQL = Replace(StrSQL, "^SUV^", "12")
        Else
            StrSQL = Replace(StrSQL, "^SUV^", "0")
        End If
        If val Like "round_trip_*airport" Then
            StrSQL = Replace(StrSQL, "^val^", "2")
        Else
            StrSQL = Replace(StrSQL, "^val^", "1")
        End If

        Dim conn As New SqlConnection(connStr)
        Dim cmd As New SqlCommand(StrSQL & strWHERE, conn)

        If val = "hourly" Then
            cmd.Parameters.AddWithValue("@hourly", HourlyCharter.SelectedValue)
        End If
        Response.Write(StrSQL)
        Response.End()
        'Now open connection to the db
        conn.Open()

        'open recordset to receive db values
        rs = cmd.ExecuteReader()

        ' This acts like the (Not RecordSource.Eof) in ASP 3.0 to loop and retrieve records.
        While rs.Read()

            Dim tipValue As Decimal = rs("tip")
            Dim totValue = rs("total")
            Dim tp As String = [String].Format("{0:C}", tipValue)
            Dim tot As String = [String].Format("{0:C}", totValue)
            lblTip.Text = tp
            lblTotal.Text = tot

        End While

Open in new window


Sorry for too much code.
0
 
aikimarkCommented:
Try this change in the first hourly check
        If val = "hourly" Then
            StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, (s.total+^SUV^) as total from hourlyQRY s "
            strWHERE = " WHERE s.Hourly = @hourly"
            If suv = "SportUtilityVehicle" Then
                strWHERE += " AND s.carTypes = 'SportUtilityVehicle'"
            Else
                strWHERE += " AND s.carTypes = '" & suv & "'"
            End If
     Else

Open in new window

0
 
aikimarkCommented:
I suspect that this can be simplified.
        If val = "hourly" Then
            StrSQL = "select DISTINCT s.carTypes, s.Newfare as Fares, s.tip, (s.total+^SUV^) as total from hourlyQRY s "
            strWHERE = " WHERE s.Hourly = @hourly"
            If len(suv) <>0 Then
                strWHERE += " AND s.carTypes = '" & suv & "'"
            End If
        Else

Open in new window

0
 
sammySeltzerAuthor Commented:
Am I trying this with your code or the latest code I posted?

So far, I have not tested the hourly fee. I have been concentrating on the round trip/one way/ SUV trip.

UPDATE:

I just tested the hourly and it works flawlessly.
0
 
aikimarkCommented:
I copied the code from your snippet in your comment, http:#a40522341

You should try all the different configurations and paste the generated SQL as well as the results (correct or incorrect) of the query.
0
 
aikimarkCommented:
Please double check the code that you used from my prior post.  You made an incorrect change that affects the round trip calculations.
        If val Like "round_trip_*airport" Then
            StrSQL = Replace(StrSQL, "^RT^", "2")
        Else
            StrSQL = Replace(StrSQL, "^RT^", "1")
        End If

Open in new window

0
 
sammySeltzerAuthor Commented:
Yes, you are correct. I made the change after testing with your original code.

I am trying to make sense of the RT.

I will try it again but it didn't work the first time.
0
 
aikimarkCommented:
forget the CTE version.  Let's work with what I posted.  You can tweak it after we know the logic works.
0
 
sammySeltzerAuthor Commented:
Ok sure. I will follow your lead sir.

Thanks for all the help.
0
 
sammySeltzerAuthor Commented:
This so cool. It works now.

I am not sure what we did differently.

I can and figure it out later.

Awesome but I need to CTE version to work because this version won't work on SQL Server.

If you want me to close this out and start a new thread, that will be fine.

Thanks for all your help.
0
 
aikimarkCommented:
Close this question and open a new one to change the generated SQL to T-SQL.  It doesn't have to use a CTE.
0
 
sammySeltzerAuthor Commented:
Ok, thank you very much.

I hope you can help out on this to keep the continuity.
0
 
sammySeltzerAuthor Commented:
Exellent work!
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 10
  • 10
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now