Anybody knows why I am getting data type mismatch?

How is the parameter in the VB code defined? More specifically, what OleDbType have you used?

As what kind of datatype is @hourly declared? Does it run if you replace the parameter with a hardcoded value?

chaau / Koen,

Everything follows access data definition, something I have used for quite a bit but not sure what the issue this time.

On the markup, everything is pulled from the db to prepoluate dropdownlist.

And yes, If I hardcode it, it seems to work but there it is more than that but first let's see if we get this issue worked out first.

Sorry to dump on you but here is more code:

        Dim StrSQL As String
        Dim rs As OleDbDataReader
        Dim val As String = tripType.SelectedItem.Value
        If val = "one_way_to_airport" Or val = "one_way_from_airport" Or val = "round_trip_airport" Or val = "one_way_no_airport" Or val = "round_trip_no_airport" Then
            StrSQL = "select r.fare, r.tip, r.total from Rates r "
            StrSQL += " Where r.city = @city "
            'Response.Write(StrSQL)
            'Response.End()
        ElseIf val = "hourly" Then
            StrSQL = "select distinct h.hourly, IIf([CarTypes] = 'Sedan', h.hourly * 55, IIf([CarTypes] = 'Stretch (6 Passengers)', h.hourly * 75,"
            StrSQL += "IIf([CarTypes] = 'Stretch (8 Passengers)', h.hourly *90,IIf([CarTypes] = 'Stretch (10 Passengers)', h.hourly *95, "
            StrSQL += "IIf([CarTypes] = 'Stretch (12 Passengers)', h.hourly *110)))))  as Fares, IIf([CarTypes] = 'Sedan', h.hourly * 55, IIf([CarTypes] = 'Stretch (6 Passengers)', h.hourly * 75,"
            StrSQL += "IIf([CarTypes] = 'Stretch (8 Passengers)', h.hourly *90,IIf([CarTypes] = 'Stretch (10 Passengers)', h.hourly *95, "
            StrSQL += "IIf([CarTypes] = 'Stretch (12 Passengers)', h.hourly *110)))))*7/100 as tip,IIf([CarTypes] = 'Sedan', h.hourly * 55, IIf([CarTypes] = 'Stretch (6 Passengers)', h.hourly * 75,"
            StrSQL += "IIf([CarTypes] = 'Stretch (8 Passengers)', h.hourly *90,IIf([CarTypes] = 'Stretch (10 Passengers)', h.hourly *95, "
            StrSQL += "IIf([CarTypes] = 'Stretch (12 Passengers)', h.hourly *110)))))*20/100 as gratuity, fares+tip+gratuity as total from HourlyRates h  "
            StrSQL += "inner join carType c on h.hourly = c.hourly Where h.Hourly = '" + HourlyCharter.SelectedValue + "' "
            'Response.Write(StrSQL)
            'Response.End()
        End If
        ' Initialize Database Connection
        Dim connStr As String = ConfigurationManager.ConnectionStrings("ALSConnectionString").ConnectionString
        Dim conn As New OleDbConnection(connStr)
        Dim cmd As New OleDbCommand(StrSQL, conn)

        'We use parametized query to prevent sql injection attack
        Dim p1 As New OleDbParameter("@city", lblPreviewfromCityState.Text)
        Dim p2 As New OleDbParameter("@hourly", HourlyCharter.SelectedValue)
        cmd.Parameters.Add(p1)
        cmd.Parameters.Add(p2)

        '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()
            '    If rs("city") <> "" Then
            Dim tipValue As Decimal = rs("tip")
            Dim totValue = rs("total")
            '   Else
            '   End If
            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

Select allOpen in new window

Dale,

Very sorry, I posted a response to the other two posters before I got yours.

The code I posted now should bring a bit more clarity.

The code is  a bit more complex than what I posted.

I am doing this for a limousine company.

Potential riders are given several options, three of which are to decide if their ride is within city limits.

