Solved

Anybody knows why I am getting data type mismatch?

Posted on 2014-11-23
23
185 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
ID: 40461289
How is the parameter in the VB code defined? More specifically, what OleDbType have you used?
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461290
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
ID: 40461303
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 28

Author Comment

by:sammySeltzer
ID: 40461307
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
ID: 40461314
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 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 150 total points
ID: 40461315
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 34

Expert Comment

by:Mike Eghtebas
ID: 40461325
instead of 1=1,10000 you can also enter True, 10000
0
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 50 total points
ID: 40461329
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 34

Expert Comment

by:Mike Eghtebas
ID: 40461332
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
ID: 40461333
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
ID: 40461335
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40461339
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 34

Expert Comment

by:Mike Eghtebas
ID: 40461340
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
ID: 40461362
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
ID: 40461370
Have you tried to use the hardcoded value in your query, as was suggested above?
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40461380
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
ID: 40461385
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 34

Expert Comment

by:Mike Eghtebas
ID: 40461390
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
ID: 40461397
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
ID: 40461406
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
ID: 40461417
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 34

Expert Comment

by:Mike Eghtebas
ID: 40461425
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
ID: 40464717
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

809 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