Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 174638 - Last Review: May 2, 2006 - Revision: 4.1
PRB: ODBC Error When You Pass Date Parameters to SQL Stored Procedure
This article was previously published under Q174638
One of the following errors occurs when passing a date to a SQL Stored
Procedure:
Microsoft OLE DB Provider for ODBC Drivers error 80004005
At least one parameter contained a type that was not supported.
-OR-
Microsoft OLE DB Provider for ODBC Drivers error 80040e21
Driver not capable.
The data type of the parameter being passed to the stored procedure does
not match the data type the stored procedure is expecting. For example,
passing a value of "7/21/97" to a stored procedure that is expecting
DateTime data will cause the error.
Make sure the data type you are passing to the stored procedure is of the
same type as the stored procedure is expecting. Using Visual InterDev's
Data Command control to create the Active Server Pages (ASP) code to call
your stored procedure will ensure the parameter is of the correct type.
This behavior is by design.
Steps to Reproduce Behavior
- Create or open a Visual InterDev project.
- Add a data connection to the "Pubs" database on your SQL server.
- From the Data View tab, right-click the Stored Procedures folder for
the "Pubs" database and choose "New Stored Procedure" from the context
menu.
- Copy/paste the code between the "===" lines below into the new stored
procedure.
========new stored procedure code==========
CREATE PROCEDURE sp_custom_sel
@custdate datetime
AS
SELECT *
FROM employee
WHERE employee.hire_date=@custdate
===========================================
- Create a new ASP file in the project.
- Copy/paste the code below
======ASP file to call stored procedure===================
<%@ LANGUAGE="VBSCRIPT" %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">
<META HTTP-EQUIV="Content-Type" content="text/html;
charset=iso-8859-1">
<TITLE>Document Title</TITLE>
</HEAD>
<BODY>
<%
Set pubs = Server.CreateObject("ADODB.Connection")
pubs.ConnectionTimeout = Session("pubs_ConnectionTimeout")
pubs.CommandTimeout = Session("pubs_CommandTimeout")
pubs.Open Session("pubs_ConnectionString"),
Session("pubs_RuntimeUserName"), Session("pubs_RuntimePassword")
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set DataCommand1 = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "dbo.""sp_custom_sel"""
cmdTemp.CommandType = 4
Set cmdTemp.ActiveConnection = pubs
Set tmpParam = cmdTemp.CreateParameter("Return Value", 3, 4, 4)
cmdTemp.Parameters.Append tmpParam
'*************************************
'The 7 in the "CreateParamter" below defines the passed value
'-"7/18/61"-as a Date data type. Changing the 7 to 135 defines the
'passed value as a Datetime data type, which the stored procedure is
'expecting. For more information on data type values, see the "Type
'Property" topic in InfoView.
'*************************************
Set tmpParam = cmdTemp.CreateParameter("@custdate",
7, 1, 16,"7/18/97")
cmdTemp.Parameters.Append tmpParam
'DataCommand1.Open cmdTemp, , 0, 1
cmdTemp.execute
%>
</BODY>
</HTML>
==============================================================
- Preview the ASP page. The error described in the SYMPTOMS section above
will appear.
- Change the 7 in the "CreateParameter" line of the ASP to 135. Save and
run the ASP page. No error will appear.
For the latest Knowledge Base articles and other support information on
Visual InterDev and Active Server Pages, see the following page on the
Microsoft Technical Support site:
APPLIES TO
- Microsoft Visual InterDev 1.0 Standard Edition
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Active Server Pages 4.0
| kbprb kbstoredproc kbvisid600fix KB174638 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate