Chuck
river guide
Posts:157
 |
| 11-19-2010 07:01 AM |
|
In my Initial SQL Query Rendering Bind, I have:
usp_Worksheet '$(DSParam1)','$(DSParam2)','$(DSParam3)','$(DSParam4)'
The values that I am passing into my store procedure are:
@DSParam1 as int,
@DSParam2 as int,
@DSParam3 as varchar(10),
@DSParam4 as varchar(10)
Whereas each represent
DSParam1=315
DSParam2=2010
DSParam3=2010-9-1
DSParam4=2010-9-30
When I run these variable in my stored procedure to test as:
Declare @DSParam1 int
Declare @DSParam2 int
Declare @DSParam3 varchar(10)
Declare @DSParam4 varchar(10)
set @DSParam1 = 315
set @DSParam2 = 2010
set @DSParam3 = '2010-8-1'
set @DSParam4 = '2010-8-31'
Everything is outputted correctly in my SQL Server Studio test. But when I attempt to run this on my DF. I get nothing. I have narrowed it down to the variables DSParam3 and DSParam4, for some reason passing the date values in my store procedure from DF has it failing.
Can anyone give me any insight on what I may be doing wrong using this method?
-Chuck
|
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 11-19-2010 08:02 AM |
|
Hello Chuck,
You may want to attach this to your URL like so:
www.yoursite.com?debug=true
or if querystring parameters exist in the URL already then use this syntax:
www.yoursite.com?DSParam3 = 2010&debug=true
This will help you see why the Initial SQL databinding failed.
Also another thing I would like to mention is that if DSParam3 and DSParam4 are datefields you should definitely change the data type to type datetime.
Then if you need to format the date you can format it like so:
Convert(char(10), @DSParam3, 101) As YourAliasName
Convert(char(10), @DSParam3, 102) As YourAliasName
Convert(char(10), @DSParam3, 103) As YourAliasName
Convert(char(10), @DSParam3, 104) As YourAliasName
NOTE: The only thing that's changing is the 101,102, 103, and 104. These are the date formats.
For instance you can format dates in multiple formats:
05/10/2010
05-10-2010
2010-10-05
2010/10/05
10-05-2010
The format number will format the date in the way you'd like.
If you have any questions please let me know.
Thanks,
Ryan |
|
|
|
|
Chuck
river guide
Posts:157
|
| 11-19-2010 10:35 AM |
|
Hi Ryan,
Thanks for the debug tip.
When I run it, it looks like this:
WorksheetStep2NonLEC.aspx?DSParam1=315&DSParam2=2010&DSParam3=2010-9-1&DSParam4=2010-9-30&debug=true
Attempting SQL Data Bind:usp_UserWorksheet '315','2010','2010-9-1','$(DSParam4)'
Failed SQL Data Bind:usp_UserWorksheet '315','2010','2010-11-1','$(DSParam4)'
So a followup question. Does this mean its not passing the $DSParam4 into the SQL Data Bind? If so, is there a limit when passing a parameter into a stored procedure e.g. $DSParam1, $DSParam2 and $DSParam3 only...$DSParam4 is not allowed?
Or does this mean in my stored procedure where I am using the @DSParam4, it does not like the format?
Thoughts?
-Chuck |
|
|
|
|
Chuck
river guide
Posts:157
|
| 11-19-2010 12:13 PM |
|
Hello Ryan,
I was able to work around my issue with the $DSParam4 parameter. For some reason, it does not like me passing that value even if it was set exactly as my $DSParam3 setup. So I removed it and created another parameter inside my stored procedure to work off my $DSParam3 being passed and all is well.
If you can verify for me if there is a limitation of only 3 parameters to pass into a SQL Bind, that would be great.
BTW I am using DF 3.3.40.0 which I know there has been more recent versions out there but for now I need to work with this one.
Thanks again.
Chuck |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 11-19-2010 12:34 PM |
|
Hello Chuck,
Now that I think of it, there are only DSParam1, DSParam2, and DSParam3 in Dynamic Forms. DSParam4 doesn't exist. I completely missed this concept when you said you were using these 4 parameters. So possibly DSParam4 wasn't recognized as a querystring token.
But technically you can use as many parameters that you'd like in SQL binding. I just find it odd that DSParam4 didn't work. What did you name the 4th parameter if you don't mind me asking?
If you have any questions please let me know.
Thanks,
Ryan |
|
|
|
|
Chuck
river guide
Posts:157
|
| 11-19-2010 12:50 PM |
|
Hi Ryan,
Basically what I was trying to do is pass my 4th parameter as an end of day of the month from a hiddenfield e.g. $(DSParam4) would have been '2010-9-30'
But since I was passing a $(DSparam3) which represented the first day of the month e.g. $(DSParam3) would have been '2010-9-1'
So what I did in my SQL store procedure, passed the 3 parameters in and within the stored procedure created another parameter to get my end of month variable.
e.g.
ALTER PROCEDURE [dbo].[usp_UserWorksheet_Step3]
-- Add the parameters for the stored procedure here
@DSParam1 int,
@DSParam2 int,
@DSParam3 varchar(10)
AS
BEGIN
DECLARE @pass_DSParam3 datetime
DECLARE @pass_DSParam4 int
DECLARE @pass_DSParam5 datetime
-- Format to yyyy-mm-dd
SET @pass_DSParam3 = CONVERT(VARCHAR(10), @DSParam3, 120)
-- Find Days in Month for @pass_DSParam3
SET @pass_DSParam4 = (SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@pass_DSParam3)),DATEADD(m,1,@pass_DSParam3))))
-- create format for end of month date based on @pass_DSParam3 and @pass_DSParam4
SET @pass_DSParam5 = (SELECT DATEADD(day, @pass_DSParam4, @pass_DSParam3))
<----- SQL Select code omitted ---->
As you can see I accomplished my goal by creating 3 new parameters within it to be used in the SQL select code further down that I would pass into my DF fields.
Hope this makes sense.
Thanks again.
-Chuck
|
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 11-19-2010 02:26 PM |
|
Hello Chuck,
This makes perfect sense now that I see the SQL script. Seeing the script is easier than trying to explain how something works.
This will be a great resource if someone runs into a similar issue. This forum thread is going into my library of great contributions.
Have a great weekend Chuck,
-Ryan |
|
|
|
|