Solved

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

Posted on 2014-12-28
117 Views
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?

``````        Dim StrSQL As String = ""
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)

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

'open recordset to receive db values

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

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
``````
0
Question by:sammySeltzer
• 10
• 10
• 2
• +1

LVL 23

Expert Comment

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
....
``````

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)

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

'open recordset to receive db values

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

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
``````

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

LVL 68

Expert Comment

jyparask, that still is nonsense. if x = y ... elseif x<> y ... else. ... will never execute the else.
0

LVL 23

Expert Comment

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

LVL 45

Expert Comment

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

LVL 28

Author Comment

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

LVL 45

Expert Comment

Something like this
``````        Dim StrSQL As String = ""
Dim strWHERE As String = ""
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
End If

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

'open recordset to receive db values

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

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
``````
0

LVL 28

Author Comment

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

LVL 45

Expert Comment

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

LVL 28

Author Comment

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
``````

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 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

LVL 45

Expert Comment

What are the values of tripType.SelectedItem.Value and vpreferred.SelectedItem.Value?
0

LVL 28

Author Comment

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>
``````

``````   <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>
``````

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 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
End If
Response.Write(StrSQL)
Response.End()
'Now open connection to the db
conn.Open()

'open recordset to receive db values

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

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
``````

Sorry for too much code.
0

LVL 45

Expert Comment

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
``````
0

LVL 45

Expert Comment

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
``````
0

LVL 28

Author Comment

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

LVL 45

Expert Comment

You should try all the different configurations and paste the generated SQL as well as the results (correct or incorrect) of the query.
0

LVL 45

Accepted Solution

aikimark earned 500 total points
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
``````
0

LVL 28

Author Comment

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

LVL 45

Expert Comment

forget the CTE version.  Let's work with what I posted.  You can tweak it after we know the logic works.
0

LVL 28

Author Comment

Thanks for all the help.
0

LVL 28

Author Comment

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.

0

LVL 45

Expert Comment

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

LVL 28

Author Comment

Ok, thank you very much.

I hope you can help out on this to keep the continuity.
0

LVL 28

Author Closing Comment

Exellent work!
0

## Featured Post

### Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page â€¦
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidtâ€¦