Error message -Data type mismatch

[ifeyinwa]

Can anyone pls tell me what I have done wrong here. This is the Error message that I get and below it is the actual asp script that the error references. thanks as usual

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/itp/legconfirm.asp, line 42


SQLStmt = SQlstmt & "Where Test =" & Test
Conn.Execute(SQLStmt)

 

[Veep]

Is your variable Test numeric? If not then your statement should look like:

SQLStmt = SQlstmt & "Where Test ='" & Test & "'"

 

[ifeyinwa]

My variable Test is an autonumber field in my access database.

 

[onpnt]

test may be a reserved word

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[Double02]

Is there a space between the rest of the SQLStmt and the "WHERE...." ?

 

[ifeyinwa]

this below is the entire Sql Statement

SQlstmt = "Update tblopgaCom2 Set "
SQLStmt = SQlstmt & "billNo = '" & StrbillNo & "',"
SQLStmt = SQlstmt & "area = '" & Strarea & "',"
SQLStmt = SQlstmt & "Description = '" & StrDescription & "',"
SQLStmt = SQlstmt & "reqDate = '" & StrreqDate & "',"
SQLStmt = SQlstmt & "comments ='" & Strcomments & "',"
SQLStmt = SQlstmt & "priorcomments ='" & Strpriorcomments & "',"
SQLStmt = SQlstmt & "updDate ='" & StrupdDate & "'"
SQLStmt = SQlstmt & "Where Test ='" & Test & "'"
Conn.Execute(SQLStmt)
Conn.Close
set RS=Nothing
Set Conn=Nothing

 

[ifeyinwa]

This is my original sql statement sorry disregard the earlier one sent above


Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
Conn.Open "eiwp"

SQlstmt = "Update tblopgaCom2 Set "
SQLStmt = SQlstmt & "billNo = '" & StrbillNo & "',"
SQLStmt = SQlstmt & "area = '" & Strarea & "',"
SQLStmt = SQlstmt & "Description = '" & StrDescription & "',"
SQLStmt = SQlstmt & "reqDate = '" & StrreqDate & "',"
SQLStmt = SQlstmt & "comments ='" & Strcomments & "',"
SQLStmt = SQlstmt & "priorcomments ='" & Strpriorcomments & "',"
SQLStmt = SQlstmt & "updDate ='" & StrupdDate & "'"
SQLStmt = SQlstmt & "Where Test =" & Test
Conn.Execute(SQLStmt)
Conn.Close
set RS=Nothing
Set Conn=Nothing

 

[amorous]

May be the type mismatch is due to this line:

SQLStmt = SQlstmt & "updDate ='" & StrupdDate & "'"

You may want to try:

SQLStmt = SQlstmt & "updDate =Convert(datetime,'"& StrupdDate & "')"

Just a guess. Not sure though.

VJ

 

[Veep]

Might not be the ultimate solution but Double02 is on the right track. A space would help here: " Where Test =" & Test

 

[onpnt]

faq333-4896 would more then likely find the problem

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[ifeyinwa]

have tried all these leads I am still getting the same error message
* SQLStmt = SQlstmt & "Where Test ='" & Test & "'" -This did not work Test is an autonumber field

* This did not work too " Where Test =" & Test

* This gave me several errors SQLStmt = SQlstmt & "updDate =Convert(datetime,'"& StrupdDate & "')"

something should work???

 

[amorous]

Why dont you response.write your query and post it here, so that someone can suggest you in the right direction.

Response.Write SQLStmt


VJ

 

[ifeyinwa]

And how do I respond.write my query here??

 

[amorous]

After your query and before these lines:

Response.Write SQLStmt

'Conn.Execute(SQLStmt)
' Conn.Close
' set RS=Nothing
' Set Conn=Nothing


and also comment the lines below the Response.Write statment as shown above.

-VJ

 

[Double02]

Well, you response.write it, copy the output and paste into a post here so we can see what the full SQLStmt output looks like.

 

[ifeyinwa]

This is the output I get

Update tblopgaCom2 Set billNo = 'HB0004',area = 'E&P',Description = 'testing',reqDate = '1/14/2004',comments ='watchers',priorcomments ='checking In',updDate ='' Where Test =1

 

[Veep]

As you can see there's nothing there for the variable updDate. Is your table set up to accept a zero length string for that column?

 

[onpnt]

Date encapsulation is wrong

If you would have checked teh FAQ this would have all been shown to you

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[amorous]

I guess inserting the null date value is the problem. You may want to put a condition, something like this:

If updDate="" Then

Dont insert this value

Else

Insert this value

End If

-VJ

 

[logicalunatic]

ifey,

I think the problem is in the Date...

In the SQL statement you have...
----------------
SQLStmt = SQlstmt & "reqDate = '" & StrreqDate & "',"
----------------

It should be...

----------------
SQLStmt = SQLstmt & "reqDate = #" & StrreqDate & "#,"
----------------

Give that a shot...

LL