Microsoft KB Archive/174638: Difference between revisions
m (Text replacement - ">" to ">") |
m (Text replacement - """ to """) |
||
Line 71: | Line 71: | ||
== CAUSE == | == CAUSE == | ||
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 | 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. | ||
</div> | </div> | ||
Line 98: | Line 98: | ||
<ol> | <ol> | ||
<li>Create or open a Visual InterDev project.</li> | <li>Create or open a Visual InterDev project.</li> | ||
<li>Add a data connection to the | <li>Add a data connection to the "Pubs" database on your SQL server.</li> | ||
<li>From the Data View tab, right-click the Stored Procedures folder for the | <li>From the Data View tab, right-click the Stored Procedures folder for the "Pubs" database and choose "New Stored Procedure" from the context menu.</li> | ||
<li><p>Copy/paste the code between the | <li><p>Copy/paste the code between the "===" lines below into the new stored procedure.</p> | ||
<pre class="codesample"> ========new stored procedure code========== | <pre class="codesample"> ========new stored procedure code========== | ||
CREATE PROCEDURE sp_custom_sel | CREATE PROCEDURE sp_custom_sel | ||
Line 116: | Line 116: | ||
<li><p>Copy/paste the code below</p> | <li><p>Copy/paste the code below</p> | ||
<pre class="codesample"> ======ASP file to call stored procedure=================== | <pre class="codesample"> ======ASP file to call stored procedure=================== | ||
<%@ LANGUAGE= | <%@ LANGUAGE="VBSCRIPT" %> | ||
<HTML> | <HTML> | ||
<HEAD> | <HEAD> | ||
<META NAME= | <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0"> | ||
<META HTTP-EQUIV= | <META HTTP-EQUIV="Content-Type" content="text/html; | ||
charset=iso-8859-1 | charset=iso-8859-1"> | ||
<TITLE>Document Title</TITLE> | <TITLE>Document Title</TITLE> | ||
</HEAD> | </HEAD> | ||
Line 129: | Line 129: | ||
<% | <% | ||
Set pubs = Server.CreateObject( | Set pubs = Server.CreateObject("ADODB.Connection") | ||
pubs.ConnectionTimeout = Session( | pubs.ConnectionTimeout = Session("pubs_ConnectionTimeout") | ||
pubs.CommandTimeout = Session( | pubs.CommandTimeout = Session("pubs_CommandTimeout") | ||
pubs.Open Session( | pubs.Open Session("pubs_ConnectionString"), | ||
Session( | Session("pubs_RuntimeUserName"), Session("pubs_RuntimePassword") | ||
Set cmdTemp = Server.CreateObject( | Set cmdTemp = Server.CreateObject("ADODB.Command") | ||
Set DataCommand1 = Server.CreateObject( | Set DataCommand1 = Server.CreateObject("ADODB.Recordset") | ||
cmdTemp.CommandText = | cmdTemp.CommandText = "dbo.""sp_custom_sel""" | ||
cmdTemp.CommandType = 4 | cmdTemp.CommandType = 4 | ||
Set cmdTemp.ActiveConnection = pubs | Set cmdTemp.ActiveConnection = pubs | ||
Set tmpParam = cmdTemp.CreateParameter( | Set tmpParam = cmdTemp.CreateParameter("Return Value", 3, 4, 4) | ||
cmdTemp.Parameters.Append tmpParam | cmdTemp.Parameters.Append tmpParam | ||
'************************************* | '************************************* | ||
'The 7 in the | '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 | 'passed value as a Datetime data type, which the stored procedure is | ||
'expecting. For more information on data type values, see the | 'expecting. For more information on data type values, see the "Type | ||
'Property | 'Property" topic in InfoView. | ||
'************************************* | '************************************* | ||
Set tmpParam = cmdTemp.CreateParameter( | Set tmpParam = cmdTemp.CreateParameter("@custdate", | ||
7, 1, 16, | 7, 1, 16,"7/18/97") | ||
Line 162: | Line 162: | ||
</pre></li> | </pre></li> | ||
<li>Preview the ASP page. The error described in the SYMPTOMS section above will appear.</li> | <li>Preview the ASP page. The error described in the SYMPTOMS section above will appear.</li> | ||
<li>Change the 7 in the | <li>Change the 7 in the "CreateParameter" line of the ASP to 135. Save and run the ASP page. No error will appear.</li></ol> | ||
Latest revision as of 11:07, 21 July 2020
Article ID: 174638
Article Last Modified on 5/2/2006
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
This article was previously published under Q174638
SYMPTOMS
One of the following errors occurs when passing a date to a SQL Stored Procedure:
-OR-
CAUSE
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.
RESOLUTION
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.
STATUS
This behavior is by design.
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.
REFERENCES
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:
Keywords: kbstoredproc kbvisid600fix kbprb KB174638