PRB: ODBC Error When You Pass Date Parameters to SQL Stored Procedure (174638)

MORE INFORMATION

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.

PRB: ODBC Error When You Pass Date Parameters to SQL Stored Procedure (174638)

SYMPTOMS

CAUSE

RESOLUTION

STATUS

MORE INFORMATION

Steps to Reproduce Behavior

REFERENCES