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
>> >>
>> >>
>>
Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts
Friday, March 23, 2012
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.
Subscribe to:
Posts (Atom)