Solved

Anybody knows why I am getting data type mismatch?

Posted on 2014-11-23
23
181 Views
Last Modified: 2014-11-25
I am using asp.net to connect to an Access 2010 database to do some calculatiions.

However, I am getting "Data Type Mismatch in Criteria Expression.

Below is code:

select distinct h.hourly, IIf([CarTypes] = 'Sedan', h.hourly * 55, IIf([CarTypes] = 'Stretch (6 Passengers)', h.hourly * 75," & _
                                                " IIf([CarTypes] = 'Stretch (8 Passengers)', h.hourly *90,IIf([CarTypes] = 'Stretch (10 Passengers)', h.hourly *95, " & _
                                                " IIf([CarTypes] = 'Stretch (12 Passengers)', h.hourly *110)))))  as Fares, IIf([CarTypes] = 'Sedan', h.hourly * 55, IIf([CarTypes] = 'Stretch (6 Passengers)', h.hourly * 75," & _
                                                " IIf([CarTypes] = 'Stretch (8 Passengers)', h.hourly *90,IIf([CarTypes] = 'Stretch (10 Passengers)', h.hourly *95, " & _
                                                " 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," & _
                                                " IIf([CarTypes] = 'Stretch (8 Passengers)', h.hourly *90,IIf([CarTypes] = 'Stretch (10 Passengers)', h.hourly *95, " & _
                                                " IIf([CarTypes] = 'Stretch (12 Passengers)', h.hourly *110)))))*20/100 as gratuity, fares+tip+gratuity as total from HourlyRates h  " & _
                "inner join carType c on h.hourly = c.hourly Where h.Hourly = @hourly

Open in new window


Hourly is of Number data type.

I will be glad to provide additional information if needed.

Thanks a lot
0
Comment
Question by:sammySeltzer
  • 10
  • 6
  • 4
  • +2
23 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
How is the parameter in the VB code defined? More specifically, what OleDbType have you used?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
As what kind of datatype is @hourly declared? Does it run if you replace the parameter with a hardcoded value?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 150 total points
Comment Utility
I would rewrite it slightly, placing the h.Hourly multiplier outside of the IIF( ) statements to reduce the length of your code.
select distinct h.hourly
, h.hourly * IIf([CarTypes] = 'Sedan', 55, 
           IIf([CarTypes] = 'Stretch (6 Passengers)', 75,
           IIf([CarTypes] = 'Stretch (8 Passengers)', 90,
           IIf([CarTypes] = 'Stretch (10 Passengers)', 95
           IIf([CarTypes] = 'Stretch (12 Passengers)', 110)))))  as Fares
, h.hourly * IIf([CarTypes] = 'Sedan', 55, 
           IIf([CarTypes] = 'Stretch (6 Passengers)', 75,
           IIf([CarTypes] = 'Stretch (8 Passengers)', 90,
           IIf([CarTypes] = 'Stretch (10 Passengers)', 95
           IIf([CarTypes] = 'Stretch (12 Passengers)', 110))))) * 20/100 as Gratuity
, fares+tip+gratuity as total 
FROM HourlyRates h
INNER JOIN carType c on h.hourly = c.hourly 
WHERE h.Hourly = @hourly

Open in new window

Next question, where does the [Tip] field come in?  What table is that in? What is the difference between tip and gratuity

And finally, what is the @hourly variable?  Normally, if you are building this query dynamically, at run time, you would use something like:

strSQL = "SELECT ... FROM ... WHERE h.Hourly = " & varHourly

I know nothing about ASP, but I am an Access expert, so hopefully these suggestions and questions will help you identify your problem.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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

Open in new window

0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 150 total points
Comment Utility
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

Open in new window


First use Switch() function instead of nested iif()s, see: http://www.techonthenet.com/access/functions/advanced/switch.php

BTW, with this function if there is no matches, to avoid error, at the end, I have added 1=1,10000 , replace 10000 to whatever makes sence in your application.

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
instead of 1=1,10000 you can also enter True, 10000
0
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 50 total points
Comment Utility
Since you say that when you hardcode the parameter the query does run, my guess is that the value you're selecting to populate the parameter doesn't match with the datatype in the query. Where are you getting the selection list of the hourly rates from? Does the datatype of that field match with the datatype of hourlyrates.hourly?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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

Open in new window


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

Assisted Solution

by:chaau
chaau earned 150 total points
Comment Utility
The problem is here:
            StrSQL += "inner join carType c on h.hourly = c.hourly Where h.Hourly = '" + HourlyCharter.SelectedValue + "' "

Open in new window

You see, you are not using the @hourly parameter. Instead, you are assigning a value from the combobox to the numeric value. When the sql is rendered the SQL string will become (e.g. for value of 5 it will be):
            inner join carType c on h.hourly = c.hourly Where h.Hourly = '5' 

Open in new window

It is incorrect from Access point of view, as it expects numeric values for the h.Hourly column.
You have several options:
1. remove the single apostrophes from the string:
            StrSQL += "inner join carType c on h.hourly = c.hourly Where h.Hourly = " + HourlyCharter.SelectedValue

Open in new window

2. Use the parameter, as you have posted in your question:
"inner join carType c on h.hourly = c.hourly Where h.Hourly = @hourly"
However, in this case you need to create the OleDbParameter properly, as a numeric parameter.
Dim p2 As New OleDbParameter("@hourly", Integer)
p2.Value = Convert.toInt32(NumericHourlyCharter.SelectedValue)

Open in new window

The latter is more preferred option as it uses the parameterised query that is more secure and is protected against SQL injections
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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>

Open in new window


I am selecting the Values not text
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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

Open 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

Open in new window


post your findings here.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Have you tried to use the hardcoded value in your query, as was suggested above?
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
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

Open 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

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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

Open 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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

15 Experts available now in Live!

Get 1:1 Help Now