Construct an EXECUTE string

Question

Construct an EXECUTE string

OceanDiscovery

Old Hand

Points: 355
More actions
December 12, 2010 at 9:37 pm

#230432

I have an Access application that I use with SQL Server.
I have some functions that build a string to execute my stored procedures in SQL.
Normally these work just fine; however I've come across an area where they don't work (and I've gone round and round this in my head so much that I've driven myself to distraction so need a fresh approach).
The string builds exactly as I expect it to, the problem is that the structure doesn't work with parameters that have Date AND Time data in a DATETIME data type.
If the string builds as
EXECUTE qDbReports '2010/12/13', '2010/12/13', 88, 1
the Stored Procedure runs fine (and inserts a record; albeit with no time part).
If the string builds as
EXECUTE qDbReports '2010/12/13 04:13:57 p.m.', '2010/12/13 04:13:58 p.m.', 88, 1
I get the error message:
"Error converting data type varchar to datetime."
The Procedure is:
ALTER PROCEDURE [dbo].[qDbReports]
(@StartTime DATETIME, @EndTime DATETIME, @ReportId INT, @userid INT)
AS
SET NOCOUNT ON
INSERT INTO dbReports
(ReportId, UserId, StartTime, EndTime, RunTime)
SELECT @ReportId AS pReportId, @userid AS pUserId, @StartTime AS pStartTime, @EndTime AS pEndTime, DateDiff(ms,@StartTime, @EndTime) AS pRunTime
RETURN
Any help would be much appreciated as I'm sure I'm missing something quite simple.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Instead of constructing a sql string containing a string representation of the parameters, you'd better use a command object and pass the parameters as such.

Example:

Dim cmd, sql, prm, conn

Set cmd = createObject("ADODB.Command")

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "qDbReports"

'Initialize your connection...

cmd.ActiveConnection = conn

'Set up parameters

cmd.parameters.add cmd.CreateParameter("@StartTime", adDBTimeStamp, adParamInput, , vbaStartDateVariable)

cmd.parameters.add cmd.CreateParameter("@EndTime", adDBTimeStamp, adParamInput, , vbaEndDateVariable)

cmd.parameters.add cmd.CreateParameter("@ReportId", adInteger, adParamInput, , vbaReportIdVariable)

cmd.parameters.add cmd.CreateParameter("@UserId", adInteger, adParamInput, , vbaUserIdVariable)

'Execute procedure

cmd.Execute

Hope this helps,

Gianluca

-- Gianluca Sartori

OceanDiscovery Old Hand Points: 355 More actions · Answer 2

We have a number of functions that have been built with differing permutations of parameters, allowing us to call any stored procedure from these functions.

It seemed to make the most sense to get what we had to work; but this didn't seem to be getting me anywhere fast!

I've never used the ADO commands for parameters before but have worked with what you posted earlier. I initially had a connection error but I've changed this to address CurrentProject.

I now get an error message "Object doesn't support this property or method" for the line:

cmd.Parameters.Add cmd.CreateParameter("@StartTime", adDBTimeStamp, adParamInput, , p1)

I must have missed a whole lot (having not used these before).

Any help would be awesome.

Cheers

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

I don't know exactly what is causing the error. It seems to be related to the parameter type.

You could try to create the parameters reading from the stored procedure metadata, using something like this:

cmd.parameters.refresh

cmd.parameters("@StartTime").value = p1

cmd.parameters("@EndTime").value = p2

...

Hope this helps.

Gianluca

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Mac_85 (12/13/2010)
I must have missed a whole lot (having not used these before).

ADO Command Objects can help you avoiding sql injection and, whenever the procedure is called multiple times, can avoid recompilations.

Give it a read:

http://msdn.microsoft.com/en-us/library/ms677502(VS.85).aspx

-- Gianluca Sartori

OceanDiscovery Old Hand Points: 355 More actions · Answer 5

Thanks Gianluca

I'll take a look and see what I can work out.

