Data type mismatch in criteria expression.

Hi,

I am santosh sending this mail regards database errorin vc++ 6.0 application . actually I am trying to convert old MS SQL server database to MS Access so what are the changes should I do in vc++ source code so it can work fine with MS Access. I am trying but it giving error on this statement as follows :-

strSQLString.MakeUpper();

ReturnCode = SQLExecDirect(m_hstmt,(UCHAR*)((LPCTSTR)strSQLString),SQL_NTS);

if (ReturnCode)

{

CDBException* pDBEx = new CDBException();

pDBEx->BuildErrorString(m_pDatabase,m_hstmt);

throw ( pDBEx);

return 0;

}

And this is sql statement witch is storing data in data type ofCStringstrSQLString;and the statement is as follows:-

strSQLString.Format("SELECT TOP %d MESSAGEID, MOBILENUMBER,MESSAGETEXT, SENDERID, RECDDATETIME, SENDDATETIME, SUBSCUSERID, SUBSCID, MESSAGESTATUS, RETURNCODE , PRIORITYLEVEL, VALIDITYDATETIME, MCL,CONFIRMREQD,DATAENCODING FROM SMSMESSAGES WHERE MESSAGESTATUS = 0", nRowsToFetch );

so I getting error likeby this in Debug mode throw (pDBEx);

Data type mismatch in criteria expression.

State:22005,Native:-3030,Origin:[Microsoft][ODBC Microsoft Access Driver]

And I am connecting throw database by DSN .

Please tell me the way for this is possible or not I am waiting for response .

Best regards,

S. Santosh Naidu

Is SMSMESSAGES.MESSAGESTATUS a varchar? If so, you'd want to rewrite your where clause to WHERE MESSAGESTATUS = '0' or similar. Send along your DDL for the table if this isn't the problem.

Hope this helps,
Josh Lindenmuth

Data type mismatch in criteria expression.

I am using access 2000 database and getting the following error:

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

The quiry is:
"SELECT AffID, Date, Hits, Payment, Commission, Paid, Sum(Hits) AS HitsTotal, Sum(Payment) AS PaymentTotal, Sum(Commission) AS CommissionTotal, Sum(Paid) AS PaidTotal, Commission - Paid AS Balance from earned WHERE earned.Date Between #"& startDate &"# and #"& endDate &"# AND AffID='"& AffId &"' GROUP by AffID, Date, Hits,Payment, Commission, Paid order by Date ASC"

Both AffID and Hits are number fields; Payment, Commission and Paid are currency fields; and Date is Date/Time field. While trying both AffID and date range get posted from the forms to the page having this script but still error is there. I tried hard but in vain. I would appreciate the help in getting rid of this error. ThanksDont put quotes around your AffID in your where clause. your code should be "AND AffID=" & AffID & " Group by"......
not "AND AffID='"& AffId &"' GROUP by"

Just remove the single quotes

Data Type Mismatch in criteria Expression, Help~

Error in Explorer:
Data type mismatch in criteria expression.

Following codings:

Dim lowestPrice As Double
Dim highestPrice As Double

lowestPrice = FormatCurrency(txtLow.Text, 2)
highestPrice = FormatCurrency(txtHigh.Text, 2)

lblLabel.Text = lowestPrice

.
.

"UNION " & _
"SELECT p.ProductID, p.ProductTitle FROM Product p " & _
"WHERE (p.Price > '" & FormatCurrency(lowestPrice, 2) & "' AND p.Price < '" & FormatCurrency(highestPrice, 2) & "') " & _
"ORDER BY p.ProductTitle"

I don't know where the error goes wrong in here.. previously because of the union missing one spacing that resulted in syntax error, after i inserted a space to it.. it shows me Data type mismatch the criteria expression. Is it because in my sql coding i cant use FormatCurrency for ASP.net? please give me a hand.. thank you

Contact me at: ryuichi_ogata86@.hotmail.com
ICQ me at: 18750757Please abandon this approach and use parameters instead. Use something like this:

Dim mySQL as string = "SELECT..." &_
"UNION " & _
"SELECT p.ProductID, p.ProductTitle FROM Product p " & _
"WHERE (p.Price > @.lowestPrice AND p.Price < @.highestPrice) " & _
"ORDER BY p.ProductTitle"
Dim myConnection as New SQLConnection(strConn)
Dim myCommand as New SQLCommand(mySQL, myConnection)
 myCommand.CommandType = CommandType.Text
Dim myParameter1 As SqlParameter = New SqlParameter("@.lowestPrice", SqlDbType.Money)
myParameter1.Value = lowestPrice
myCommand.Parameters.Add(myParameter1)
Dim myParameter2 As SqlParameter = New SqlParameter("@.highestPrice", SqlDbType.Money)
myParameter2.Value = highestPrice
myCommand.Parameters.Add(myParameter2)
myConnection.Open()

This may not take care of your problem but will make your code more secure and should bring it closer.

Terri

Data type mismatch in criteria expresioin

Hi again, I'm having problems with an insert record page to insert records into a msaccess database via sql and dreamweaver mx.asp pages.

There are four date fields that can be entered (all not required), but it keep getting the

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

from my documentation there seems to be a known problem with msaccess and update/insert server behaviours....

Has anyone managed to overcome this issue, and if so, how ??

thanks for your help...That may be because of the Date format on the access database.
Try to insert the date in this format:
aaaa-mm-dd

______________
Paulo Gonalves|||thanks heaps...I actually got it sorted about 20 minutes before I read your reply...and you were right...I had the msaccess date formats set to an input mask that I don't think dreamweaver took a liking to...

cheers.