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

``````        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
``````
LVL 29
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Batchelor, Developer and EE Topic AdvisorCommented:
jyparask, that still is nonsense. if x = y ... elseif x<> y ... else. ... will never execute the else.
0
Commented:
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
Commented:
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
Author 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
Commented:
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
Author 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
Commented:
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
Author 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
``````

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
Commented:
What are the values of tripType.SelectedItem.Value and vpreferred.SelectedItem.Value?
0
Author 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>
``````

``````   <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
Commented:
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
Commented:
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
Author 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
Commented:

You should try all the different configurations and paste the generated SQL as well as the results (correct or incorrect) of the query.
0
Commented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author 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
Commented:
forget the CTE version.  Let's work with what I posted.  You can tweak it after we know the logic works.
0
Author Commented:

Thanks for all the help.
0
Author 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.

0
Commented:
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
Author Commented:
Ok, thank you very much.

I hope you can help out on this to keep the continuity.
0
Author Commented:
Exellent work!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.