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?
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: - ...
-
Dim dTemp As Date
-
If IsDate(Me.Last_Downloaded_Date) Then dTemp = Me.Last_Downloaded_Date
-
.Parameters.Append cmd.CreateParameter("@Last_Downloaded_Date", adDate, adParamInput, 0, dTemp
-
...
2 4281
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: - ...
-
Dim dTemp As Date
-
If IsDate(Me.Last_Downloaded_Date) Then dTemp = Me.Last_Downloaded_Date
-
.Parameters.Append cmd.CreateParameter("@Last_Downloaded_Date", adDate, adParamInput, 0, dTemp
-
...
Assuming the Parameter is in a Date Form, try an explicit conversion: - .Parameters.Append cmd.CreateParameter("@Last_Downloaded_Date", adDate, adParamInput, 0, CDate(Me.Last_Downloaded_Date))
-
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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;...
|
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...
|
by: ganeshvkl |
last post by:
i am use vb.net 2003.
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |