.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Specifying a comma separated string as a parameter value in BIDS

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

Hi, I have a problem that is driving me mad.  I am trying to create a report in BIDS that pulls data from oracle database and generates the report of call it sales by month.  What I'm attempting to do is to create a parameter in BIDS and force a comma separated value into it and then pass that value into SQL statement for an oracle dataset in other words.

BIDS:  Parameter @QTR Paramater label QTR1  Paramter value ('01','02','03')

           Parameter @Year Parameter label Year Parameter value 2010

Oracle side: select * from ....

where year=:Year and

month in :QTR

Whenever I try to run it like that the report generates but it's blank.  Replacing value in QTR parameter with a single non quoted value like 01 generates report perfectly fine.  Could anyone shed some light as to how properly specify comma delimited strings to be used in :in: statements. 

View Complete Post

More Related Resource Links

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


I am trying to get the UserRole from the logged in user and send it to the database table my code is:

SqlParameter FamilyFriendsOther = new SqlParameter("@Role", SqlDbType.UniqueIdentifier);
        string [] roles = System.Web.Security.Roles.GetRolesForUser();
        if (roles.Length > 0)
            FamilyFriendsOther.Value = roles[0];

adding parameter to query string


hi all,

how do i add a new parameter to an existing query string?


now i need to add a new parameter say, showsearch.

Using IN clause(string parameter) in Stored Procedure

Hi guys. I need to use IN clause in a selection query. From all the resources that I found from google, it's all about adding a function to split a integer parameter into a list. But I got a string parameter, for example 'A1,B1,C1' I tried to use the below where single quotes in @CustCode is double up. ___________________________________ Declare @SQL VarChar(1000)   SELECT @SQL = ' SELECT [something]' SELECT @SQL = @SQL + ' FROM [Table]' SELECT @SQL = @SQL + ' WHERE CustomerCode IN ('  + @CustCode + ')'   EXECUTE (@SQL) _____________ Executing the stored procedure in MSSQL works fine, but it didn't when executing from Visual Studio 2005. Please advice.

Failed To Convert Parameter Value From A String To A Int32

 This is my stored procedure: PROCEDURE[dbo].[uspEXPENDITURE_DETAILS] ( @ExpDetailsID int=0 output, @ExpDet_ExpID int, @ExpDet_Municipality_ID nvarchar(50), @ExpDetGeneralFund money, @ExpDetSpecialRevenue money, @ExpDetCapitalProjects money, @ExpDetTotal money, @Exp_Reporting_Year varchar(10), @ExpComments varchar(max), @Contact_ID int ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON; deletefrom [dbo].[TBL_EXPENDITURE_DETAILS] where ExpDet_ExpID=@ExpDet_ExpID and ExpDet_Municipality_ID=@ExpDet_Municipality_ID and Exp_Reporting_Year=@Exp_Reporting_Year If@ExpDetailsID >= 0 Begin INSERT     INTO [dbo].[TBL_EXPENDITURE_DETAILS] ([ExpDet_ExpID] ,[ExpDet_Municipality_ID] ,[ExpDetGeneralFund] ,[ExpDetSpecialRevenue] ,[ExpDetCapitalProjects] ,[ExpDetTotal] ,[Exp_Reporting_Year] ,[ExpComments] ,[ExpDetCreatedBy] ,[ExpDetCreateDate]) VALUES  (@ExpDet_ExpID , @ExpDet_Municipality_ID , @ExpDetGeneralFund, @ExpDetSpecialRevenue , @ExpDetCapitalProjects, @ExpDetTotal, @Exp_Reporting_Year, @ExpComments, @Contact_ID, GetDate () )    SET @ExpDetailsID = SCOPE_IDENTITY()  return @ExpDetailsID end   Public Sub CreateExpenseDetails() Dim item As GridViewRow Dim txtExpGenFund, txtExpD

failed to convert parameter value from a string to a int32

I am not sure where my app is failing but most probably this is where it is failing.. I think lblExpID.Text is the culprit. Here's the relvant part of my code: Dim lblExpID As Label For Each item In LSGrid.Rows lblExpID = item.FindControl("lblID") If txtExpGenFund.Text <> "" Then sqlArray(i) = ExpDetailsID & "," & (lblExpID.Text) & "," & MunID & "," & _ Val(txtExpGenFund.Text) & "," & Val(txtExpDetSpecialRevenue.Text) & "," & _ Val(txtExpDetCapitalProjects.Text) & "," & expTotal & "," & reportingYear & "," & "Test Comments" & "," & UserID End If Can u help me identify why i am getting this error looking at the sp below: PROCEDURE [dbo].[uspEXPENDITURE_DETAILS] ( @ExpDetailsID int=0 output, @ExpDet_ExpID int, @ExpDet_Municipality_ID nvarchar(50), @ExpDetGeneralFund money, @ExpDetSpecialRevenue money, @ExpDetCapitalProjects money, @ExpDetTotal money, @Exp_Reporting_Year varchar(10), @ExpComments varchar(max), @Contact_ID int ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; delete from [dbo].[TBL_EXPENDITURE_DETAILS] where ExpDet_ExpID=@ExpDet_ExpID and ExpDet_Municipality_ID=@ExpDet_Municipality_ID and Ex

Duplicate URLs in Source Parameter of Query String for All SharePoint Lists

After performing a database attach method of upgrading our existing web application from MOSS 2007 to SharePoint Server 2010, all of our lists that migrated now exhibit an interesting but undesirable behavior.  The DispForm.aspx and EditForm.aspx pages that get loaded are given the Source parameter of the query string but instead of the URL being a single entry like: Source=http://sharepointsite.domain.com/Pages/Default.aspx It winds up putting the URL twice like this: Source=http://sharepointsite.domain.com/Pages/Default.aspx,http://sharepointsite.domain.com/Pages/Default.aspx Naturally this makes processing of the form -- either by choosing OK or Cancel buttons -- result in an HTTP 404 error.  The question we're left with is how in the world did the Source parameter get set that way to begin with.   It wasn't that way in 2007. We had thought that perhaps there was something that didn't migrate over correctly for the existing lists, so I tried creating a new list and then added a new item to the list.  So far so good.  Then attempted to Edit Properties or Edit Item or even View Item, etc. -- essentially triggering the load of the EditForm.aspx and DispForm.aspx  -- and we received the 404 again.  At this point we're not sure what's going on or how to fix it.  We've performed the migration three times so far with t

Comma Separated list returned from XML query

I found an example of how to do this, but I must have something wrong as I can't get it to execute without syntax errors or errors. Here's an example XML:   <GrantList> <GRANT SAPGrantNumber="12345" PersonID="168658" SequenceNumber="9" ItemTypeID="1" > <PEOPLE Investigators="Smith, Many" /> <PEOPLE Investigators="Brown, Esmond" /> <PEOPLE Investigators="White, Christine" /> <PEOPLE Investigators="Saga, John" /> <PEOPLE Investigators="Mog, Maureen" /> <PEOPLE Investigators="LastName, FirstName" /> </GRANT> <GRANT SAPGrantNumber="12645" PersonID="168658" SequenceNumber="10" ItemTypeID="1" /> <GRANT SAPGrantNumber="16546" PersonID="168658" SequenceNumber="11" ItemTypeID="1" /> <GRANT SAPGrantNumber="684684351" PersonID="168658" SequenceNumber="12" ItemTypeID="1" > <PEOPLE Investigators="Smith, Many" /> <PEOPLE Investigators="Smith, John" /> <PEOPLE Investigators="Mog, Maureen" /> <PEOPLE Investigators="Xiansa, Ming" /> </GRANT> </GrantList> Here's my query where the xml above is in the

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

Comma in viewstate string

Hi, I'm running into following error: "The viewstate is invalid for this page and might be corrupted." "Invalid character in base64 string." Only with IE 8.0.  Firefox works fine. The persisted view state is "/wEPDwULLTE2OTU5NzE1MjYPZBYCAgMPZBYCAgEPZBYCAgMPZBYCZg9kFgQCBQ8WAh4FVmFsdWUFCGRpc2FibGVkZAIHDxYCHwAFCGRpc2FibGVkZGSt4I2szqsXGPwfecanpFW+cIZNhQ==,/wEPDwULLTE2OTU5NzE1MjYPZBYCAgMPZBYCAgEPZBYCAgMPZBYCZg9kFgQCBQ8WAh4FVmFsdWUFCGRpc2FibGVkZAIHDxYCHwAFCGRpc2FibGVkZGSt4I2szqsXGPwfecanpFW+cIZNhQ==" i.e. two identical parts separated by a comma. I'm using a form with both jQuery and UpdatePanel. I've inspected the __VIEWSTATE hidden field before the post __doPostBack and  $I.System.InvokePageMethod.  In both cases the viewstate does NOT contain the comma. Any ideas where the field gets doubled up with the comma inserted? Or how to fix.   Thanks,   D

i need the row index value in the form of "comma Separated value"

hi, i am new to the C# in one of my page i need to pass the gridview rowindex vaule where the check box is checked true [i.e in comma sepparated vaule] this is my html code <tr>  <td>  <asp:GridView ID="GridView1" runat="server" EmptyDataText="THERE IS NO DATA FOR THIS REQUEST">  <Columns>  <asp:BoundField DataField="Tablelevelid" HeaderText="att_ID" />  <asp:TemplateField>  <HeaderTemplate>  <asp:CheckBox ID="checkAll" runat="server" onclick="checkAll(this);" /></HeaderTemplate>  <ItemTemplate>  <asp:CheckBox ID="CheckBox1" runat="server" onclick="Check_Click(this)" /></ItemTemplate>  </asp:TemplateField>  <asp:BoundField DataField="NAME" HeaderText="Name" />  <asp:BoundField DataField="PROJECT_ID" HeaderText="Project" />  <asp:TemplateField HeaderText="Comments">  <ItemTemplate>  <asp:TextBox ID="txtComments" runat="server" Text="" MaxLength="250"></asp:TextBox></ItemTemplate>  </asp:TemplateField>  <asp:TemplateField>  <ItemTemplate>  <asp:Require

Inserting string into sql parameter. Is their a place holder ?

My stored procedure accepts a where clause that is passed in as a parameter. (In this case it is named "@filter") @filter as nvarchar(120) Is their a way to insert a parameter into this @filter string? Look at this where clause that is passed in: 'Where Index > 0 AND Status IN (''Closed'',''Review'')' What I need to do is to insert the view name in front of the "Status" string as follows: 'Where Index > 0 AND dbo.TrView.Status IN (''Closed'',''Review'')' So in other words the string "dbo.TrView." needs to be inserted. How can this be done?I know in C# you can use substitute indicators like {0} etc. But how to do this in SQLI don't know. Can someone help me out?

Report Parameter adds COMMA ( , )

Hi All, This is purely t-sql base question, previously we had simple as "LastName FirstName, LastName FirstName........" (comma is added by the SSRS Report Parameter when users selects multple values from the drop down menu), everything was working fine with the below query:- DECLARE @data NVARCHAR(MAX), @delimiter NVARCHAR(5) SELECT @data = 'Albert John, Blackmore Allen, Taylor Ken V., Heather Day F.' SET @delimiter = ',' DECLARE @textXML XML; SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML); SELECT LTRIM(T.split.value('.', 'nvarchar(max)')) AS [Items] INTO #TEMP FROM @textXML.nodes('/d') T (split) SELECT CASE WHEN [ThirdPart] IS NULL THEN [FirstPart] ELSE [SecondPart] END AS [FirstName], CASE WHEN [ThirdPart] IS NULL THEN [SecondPart] ELSE [ThirdPart] END AS [SecondName], CASE WHEN [ThirdPart] IS NULL THEN [ThirdPart] ELSE [FirstPart] END AS [MiddleName] FROM #TEMP CROSS APPLY (SELECT REPLACE( REPLACE([Items],'.',''),' ','.') AS [Part] )[t1] CROSS APPLY (SELECT PARSENAME([t1].[Part], 2) AS [SecondPart] ,PARSENAME([t1].[Part], 3) AS [ThirdPart], PARSENAME([t1].[Part],1) AS [FirstPart] )[t2] DROP TABLE #TEMP   Now, we have to implement string as "LastName, FirstName, LastName, FirstName......&

Can MVC attributes with a string parameter be fed a function returning a string?


Ok so take for instance this attribute:

[PropertiesMustMatch("NewPassword", "ConfirmPassword", ErrorMessage = "La nuova password e la password di conferma non corrispondono.")]

You can see that the third parameter is a string that I would want to localize, hence I would like to delete that message and replace it with a function call, say something along the lines of this:

[PropertiesMustMatch("NewPassword", "ConfirmPassword", ErrorMessage = GetLocalization("strErrPassMustMatch")]

Do you think it would be possible? If I got *any* chance to do it, then good, I will be setting up a system to do this :-P otherwise, I'll have to find some other way :-)

What can you tell me?

Thank you!


Replace the string after comma starts in MS SQL


Hi All

Could you anybody help me, How to replace the remaining String after the Comma Starts Using SQL Query.  Below is the data

"MediaRights, MediaRightsLink, MediaRightsCompanies, MediaRightsTypes"

EvtBids 1
MediaRights, MediaRightsLink, MediaRightsCompanies, MediaRightsTypes 1
MediaRightsLink 8
SponsorRights, SponsorRightsLink, SponsorRightsBrands, SponsorRightsTypes 3
SponsorRights, SponsorRightsLink, SponsorRightsCompany, SponsorRightsCompanyTypes 1

Assign Query String Values to Selectcommand parameter in




I have two pages EventList.aspx and EventDetail.apsx. When user selects any events [basically list item] on EventList.aspx page [all events are rollup in single sote collection] in am redirected to EventDetail.aspx page with QueryString ListID and ListItemId.

On EventDetail.aspx page i am using


"<Webs Scope='SiteCollection'></Webs><Lists><List ID='{ListID}'></Li

Dividing a comma delimited string


Hi guys,

I am fairly new to TSQL and need some urgent help on this.

I have a column in a table made up of a string that has data as follows:


I would like to break the long string into different words and then store them in separate columns, so I should have

Column 1: COSTS

Column 2: LEVEL

Column 3: GRADE

Column 4: ITEM

Can someone please help me out with this, taking into consideration the fact that I am a newbie to TSQL.


Thank you

How to send parameter in query string on ajax call in asp.net mvc


I want to send selected page value on querystring while navigating through paging. URL that is generating on paging are like this

Link/Index?page=2  or Link/Index?page=3

but on my URL it only shows Link/Index and perform Ajax call but if i disable my Javascript and then navigate through paging it gets Postback and have URL like


which is perfect. But i want this type of URL in Ajax call as well how can i do this? Issue is if we navigate through pages when Javascript is enable it shows Link/Index and when user goes to page no 2 then 3 then 4 and press back button it goes to press page instead of page 3 then page 2. For reference if writing the code which generate paging

<%= Ajax.Pager(new AjaxOptions { UpdateTargetId = "divGrid", LoadingElementId = "divLoading" }, ViewData.Model.PageSize, ViewData.Model.PageNumber,
    ViewData.Model.TotalItemCount, new { controller = "LinkManagement", action = "Index" })%>

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend