Stored Queries in Access. .ASP VbScript

[Guest]

Hi all.

Just whent from MX2004 to CS4

So i have an Access DB.

There is a stored Querie called :

sqCarAllInfoByProdDate

The Sql for the Querie :

SELECT tblCar.Id, tblCar.Vin, tblCar.ChassiName, tblCar.Series, tblCar.SteeringWheel, tblCar.Doors, tblCar.EngineBoor, tblCar.Hp, tblCar.ProdDate, tblEngineType.EngineType, tblExtColor.ExtColorTxt, tblGearBox.GearBox, tblIntUpHolstery.*, tblTransmision.Transmision, tblType.Typee, tblTypeCode.TypeCode
FROM tblTypeCode INNER JOIN (tblType INNER JOIN (tblTransmision INNER JOIN (tblIntUpHolstery INNER JOIN (tblGearBox INNER JOIN (tblExtColor INNER JOIN (tblEngineType INNER JOIN tblCar ON tblEngineType.Id = tblCar.EngineType) ON tblExtColor.ExtColorCode = tblCar.Color) ON tblGearBox.Id = tblCar.GearBox) ON tblIntUpHolstery.IntUpHolsteryCode = tblCar.IntUpHolstery) ON tblTransmision.Id = tblCar.Transmision) ON tblType.Id = tblCar.Typee) ON tblTypeCode.Id = tblCar.TypeCode
WHERE (((tblCar.SteeringWheel)=[@Steering]) AND ((tblCar.ProdDate) Between [@StartDate] And [@EndDate]))
ORDER BY tblCar.ProdDate;

When i try the SQL directly in CS4 (just edit the Variable names offcourse) it works fine.

But i I do :

EXEC sqCarAllInfoByProdDate Steering, EndDate, StartDate

Hit Test, it works. But upload and run on the server gives error :

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/forum/e34m5all.asp, line 37

<%
Dim rsAllByProdDate
Dim rsAllByProdDate_cmd
Dim rsAllByProdDate_numRows

Set rsAllByProdDate_cmd = Server.CreateObject ("ADODB.Command")
rsAllByProdDate_cmd.ActiveConnection = MM_connM5_STRING
rsAllByProdDate_cmd.CommandText = "EXEC sqCarAllInfoByProdDate ?, ?, ?"
rsAllByProdDate_cmd.Prepared = true
rsAllByProdDate_cmd.Parameters.Append rsAllByProdDate_cmd.CreateParameter("param1", 200, 1, 255, rsAllByProdDate__Steering) ' adVarChar
rsAllByProdDate_cmd.Parameters.Append rsAllByProdDate_cmd.CreateParameter("param2", 135, 1, -1, rsAllByProdDate__EndDate) ' adDBTimeStamp
rsAllByProdDate_cmd.Parameters.Append rsAllByProdDate_cmd.CreateParameter("param3", 135, 1, -1, rsAllByProdDate__StartDate) ' adDBTimeStamp

Line 37 : Set rsAllByProdDate = rsAllByProdDate_cmd.Execute
rsAllByProdDate_numRows = 0
%>

But as to my settings the data types match, and it also works with "Test" button

What's up with this???

Anyone

[DEPearson]

I am thinking your problem has something to do with the date values.  I run into datatype match errors in SQL 2005 and higher, 9 times out of ten I have to change the datatype date to Datetime.

What format is rsAllByProdDate__EndDate in? '20090402'

Do you have SQL express avaiable to your host? If so you would be better off using sql express than access for performance reasons.

David Pearson

www.saludalabs.com

www.workhorsecreative.net

[Guest]

Hi.

The Date field is #DD-MM-YYYY#

Yes I have MsSql availiable on my host, but i like to test out stuff in Access first, and also run small databases in Access as I am just a hobby programmer, and access is just to easy to move around 🙂

This Database is only about 10.000 records in it's main table, and I have normalised it by all the rules out there (without going overboard), so it will do for now.

I never had Stored Queries problems in MX2004, and Its strange that it works with "Test" but not Live......

[DEPearson]

Hey,

  I went to google and found this about CS3 and a work around. It might be the same problem.  format (dd/mm/yyyy), but this is
now getting flipped to mm/dd/yyyy.

CS3 breaks MS Access date  format on insert record

http://groups.google.com/group/macromedia.dreamweaver/browse_thread/thread/9e3c6637565226e8?pli=1

David Pearson

http://www.saludalabs.com

http://www.workhorsecreative.net