December 4, 2017 at 4:44 pm
I'm not sure what the best way is to present this issue so please forgive me if I fall short and please ask any qualifying questions necessary.
Here goes..
The code below is the what is triggered by a CmdButton on a userform.
[Code]
On Error GoTo ErrorHandler
Dim UserName As String
Dim strQDefSQL As String, strQueryName As String
Dim strAssignedBy As String, intPriority As Integer, strQkey As String, strBatchID As String
Dim strSQL As String, strDBID As String, strWhere As String, strUserName As String, strExistsFlag As String
Dim RecUsers As dao.Recordset
Dim rstSysInfo As dao.Recordset
Dim strDBName As String
Dim rst As dao.Recordset
Dim RetAssociateID, RetISNumber
RetAssociateID = Environ$("USERNAME")
RetISNumber = Environ$("COMPUTERNAME")
strDBName = strDatabaseName
' Get Host Name / Get Computer Name
UserName = Environ("USERNAME")
Dim rsFindUser As dao.Recordset
Dim strFindUser As String
strFindUser = "SELECT tblSecurity.Mgmt_Sec, * FROM tbl_AssocInfo INNER JOIN tblSecurity ON tbl_AssocInfo.DB_ID = tblSecurity.DB_ID " & _
"WHERE (((tbl_AssocInfo.Assoc_ID)='" & RetAssociateID & "')) OR (((tbl_AssocInfo.Temp_ID)='" & RetAssociateID & "'));"
Set rsFindUser = CurrentDb.OpenRecordset(strFindUser, dbOpenSnapshot)
With rsFindUser
If rsFindUser.RecordCount = 0 Then
'GoTo ErrorHandler
Else
strDBID = ![tbl_AssocInfo.DB_ID]
strUserName = !First_Nm & " " & !Last_Nm
End If
End With
Set rsFindUser = Nothing
'*************************************STORED PROCEDURE*****************************
'Dim strDBID As String
Dim rsDailyHours As dao.Recordset
Dim objControl As Control
'Dim strSQL As String
Dim conn1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Set conn1 = New ADODB.Connection
conn1.ConnectionString = strConnection
conn1.Open
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = strConnection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "Tri.UpdEntTime"
cmd1.Parameters.Refresh
cmd1.Parameters("@Date") = Me.ListTimeDet.Column(5)
cmd1.Parameters("@DBID") = Me.ListTimeDet.Column(0)
cmd1.Parameters("@Hours") = Me!TxtUpdate
cmd1.Parameters("@EditBy") = strUserName
cmd1.Parameters("@EditDate") = Now() <------ ***On this line I receive an: "Application Uses a value of the wrong type for the current operation."***
cmd1.Execute
conn1.Close
Set conn1 = Nothing
Set cmd1 = Nothing
[/Code]
As indicated in the code above I can't get past the: cmd1.Parameters("@EditDate") = Now() line
Below is the Stored Procedure triggered @ the cmd1.Execute line.
[Code]
USE [RAOCProds]
GO
/****** Object: StoredProcedure [tri].[UpdEntTime] Script Date: 12/04/2017 14:57:25 ******/
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [tri].[UpdEntTime]
@Date Date,
@DBID nvarchar (30),
@Hours nvarchar (30),
@EditBy nvarchar (30),
@EditDate Date
AS
UPDATE Tri.DailyHoursWorked
SET HoursWorked = @Hours, HrsEditedBy = @EditBy, HrsEditedDate = @EditDate
WHERE DB_ID = @DBID And Entered = @Date;
[/Code]
Some additional Information:
When I execute the Stored Procedure it comes back as 'Completed Successfully"
Both the table in SQL and in Access are formatted for Date/Time with enough length
Please let me know if I need to provide anything further to help you - - - help me
And as always, Thank You
December 4, 2017 at 5:12 pm
Can you trying changing your line with the error to use Date() instead of Now()
Not sure if that will work but the stored procedure expects a date data type. Or use datetime in both Access and SQL Server.
Sue
December 5, 2017 at 7:06 am
Sue_H - Monday, December 4, 2017 5:12 PMCan you trying changing your line with the error to use Date() instead of Now()
Not sure if that will work but the stored procedure expects a date data type. Or use datetime in both Access and SQL Server.Sue
Hey Sue ~ Thanks so much for responding...
Okay, so I made the following changes and while I am closer, I am still not quite where I need to be.
Per your suggestion, I did change the line with the error from Now() to Date and it worked!! However, as expected it returneds only the date and I need a timestamp value of both date & time.
I then changed the @EditDate data type from Date to DateTime and it ran through again, but again it entered only the date.
I then changed Date back to Now() in the error line thinking perhaps I solved the issue by changing the @EditDate variable data type to DateTime...No such luck it returned the same error as before.
The fields of both the SQL Table and the Access table are set to DateTime data type.
One last change I made was to set Now() to a string variable... same error.
As a last ditch effort I made the following change:
[Code]
cmd1.Parameters("@EditDate") = Now()
[/Code]
To:
[Code]
cmd1.Parameters("@EditDate") = Format(Now(), "YYYYMMDD_HHnnSS")
[/Code]
Same error...As a newbie to this language I am at a complete loss
December 5, 2017 at 7:25 am
MarkW.Rhythm1 - Tuesday, December 5, 2017 7:06 AMSue_H - Monday, December 4, 2017 5:12 PMCan you trying changing your line with the error to use Date() instead of Now()
Not sure if that will work but the stored procedure expects a date data type. Or use datetime in both Access and SQL Server.Sue
Hey Sue ~ Thanks so much for responding...
Okay, so I made the following changes and while I am closer, I am still not quite where I need to be.
Per your suggestion, I did change the line with the error from Now() to Date and it worked!! However, as expected it returneds only the date and I need a timestamp value of both date & time.
I then changed the @EditDate data type from Date to DateTime and it ran through again, but again it entered only the date.
I then changed Date back to Now() in the error line thinking perhaps I solved the issue by changing the @EditDate variable data type to DateTime...No such luck it returned the same error as before.
The fields of both the SQL Table and the Access table are set to DateTime data type.
One last change I made was to set Now() to a string variable... same error.
As a last ditch effort I made the following change:
[Code]
cmd1.Parameters("@EditDate") = Now()
[/Code]
To:
[Code]
cmd1.Parameters("@EditDate") = Format(Now(), "YYYYMMDD_HHnnSS")
[/Code]
Same error...As a newbie to this language I am at a complete loss
Keep it as YYYYMMDD.
Your parameter is defined as date not datetime.
December 5, 2017 at 7:36 am
SOLVED!!!
Okay, so changing the @EditDate in the stored procedure from:
[Code]
@EditDate Date
[/Code]
To:
[Code]
@EditDate DateTime
[/Code]
Did work with Now() as a parameter
However, what I forgot to do after I made that change was to save, or click (! Execute). Once I clicked ! Execute and then ran it... All was good!
Thank You Sue! Your suggestion got me thinking in a different direction which lead me to this solution.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply