failed to convert parameter value from DateTime to a Int32

I have criteria in my dataset that throws the above error. Iâ'm just trying
to get RS to understand the parsing out of a DateTime Field the values of a
month or year. I know the value I am comparing to is a number. Iâ've tried
the following different ways:
= YEAR(@.Date_Selection) â' 1
= DatePart(yy,@.Date_Selection) - 1
Additionally, Iâ've tried casting and converting back and forth from and to
VarChar and Int to no avail.
Any thoughts?
--
Marty ClineIt would be easier to help you if we could see the field types of your table
and the whole query that's included in your SSRS dataset (including the
parameter).
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>I have criteria in my dataset that throws the above error. Iâ'm just trying
> to get RS to understand the parsing out of a DateTime Field the values of
> a
> month or year. I know the value I am comparing to is a number. Iâ've tried
> the following different ways:
> = YEAR(@.Date_Selection) â' 1
> = DatePart(yy,@.Date_Selection) - 1
> Additionally, Iâ've tried casting and converting back and forth from and to
> VarChar and Int to no avail.
> Any thoughts?
> --
> Marty Cline|||Sure, butt here's the real "kicker".
This work perfectly in SQL server studio. Reporting services just has
issues with the datetime parameter.
Here's the sql:
DECLARE @.DATE_Selection AS DATETIME
SET @.DATE_Selection = '10/31/2007'
SELECT
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD =td.Curr_hold INNER JOIN
Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
(ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
(1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection) -
1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <=20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
Marty Cline
"Alain Quesnel" wrote:
> It would be easier to help you if we could see the field types of your table
> and the whole query that's included in your SSRS dataset (including the
> parameter).
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >I have criteria in my dataset that throws the above error. Iâ'm just trying
> > to get RS to understand the parsing out of a DateTime Field the values of
> > a
> > month or year. I know the value I am comparing to is a number. Iâ've tried
> > the following different ways:
> >
> > = YEAR(@.Date_Selection) â' 1
> > = DatePart(yy,@.Date_Selection) - 1
> >
> > Additionally, Iâ've tried casting and converting back and forth from and to
> > VarChar and Int to no avail.
> >
> > Any thoughts?
> > --
> >
> > Marty Cline
>|||nevermind. It just started working. I have no idea why.
--
Marty Cline
"Alain Quesnel" wrote:
> It would be easier to help you if we could see the field types of your table
> and the whole query that's included in your SSRS dataset (including the
> parameter).
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >I have criteria in my dataset that throws the above error. Iâ'm just trying
> > to get RS to understand the parsing out of a DateTime Field the values of
> > a
> > month or year. I know the value I am comparing to is a number. Iâ've tried
> > the following different ways:
> >
> > = YEAR(@.Date_Selection) â' 1
> > = DatePart(yy,@.Date_Selection) - 1
> >
> > Additionally, Iâ've tried casting and converting back and forth from and to
> > VarChar and Int to no avail.
> >
> > Any thoughts?
> > --
> >
> > Marty Cline
>|||Did you set the proper type for your parameter in SSRS? It defaults to
string the first time you create it, and if you modify it afterwards, it
tends to revert back to string. From your SQL code, it should be datetime.
If that's not the issue, you could try this:
LEFT(convert(varchar, @.DATE_Selection, 2), 2)
instead of this:
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
And so on for the rest of your fields. You never know...
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
> Sure, butt here's the real "kicker".
> This work perfectly in SQL server studio. Reporting services just has
> issues with the datetime parameter.
> Here's the sql:
> DECLARE @.DATE_Selection AS DATETIME
> SET @.DATE_Selection = '10/31/2007'
> SELECT
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
> CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
> ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
> ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
> td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
> ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
> ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
> sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
> FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
> Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD => td.Curr_hold INNER JOIN
> Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
> WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
> (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20)
> AND
> (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
> (1 = 1) AND (ir.YEAR = DATEPART(yy,
> @.Date_Selection) -
> 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL
> <=> 20) AND
> (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
> ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
>
> --
> Marty Cline
>
> "Alain Quesnel" wrote:
>> It would be easier to help you if we could see the field types of your
>> table
>> and the whole query that's included in your SSRS dataset (including the
>> parameter).
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>> >I have criteria in my dataset that throws the above error. Iâ'm just
>> >trying
>> > to get RS to understand the parsing out of a DateTime Field the values
>> > of
>> > a
>> > month or year. I know the value I am comparing to is a number. Iâ've
>> > tried
>> > the following different ways:
>> >
>> > = YEAR(@.Date_Selection) â' 1
>> > = DatePart(yy,@.Date_Selection) - 1
>> >
>> > Additionally, Iâ've tried casting and converting back and forth from and
>> > to
>> > VarChar and Int to no avail.
>> >
>> > Any thoughts?
>> > --
>> >
>> > Marty Cline
>>|||Thanks for your response. To answer your question, yes the parameter type
was set to DateTime.
I'm not sure why it was not working to begin with but it seems to be working
now. Probably something I did not know I did, but I'm not arguing. Thanks
for your help again!
--
Marty Cline
"Alain Quesnel" wrote:
> Did you set the proper type for your parameter in SSRS? It defaults to
> string the first time you create it, and if you modify it afterwards, it
> tends to revert back to string. From your SQL code, it should be datetime.
> If that's not the issue, you could try this:
> LEFT(convert(varchar, @.DATE_Selection, 2), 2)
> instead of this:
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
> And so on for the rest of your fields. You never know...
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
> > Sure, butt here's the real "kicker".
> >
> > This work perfectly in SQL server studio. Reporting services just has
> > issues with the datetime parameter.
> >
> > Here's the sql:
> >
> > DECLARE @.DATE_Selection AS DATETIME
> > SET @.DATE_Selection = '10/31/2007'
> >
> > SELECT
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
> >
> > CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
> >
> > ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
> > ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
> > td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
> > ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
> > ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
> > sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
> > FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
> > Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD => > td.Curr_hold INNER JOIN
> > Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
> > WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
> > (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20)
> > AND
> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
> > (1 = 1) AND (ir.YEAR = DATEPART(yy,
> > @.Date_Selection) -
> > 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL
> > <=> > 20) AND
> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
> > ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
> >
> >
> > --
> >
> > Marty Cline
> >
> >
> > "Alain Quesnel" wrote:
> >
> >> It would be easier to help you if we could see the field types of your
> >> table
> >> and the whole query that's included in your SSRS dataset (including the
> >> parameter).
> >>
> >>
> >>
> >> Alain Quesnel
> >> alainsansspam@.logiquel.com
> >>
> >> www.logiquel.com
> >>
> >>
> >> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> >> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >> >I have criteria in my dataset that throws the above error. Iâ'm just
> >> >trying
> >> > to get RS to understand the parsing out of a DateTime Field the values
> >> > of
> >> > a
> >> > month or year. I know the value I am comparing to is a number. Iâ've
> >> > tried
> >> > the following different ways:
> >> >
> >> > = YEAR(@.Date_Selection) â' 1
> >> > = DatePart(yy,@.Date_Selection) - 1
> >> >
> >> > Additionally, Iâ've tried casting and converting back and forth from and
> >> > to
> >> > VarChar and Int to no avail.
> >> >
> >> > Any thoughts?
> >> > --
> >> >
> >> > Marty Cline
> >>
> >>
>|||You're welcome.
Debugging a report in SSRS based on the error message on the Preview page
isn't exactly intuitive. Or at least not in the beginning when you're not
familiar with the environment.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:491E40DE-D931-4162-B5F5-C707533EAD34@.microsoft.com...
> Thanks for your response. To answer your question, yes the parameter type
> was set to DateTime.
> I'm not sure why it was not working to begin with but it seems to be
> working
> now. Probably something I did not know I did, but I'm not arguing.
> Thanks
> for your help again!
> --
> Marty Cline
>
> "Alain Quesnel" wrote:
>> Did you set the proper type for your parameter in SSRS? It defaults to
>> string the first time you create it, and if you modify it afterwards, it
>> tends to revert back to string. From your SQL code, it should be
>> datetime.
>> If that's not the issue, you could try this:
>> LEFT(convert(varchar, @.DATE_Selection, 2), 2)
>> instead of this:
>> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
>> And so on for the rest of your fields. You never know...
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
>> > Sure, butt here's the real "kicker".
>> >
>> > This work perfectly in SQL server studio. Reporting services just has
>> > issues with the datetime parameter.
>> >
>> > Here's the sql:
>> >
>> > DECLARE @.DATE_Selection AS DATETIME
>> > SET @.DATE_Selection = '10/31/2007'
>> >
>> > SELECT
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
>> >
>> > CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS
>> > ReportOrder,
>> >
>> > ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month,
>> > DATENAME(d,
>> > ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
>> > td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL,
>> > ir.IMP_10_CNT,
>> > ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
>> > ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
>> > sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
>> > FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
>> > Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD
>> > =>> > td.Curr_hold INNER JOIN
>> > Investments.CAM_SEC_DIM AS sd ON td.Cusip =>> > sd.CUSIP
>> > WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
>> > (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <=>> > 20)
>> > AND
>> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
>> > OR
>> > (1 = 1) AND (ir.YEAR = DATEPART(yy,
>> > @.Date_Selection) -
>> > 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND
>> > (ir.IMPAIRMENT_LVL
>> > <=>> > 20) AND
>> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
>> > ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
>> >
>> >
>> > --
>> >
>> > Marty Cline
>> >
>> >
>> > "Alain Quesnel" wrote:
>> >
>> >> It would be easier to help you if we could see the field types of your
>> >> table
>> >> and the whole query that's included in your SSRS dataset (including
>> >> the
>> >> parameter).
>> >>
>> >>
>> >>
>> >> Alain Quesnel
>> >> alainsansspam@.logiquel.com
>> >>
>> >> www.logiquel.com
>> >>
>> >>
>> >> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> >> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>> >> >I have criteria in my dataset that throws the above error. Iâ'm just
>> >> >trying
>> >> > to get RS to understand the parsing out of a DateTime Field the
>> >> > values
>> >> > of
>> >> > a
>> >> > month or year. I know the value I am comparing to is a number. Iâ've
>> >> > tried
>> >> > the following different ways:
>> >> >
>> >> > = YEAR(@.Date_Selection) â' 1
>> >> > = DatePart(yy,@.Date_Selection) - 1
>> >> >
>> >> > Additionally, Iâ've tried casting and converting back and forth from
>> >> > and
>> >> > to
>> >> > VarChar and Int to no avail.
>> >> >
>> >> > Any thoughts?
>> >> > --
>> >> >
>> >> > Marty Cline
>> >>
>> >>
>>

Failed to convert parameter value from a String to a Guid?

Hi all,

I have setup my database that have 3 columns:

1. Primary Key

2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.

3. Another Foreign key with just an int.

I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:

SELECT aspnet_Users.UserId, t_music.MUSIC_TITLE
FROM t_user_viewed
JOIN aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId
JOIN t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FK
WHERE aspnet_Users.UserId = @.UserId

Any help would be greatly appreciated,

Kenny.

You need to post definition for @.UserId parameter.

|||

Can you please elaborates? I'm new to asp.net as well as ms-sql.

Thank you,

Kenny.

|||

Sounds like you need to cast the string value to GUID on the @.UserId when you call the select function and pass the @.UserId value.

ctype(StringValueGUID, GUID)

Burl

|||

Where do I cast the string value? In the DAL (dataset) or where I have my ObjectDataSource doing the select? Sorry for my lack of knowledge on asp.net and mssql.

Thanks,

Kenny.

|||

How are you passing the GUID to the ObjectDataSource for the select? Wherever that value comes from, its in a string type and needs to be cast as a GUID type. Can you post some code?

Burl

|||

I've created a Dataset and setup my table, then I've created a BLL that have this code to get the fields:

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}

In my ObjectDataSource, I chose the above method (GetSongsByUserPK) and have the parameter source as a session. Here is the code for the ObjectDataSource:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>

The SELECT query is the same as my first post... is there anything else I need to do?

Thank you very, very much.

Kenny.

|||

Well,

I've made some minor adjustment to my code and it's working now, however, the SELECT statement that I wrote in the DataSet didn't return the proper table. Here are the changes:

1. In my DataSet, I have setup the table called t_user_viewed that will store the primary key of t_music database as foreign key, and the UserId from the aspet_users as another foreign key. Here is the select statement:

SELECT *
FROM t_music a
JOIN t_user_viewed b on a.MUSIC_PK = b.MUSIC_PK_FK
JOIN aspnet_Users c ON b.UserID = c.UserId
WHERE b.UserID = @.UserId

So when I run this code, and enter the UserId, it should return the information from table "t_music" (with all information such as song title, artist, etc...) instead.

2. This is the code for the SongsBLL.cs class, to get the above select statement:

private t_user_viewedTableAdapter _t_user_viewed = null;

protected t_user_viewedTableAdapter Adapter2
{
get
{
if (_t_user_viewed == null)
_t_user_viewed = new t_user_viewedTableAdapter();

return _t_user_viewed;
}
}

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}

3. Here is the ObjectDataSource that was generated by VS:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL" OldValuesParameterFormatString="original_{0}">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>

4. Here is the GridView that was generated automatically when I select the DataSourceID to the above ObjectDataSource:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="VIEWED_PK"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="VIEWED_PK" HeaderText="VIEWED_PK" InsertVisible="False"
ReadOnly="True" SortExpression="VIEWED_PK" />
<asp:BoundField DataField="MUSIC_PK_FK" HeaderText="MUSIC_PK_FK" SortExpression="MUSIC_PK_FK" />
<asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
</Columns>
</asp:GridView>

Apparently, the GridView & the ObjectDataSource only pick up the t_music_viewed table columns, instead of the "t_music" columns that I specified in my Select statement.

Any idea?

Thank you very much,

Kenny.

|||

I've solved it by building my SELECT query in the "t_music" table instead.

THANK YOU!

Kenny.

|||

I've solved it by building my SELECT query in the "t_music" table instead.

THANK YOU!

Kenny.

sql

Failed searching records by using SQLCommand Parameter "LIKE %@keywords%"

I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below.

However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help?

Thanks,

Andy.

privatebool searchProducts(string keywords)

{

......

command.CommandText ="SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@.keywords%' ";

command.Parameters.Add("@.keywords",SqlDbType.VarChar, 100);

command.Parameters["@.keywords"].Value = keywords;

..........

}

Your keyword parameter will be treated as literal text as it is within quotes and hence it will not be treated as parameter.

The following shoud work:

command.CommandText ="SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE @.keywords";

command.Parameters.Add("@.keywords",SqlDbType.VarChar, 100);

command.Parameters["@.keywords"].Value = "%" + keywords + "%";

|||

Many thanks. It works now.

Andy.

fail to change the value of parameter with operator 'range'

Hi all,

I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).

...

From date To Date

...

From age To Age

...

From Salary To Salary

@.salary is the last one.

when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'DimTimesheetTimesheetNumber'. (rsErrorExecutingCommand)

Range operator ( : ) operands have different levels; they must be the same.

BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.

Any idea and suggestion will be appreciated!

Jone

Anybody has any idea?

Thanks,

Extracting the parameter values

I have a report application where I am using Microsoft AxBrowser and url acess to view the reports. I have a report with multivalued parameter. I need to extract the user selected parameter value for that rpeort for some other purpose. How to extract the parameter value?

Please help

Do you have access to the cookies collection of the AxBrowser control? If so, then you can try to get the RS session ID and call the SOAP API GetExecutionInfo() on that session. It will return the effective parameters for the current session. Assuming you haven't changed them then it should be the parameters that were used to render the report.

Question: Why aren't you using the viewer control?

|||Thanks for the reply John.
I am passing the default parameters to the report and getting it.
I needed to extract the parameter values when the user inputs (selects) some other value.
I could able to retrieve the parameter values by capturing the url after navigation and used some string functions. I used the Navigate2 event of AXBroweser. This works for me becuase I had to extract values for only two reports and I know the names of the parameters
we have SQL2000 and Reporting Services2000. I guess we can use report viewr control only with Reporting Services 2005. Correct me if I am wrong.
Thanks,
Siri..
|||

Yes the viewer control only works with the 2005 endpoints.

Your solution will only work in the case where parameters are passed on the URL string, which in general is not the case. I would recommend trying to get the sessionId from the AxBrowser and calling GetExecutionInfo() as it should work regardless of how the parameters are set.

