By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 1,327 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Error: Data type mismatch in criteria expression

P: n/a
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.
Jul 22 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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!
"Jack" <Ja**@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...
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.

Jul 22 '05 #2

P: n/a
Jack wrote:
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: <snip> 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
--
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.
Jul 22 '05 #3

P: n/a
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" wrote:
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!
"Jack" <Ja**@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...
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.


Jul 22 '05 #4

P: n/a
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" <Ja**@discussions.microsoft.com> wrote in message
news:CF**********************************@microsof t.com...
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" wrote:
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!
"Jack" <Ja**@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...
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.


Jul 22 '05 #5

P: n/a
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]" wrote:
Jack wrote:
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:

<snip>
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
--
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.

Jul 22 '05 #6

P: n/a
Jack wrote:
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.
zero length string <> Null
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
"Bob Barrows [MVP]" wrote:
Jack wrote:
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:

<snip>
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
--
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.


--
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.
Jul 22 '05 #7

P: n/a
Got it Bob. Thanks.

"Bob Barrows [MVP]" wrote:
Jack wrote:
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.


zero length string <> Null
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

"Bob Barrows [MVP]" wrote:
Jack wrote:
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:
<snip>
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
--
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.


--
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.

Jul 22 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.