I'd still quite like to know why the string passed to SQL only works with date values and fails if there is a time part to it...

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Mac_85 (12/13/2010)
We have a number of functions that have been built with differing permutations of parameters, allowing us to call any stored procedure from these functions.
It seemed to make the most sense to get what we had to work; but this didn't seem to be getting me anywhere fast!
I've never used the ADO commands for parameters before but have worked with what you posted earlier. I initially had a connection error but I've changed this to address CurrentProject.
I now get an error message "Object doesn't support this property or method" for the line:
cmd.Parameters.Add cmd.CreateParameter("@StartTime", adDBTimeStamp, adParamInput, , p1)
I must have missed a whole lot (having not used these before).
Any help would be awesome.
Cheers

Just remove the periods from A.M. and P.M. Seriously... check it out... (I used an explicit conversion here)...

SELECT CAST('2010-01-01 12:00:00 p.m.' AS DATETIME)

SELECT CAST('2010-01-01 12:00:00 pm' AS DATETIME)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OceanDiscovery Old Hand Points: 355 More actions · Answer 7

Thanks Jeff.

I appreciate your reply (as I can't beleive I missed that! but when you look at two differences a lot I guess that happens!).

I'll see if I can pass the string this way.

Cheers

OceanDiscovery Old Hand Points: 355 More actions · Answer 8

Thanks Jeff; I managed to work out how to send the date/time without the periods.

However, even as this:

"EXECUTE qDbReports '2010-17-12 07:56:52 AM', '2010-17-12 07:56:53 AM', 88, 1"

I still receive the same error message:

"Error converting data type varchar to datetime."

So I'll have to look further into Gianluca's post.

Thanks for your time.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Mac_85 (12/16/2010)
Thanks Jeff; I managed to work out how to send the date/time without the periods.
However, even as this:
"EXECUTE qDbReports '2010-17-12 07:56:52 AM', '2010-17-12 07:56:53 AM', 88, 1"
I still receive the same error message:
"Error converting data type varchar to datetime."
So I'll have to look further into Gianluca's post.
Thanks for your time.

The 17 and the 12 are in the wrong places for an ISO format. The data in the format above is yyyy-dd-mm. The normally accepted ISO format is yyy-mm-dd.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OceanDiscovery Old Hand Points: 355 More actions · Answer 10

Jeff, I really appreciate the time you've taken to look at this; as that was exactly the issue.

Very much appreciated (and red faced for missing a basic date conversion principle). We always pass a conversion on dates to the same standard format of yyyy-mm-dd; but in my attempts at trying to resolve this I reverted to our local format!

Cheers Jeff!

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 11

you can also use SET DATEFORMAT command to set the date format. Once your work is done, reset it whatever it was.

Suppose, your date format is dmy then below command gives 'Conversion failed' error:

DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';

SELECT @datevar;

But, this will work:

-- Set date format to day/month/year.

SET DATEFORMAT dmy;

GO

DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';

SELECT @datevar;

GO

Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

I'm glad you solved your issue. It couldn't be otherwise with Jeff helping! 😉

However, I still suggest using command objects and parameters when setting up SQL query from the application.

It doesn't only apply to stored procedures, commands can be used with plain queries as well.

Why are they better?

1) No need to convert dates and numbers (VBA/VBS turns dots to commas when converting a number to a string in Italian language)

2) Avoid sql injection

3) Reuse plan during repeated calls (even with simple parametrization)

Just my two cents.

-- Gianluca Sartori

OceanDiscovery Old Hand Points: 355 More actions · Answer 13

Thanks Gianluca

Now that I have the VBA call working correctly (and know why it wasn't working) I can shift my attention from problem solving to future proofing.

I had kept your post in mind while we've been trouble shooting this, and have every intention of researching the command object address as you've suggested simply for the risk from SQL injection.

You'll most likely hear more from me as I try to implement something new...

Thanks to all once again for their help, and a very Merry Christmas!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

Mac_85 (12/16/2010)
Jeff, I really appreciate the time you've taken to look at this; as that was exactly the issue.
Very much appreciated (and red faced for missing a basic date conversion principle). We always pass a conversion on dates to the same standard format of yyyy-mm-dd; but in my attempts at trying to resolve this I reverted to our local format!
Cheers Jeff!

You bet. And trust me... I've overlooked things that were a whole lot more obvious.

Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)