Extracting Parameter values from a Custom Data Processing Extension

I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.

My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.

The IDBCommand interface contains a method CreateParameter(...).

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

See also: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_2a4a.asp

-- Robert

|||Where is the proper place to initialize the Parameter collection? I keep getting a "Collection was modified" when I try to tell my Command object was parameters it has.|||

Robert Bruckner MSFT wrote:

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

Sorry for pullig out this old thread, but it's exactly the problem I have right now.
At the moment my CreateParameter()-Function looks like this (like in the FSI example)

Code Snippet

Return CType(New DataParameter, IDataParameter)

nothing more. As an extra, I am searching for parameters everytime the command-text is set. I am pullig out the parameters from the text with a regExp and add them to the collection.
Is this the right way?

In technet it says: "The CreateParameter method is called by the report server to set the name and value for each query parameter in the command text. The parameter is then added to the Parameters collection prior to the ExecuteReader method being called."

Right now, I am doing all of that in my cde manually:
set command-text? then get parameters from string -> add all of them to the collection.

without this extra-step after setting the command-text, the collection gets called later and would be empty. There is no call from report-server to CreateParameter. (I put traces in every function/sub)|||Ok, I worked it out:

"Public ReadOnly Property Parameters() As IDataParameterCollection Implements IDbCommand.Parameters"
This one is used by the server to access and build up the parameterCollection.

"Public Function GetParameters() As IDataParameterCollection Implements IDbCommandAnalysis.GetParameters"
This one has to parse out all parameters from the query, so the dataset preview can list them.
http://technet.microsoft.com/en-us/library/aa256673(SQL.80).aspx

Extracting Parameter values from a Custom Data Processing Extension

I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.

My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.

The IDBCommand interface contains a method CreateParameter(...).

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

See also: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_2a4a.asp

-- Robert

|||Where is the proper place to initialize the Parameter collection? I keep getting a "Collection was modified" when I try to tell my Command object was parameters it has.
|||

Robert Bruckner MSFT wrote:

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

Sorry for pullig out this old thread, but it's exactly the problem I have right now.
At the moment my CreateParameter()-Function looks like this (like in the FSI example)

Code Snippet

Return CType(New DataParameter, IDataParameter)

nothing more. As an extra, I am searching for parameters everytime the command-text is set. I am pullig out the parameters from the text with a regExp and add them to the collection.
Is this the right way?

In technet it says: "The CreateParameter method is called by the report server to set the name and value for each query parameter in the command text. The parameter is then added to the Parameters collection prior to the ExecuteReader method being called."

Right now, I am doing all of that in my cde manually:
set command-text? then get parameters from string -> add all of them to the collection.

without this extra-step after setting the command-text, the collection gets called later and would be empty. There is no call from report-server to CreateParameter. (I put traces in every function/sub)
|||Ok, I worked it out:

"Public ReadOnly Property Parameters() As IDataParameterCollection Implements IDbCommand.Parameters"
This one is used by the server to access and build up the parameterCollection.

"Public Function GetParameters() As IDataParameterCollection Implements IDbCommandAnalysis.GetParameters"
This one has to parse out all parameters from the query, so the dataset preview can list them.
http://technet.microsoft.com/en-us/library/aa256673(SQL.80).aspx

Extracting Attribute Values from XML Using XQuery

I am serializing a .NET object as XML and passing it as a parameter to a stored procedure. I am new to XQuery and have tried other places to find the answer to this question but have so far been unsuccessful.

How can I get the value of an attribute out of an XML document as a standard SQL Server data type? For instance, if my XML document were:

<RootNode>
<Object ID = "1234">
<AnElement>Some Other Value</AnElement>
</Object>
</RootNode>

What would be the correct XQuery to get the value of the ID attribute in the Object element?

Thanks in advance.

Kevin J Lambert

create table #xml_temp (

xml_col xml

)

insert into #xml_temp values('<RootNode>

<Object ID = "1234">

<AnElement>Some Other Value</AnElement>

</Object>

</RootNode>')

--With query() function

select xml_col.query('data(/RootNode/Object/@.ID)') from #xml_temp

--With value() function

select xml_col.value('data(/RootNode/Object/@.ID)[1]','int') from #xml_temp