473,325 Members | 2,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Passing a date variable to an SQL stored procedure

I have some Access 2016 VB code which is used to pass variables to a SQL stored procedure: -

With cmd

.ActiveConnection = tmpConn_String
.CommandType = adCmdStoredProc
.CommandText = "SEL_Suppliers_New"

.Parameters.Append cmd.CreateParameter("@Last_Downloaded_Date", adDate, adParamInput, 0, Me.Last_Downloaded_Date)

.Parameters.Append cmd.CreateParameter("@Branch_Code", adVarChar, adParamInput, 5, Forms![F_Current_User]![Branch_Code])


The procedure does not have an issue with passing the @Branch_Code variable but it does with the @Last_Downloded_Date variable, and when run I get this error: -

“Error converting data type varchar to datetime”

The date that I am trying to pass is: -

“25/10/2016 04:44:53”

The stored procedure looks like this: -

alter PROCEDURE [dbo].[SEL_Suppliers_New]

@Branch_Code nvarchar(5),
@Last_Downloaded_Date datetime

AS

BEGIN

SET NOCOUNT ON;

IF @Branch_Code = 'Admin'

BEGIN

SELECT *, GETDATE() AS Last_Downloaded_Date FROM

T_Suppliers
WHERE Entered_Date > @Last_Downloaded_Date
ORDER BY Supplier ASC

If I run the SP inside SQL and paste this date in it works correctly.

Can anyone assist me with this issue?
Oct 25 '16 #1

✓ answered by jforbes

The error “Error converting data type varchar to datetime” looks to be a very SQL error as both of those Data Types are SQL Data Types; Access' VBA Data Types are String and Date. Which could mean that SQL is getting the Parameter as a String(varchar) Data Type and not a Date (DateTime), even though it is called out to be Date by the use of adDate.

You may want to try to create a Date Variable to pass to .CreateParameter method:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Dim dTemp As Date
  3. If IsDate(Me.Last_Downloaded_Date) Then dTemp = Me.Last_Downloaded_Date
  4. .Parameters.Append cmd.CreateParameter("@Last_Downloaded_Date", adDate, adParamInput, 0, dTemp 
  5. ...

2 4281
jforbes
1,107 Expert 1GB
The error “Error converting data type varchar to datetime” looks to be a very SQL error as both of those Data Types are SQL Data Types; Access' VBA Data Types are String and Date. Which could mean that SQL is getting the Parameter as a String(varchar) Data Type and not a Date (DateTime), even though it is called out to be Date by the use of adDate.

You may want to try to create a Date Variable to pass to .CreateParameter method:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Dim dTemp As Date
  3. If IsDate(Me.Last_Downloaded_Date) Then dTemp = Me.Last_Downloaded_Date
  4. .Parameters.Append cmd.CreateParameter("@Last_Downloaded_Date", adDate, adParamInput, 0, dTemp 
  5. ...
Oct 25 '16 #2
ADezii
8,834 Expert 8TB
Assuming the Parameter is in a Date Form, try an explicit conversion:
Expand|Select|Wrap|Line Numbers
  1. .Parameters.Append cmd.CreateParameter("@Last_Downloaded_Date", adDate, adParamInput, 0, CDate(Me.Last_Downloaded_Date))
  2.  
Oct 30 '16 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: mirza i | last post by:
in asp i have this line: ..... Set paramsx = cmdUpdatex.CreateParameter ("@invdate", addate, adParamInput, 50, invdate) cmdUpdatex.Parameters.Append paramsx ... (the invdate value is a...
3
by: mhk | last post by:
Hi, i have "req_date" column of "datetime" type in Database table besides other columns. From my Web page, i am calling the Stored Procedure with variable parameter "Search_Date" of...
2
by: Bob | last post by:
I'm new to Access projects and SQL server and am not a veteran VB programmer. There's a cry for help! I'm attempting to print the current form on screen by using a command button which the user...
3
by: nandan | last post by:
Hi, Has any one ever compared the performance of calling a DataTable's Select method with a stored procedure doing the same thing? My point is: dataRows = DataTable.Select(filter) is better or...
1
by: vncntj | last post by:
I have a C#.NET that simply passes 6 values to a Stored Procedure. But I'm trying to get the (Default.aspx.cs page) to handle passing the values to the sp. The goal is to pass the values and see...
3
by: yogsadafal | last post by:
I have a problem while passing Null parameter for Date To stored procedure .... it throws exception.... because i have set Driver URL with parameter noAccessToProcedureBodie=true ..... thanks...
4
Soniad
by: Soniad | last post by:
Hello, I am passing date and time individually to stored procedure as parameters as follows : Exec mySP '01/31/2009' , '5:03:56 PM' whithin this stored procedure, i have declared one...
4
code green
by: code green | last post by:
Within a stored procedure I am creating a table from an Excel spreadsheet /*Create tmp table from XLS*/ SELECT * INTO tmp_cons FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;...
2
by: hemantc87 | last post by:
i have created this function with a parameter offset and i want to access the database using stored procedure but the code and stored procedure i have written below is not working...what is the right...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.