if Yes, then they are billed from one point of the city to another point for a set amount.

This includes fares, Tips, tax and of course total.

Then user can also decide to do charter ride.

In this case, depending of what type of car they select, they are charged a different amount, plus tip, plus tax and then total.

So, that's how tip comes about.

I can explain more if you need me to.

instead of 1=1,10000 you can also enter True, 10000

This is easy to read:

select distinct h.hourly, Switch([CarTypes] = 'Sedan', 55, [CarTypes] = 'Stretch (6 Passengers)', 75," & _
                    " [CarTypes] = 'Stretch (8 Passengers)', 90, [CarTypes] = 'Stretch (10 Passengers)', 95, " & _
                    " [CarTypes] = 'Stretch (12 Passengers)', 110 ,1=1, 10000)* h.hourly   as Fares, " & _
                    " Switch([CarTypes] = 'Sedan', 55, [CarTypes] = 'Stretch (6 Passengers)', 75," & _
                    " [CarTypes] = 'Stretch (8 Passengers)', 90, [CarTypes] = 'Stretch (10 Passengers)', 95, " & _
                    " [CarTypes] = 'Stretch (12 Passengers)', 110)* h.hourly*0.7 as tip, " & _
                    " Switch([CarTypes]  = 'Sedan', 55, [CarTypes] = 'Stretch (6 Passengers)', 75," & _
                    " [CarTypes] = 'Stretch (8 Passengers)', 90,[CarTypes] = 'Stretch (10 Passengers)', 95, " & _
                    " [CarTypes] = 'Stretch (12 Passengers)', 110, 1=1, 10000)*0.20*h.hourly as gratuity,  " & _
                    " fares+tip+gratuity as total from HourlyRates h " & _
                    " inner join carType c on h.hourly = c.hourly Where h.Hourly = @hourly

Select allOpen in new window

Also try:    ...   Where cstr(h.Hourly) = cstr(@hourly)

Koen,

As stated, I am populating hourly data dynamically from the database.

The data type is defined as Number on the database and here is the dropdownlist code with values:

<select name="HourlyCharter" id="HourlyCharter" class="select ">
	<option value="3">3 Hours</option>
	<option value="4">4 Hours</option>
	<option value="5">5 Hours</option>
	<option value="6">6 Hous</option>
	<option value="7">7 Hours</option>
	<option value="8">8 Hours</option>
	<option value="9">9 Hours</option>
	<option value="10">10 Hours</option>
	<option value="11">11 Hours</option>
	<option value="12">12 Hours</option>
	<option value="13">Over 12 Hours</option>

</select>

Select allOpen in new window

I am selecting the Values not text

chaau,

Great catch!

I should know better especially someone like me who has spent most of my programming life using classic asp.

Let me try your solution.

I must say that Mike's code is very elegant, much better than mine.

Run the following tests to see what you get:

select @hourly as Pram,  h.hourly from HourlyRates h " & _
                    " inner join carType c on h.hourly = c.hourly

Select allOpen in new window

Also run:

select Vartype(@hourly) as PramType,  Vartype(h.hourly) as hourlyType from HourlyRates h " & _
                    " inner join carType c on h.hourly = c.hourly

Select allOpen in new window

post your findings here.

Ok, here is what works and what doesn't.

This is getting to be more frustrating that I was prepared for.

BTW: Before I posted here, I have been working on this for over 5 hours.

Ok, using parametized query (my preference) with the changes chaau made, that doesn't work. Still spitting out same data type mismatch.

When I used the form value - " + HourlyCharter.SelectedValue + ", the error was gone.

However, it doesn't matter whether I select 3 hours or 12 hours, is giving me same value.

Have you tried to use the hardcoded value in your query, as was suggested above?

I am not sure I understand what you mean by using hardcoded value.

Earlier in my post, I was asked if I tried using hardcoded value to see if it works and I said yes.

I used hardcoded value to see if it works and it does but I don't want to hardcode the values.

That's akin to hiding the real problem preventing this from working.

I am wondering if checking for the value of TripType before running sql is the probem.

So, does this query works?:

select distinct h.hourly, Switch([CarTypes] = 'Sedan', 55, [CarTypes] = 'Stretch (6 Passengers)', 75," & _
                    " [CarTypes] = 'Stretch (8 Passengers)', 90, [CarTypes] = 'Stretch (10 Passengers)', 95, " & _
                    " [CarTypes] = 'Stretch (12 Passengers)', 110 ,1=1, 10000)* h.hourly   as Fares, " & _
                    " Switch([CarTypes] = 'Sedan', 55, [CarTypes] = 'Stretch (6 Passengers)', 75," & _
                    " [CarTypes] = 'Stretch (8 Passengers)', 90, [CarTypes] = 'Stretch (10 Passengers)', 95, " & _
                    " [CarTypes] = 'Stretch (12 Passengers)', 110)* h.hourly*0.7 as tip, " & _
                    " Switch([CarTypes]  = 'Sedan', 55, [CarTypes] = 'Stretch (6 Passengers)', 75," & _
                    " [CarTypes] = 'Stretch (8 Passengers)', 90,[CarTypes] = 'Stretch (10 Passengers)', 95, " & _
                    " [CarTypes] = 'Stretch (12 Passengers)', 110, 1=1, 10000)*0.20*h.hourly as gratuity,  " & _
                    " fares+tip+gratuity as total from HourlyRates h " & _
                    " inner join carType c on h.hourly = c.hourly Where h.Hourly = 3

Select allOpen in new window

If it does then this one should do as well:

                    " inner join carType c on h.hourly = c.hourly Where h.Hourly = "  + HourlyCharter.SelectedValue

Select allOpen in new window

chaau,

You have sammy's ear. Ask him to run:

1.  select @hourly as Pram,  h.hourly from HourlyRates h " & _
                    " inner join carType c on h.hourly = c.hourly

2. select Vartype(@hourly) as PramType,  Vartype(h.hourly) as hourlyType from HourlyRates h " & _
                    " inner join carType c on h.hourly = c.hourly

to make sure these return data and what their datatypes are.

chaau, maybe the query is incorrect.

Here is why. I am getting a value of $627.00.

The query may be incorrect but that's beside the point now.

The problem if I pass the dropdown value - where h.hourly = " + hourlyCharter.SelectedValue + " , I get same value no matter what I passed as the value of hourlyCharter.

I really appreciate the time and patience.

eghtebas,

Sorry, I am not ignoring you or anyone.

I am just interested in who helps me solve this problem.

I am about to run your suggestions now.

Please give me a sec and I will come back with results.

The first part of your query gives me out of index exception error that's because it was trying to reference tip on this code below:

        While rs.Read()
            '    If rs("city") <> "" Then
            Dim tipValue As Decimal = rs("tip")
            Dim totValue = rs("total")
            '   Else
            '   End If
            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

Select allOpen in new window

When I commented out entire WHILE loop, the query produced nothing.

I think I know what is going on here.

This code below:

        Dim val As String = tripType.SelectedItem.Value
        If ((val = "one_way_to_airport") Or (val = "one_way_from_airport") Or (val = "round_trip_airport") Or (val = "one_way_no_airport") Or (val = "round_trip_no_airport")) Then
show one code

elseIf val="hourly" show the code we have been having problem with.

I think this where the problem lies.

I have to step out now. Usually what I do in situations like this (where their are lots of moving parts), I simplify my query as much as possible to make it work, Then I add more and more elements I need to it until I locate the error.

Will check back later,

Mike

I was able to figure out why the code wasn't working when I initially select hourly.

That is solved now. I still can't figure out (yet) why I continue to get type mismatch after using the CONVERT function. Currently, I am filtering directly with hourlyCharter.SelectedValue.

For now, that's good enough. I can worry about the other stuff later.

Thanks all for your contributions.