Error: Data type mismatch in criteria expression

[Jack]

Hi,
I have posted this problem before. Apprently, the suggestion took care of
the problem. However, still I am getting the above error message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most cases this
session variable value should be zero. However, instead, it is giving null
value and this null value is messing up the update statement which is
supposed to update the table in the database. This session variable value
being null is trying to update a currency field which gives the above error.
The sql statement via response.write is as follows:
UPDATE tblGMISExpenditures_Quarter SET
tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShare= '$0.00' ,
tblGMISExpenditures_Quarter.MBCCShare= '' ,
tblGMISExpenditures_Quarter.UnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.LocalShareUnpaidOblig= '$0.00' ,
tblGMISExpenditures_Quarter.ProjectIncome= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureIncome= '$0.00' ,
tblGMISExpenditures_Quarter.OtherExpense= '$0.00' ,
tblGMISExpenditures_Quarter.ForfeitureExpense= '$0.00' ,
tblGMISExpenditures_Quarter.InterestReceived= '$0.00' ,
tblGMISExpenditures_Quarter.TotalPeriodOutlay= '' ,
tblGMISExpenditures_Quarter.Remarks= ' ' , tblGMISExpenditures_Quarter.Name=
' ' , tblGMISExpenditures_Quarter.Title= ' ' ,
tblGMISExpenditures_Quarter.AreaCode= ' ' ,
tblGMISExpenditures_Quarter.Phone1= ' ' , tblGMISExpenditures_Quarter.Phone2=
' ' , tblGMISExpenditures_Quarter.Date= '1/3/2005' where
tblGMISExpenditures_Quarter.SubgrantIntID = 6032;
Any help or alternative to use a calculated field(not user input field) to
update a database is appreciated. Thanks.

 

[Steven Burn]

If IsNull(TheValue) Then TheValue = 0
'// code to update the DB

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[Bob Barrows [MVP]]

Hi,
I have posted this problem before. Apprently, the suggestion took
care of the problem. However, still I am getting the above error
message.
I am using a session variable to transfer a value from a form to a
confirmation.asp page. This value is a calculated field. For most
cases this session variable value should be zero. However, instead,
it is giving null value and this null value is messing up the update
statement which is supposed to update the table in the database. This
session variable value being null is trying to update a currency
field which gives the above error. The sql statement via
response.write is as follows:

tblGMISExpenditures_Quarter.CurrentOutlay= '$0.00' ,

Assuming CurrentOutlay is one of your currency fields, you need to be aware
that currency is a numeric datatype. Are you really going to the trouble of
putting the quotes and "$" in these values? There is no need to do so.
Currency is a numeric datatype. This SET statement can be changed to:

CurrentOutlay=0

with the same effect as the statement you currently are using, only much
simpler. (Simpler still would be a saved parameter query, but I think I've
already mentioned that to you.)

Also, you have a single table in your query. There is no chance that the
query engine is going to get confused as to which table the fields
referenced in your query are coming from, so there is no need to fully
qualify each field name with that god-awfully long table name: it makes it
so much harder to read. Just use the field names. Only use the table name
qualifier if the query includes two or more tables, and better yet, use
table aliases when you do.

You need to check the session variable value and substitute the word NULL
for it if it is empty:

dim value
value = session("variablename")
if len(value) = 0 then value = "NULL"

sSQL = "...., CurrentOutlay= " & value & ", ..."

Bob Barrows

 

[Jack]

Thanks for your advise here, Steve. I have couple of questions here.
Why is the session variable value when set to 0 in the form gives null value
in the confirmation asp page? The other question is if in a form, there are
few fields that are input by user, while there is one calculated field based
on these input and the calculated field needs to be updated in a database,
what would be the right method to handle the calculated field? It seems there
should be a way other than session variable concept to handle the calculated
field. Does input type hidden is of any use here. Looking for alternatives.
Thanks. Regards,

 

[Steven Burn]

A hidden field would certainly be an easier way of doing it (IMHO anyway).

As for why the value is not null in one page and is null after being passed,
I would guess it is most likely caused by whatever method your using to
"extract" the value.

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[Jack]

Hi Bob,
Sorry for not being able to reply back early. Got caught up with something.
Thanks for your generous help again. I think I am not sure why would you
convert the zero length to null as advised by you at the end of your
comments. Would it not be the other way round i.e. from null to 0. Thanks.

 

[Bob Barrows [MVP]]

Hi Bob,
Sorry for not being able to reply back early. Got caught up with
something. Thanks for your generous help again. I think I am not sure
why would you convert the zero length to null as advised by you at
the end of your comments.

Would it not be the other way round i.e.
from null to 0. Thanks.

Depends on what you want. If your field is not required, you can enter a
Null in it. If it is required, then you probably want to enter a zero. What
you cannot do is this:

...., fieldname=, ...

That will cause an error. Either this:

...., fieldname = Null, ...

or this:

...., fieldname = 0, ...

will work

 

[Jack]

Got it Bob. Thanks.

Depends on what you want. If your field is not required, you can enter a
Null in it. If it is required, then you probably want to enter a zero. What
you cannot do is this:

...., fieldname=, ...

That will cause an error. Either this:

...., fieldname = Null, ...

or this:

...., fieldname = 0, ...

will work

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.