Output Multivalue Parameters

Jun 16, 2006

I'm trying to output all the values selected through a multivalue parameter, but I am having difficulties. In the text box, the parameter contains only four member functions (value, label, count, ismultivalue). However, to output a value or label for the parameter you need to specify an index in the form of:

Parameters!Names.Value(0)

Ideally, I would like to be able to obtain all these values with a function call, but it doesn't look like there is one. Any ideas of how to get all the values?

Thank you!!

View 4 Replies


ADVERTISEMENT

MultiValue Parameters

Sep 21, 2007

Hi. I am fairly new to reporting. I have a report that currently displays queried data in both a table and a matrix. I'd like for the data that is displayed to be based off of my report parameters. I have three, all of which are multivalue. Currently, I am able to display the report, based on the only the first value of TWO of the three parameters. I tried using the dataset parameters, but was not successful with linking them to my report parameters. Please help! In addition, is there a way to set multivalue parameters to only display distinct values? Thanks, in advance, for any help.

View 3 Replies View Related

SP2 And Multivalue Parameters

Jan 17, 2008

Hi,

I need to set a label in my report when all the values in a certain multivalue parameter are selected. In other words, i would need to check whether this parameter has the value "Select All" selected or not.
How do i accomplish this?

Thanks in advance,
Claudio

View 3 Replies View Related

Multivalue Parameters

Apr 18, 2007

how do I create a multivalue parameter that has a default value of 'All' Or how to I I get the "Select All" option as the default parameter?

View 3 Replies View Related

Multivalue Parameters - Is This Possible ??

Dec 6, 2006



i am trying to build a report that uses multivalue parameters - the report is a simple listing report that lists out a list of cases as a table - the data is obtained from a view which just returns all details for all cases

there is no user input for the selection details - am going to be calling the report and passing over the parameter values directly to it.

there are several parameters for the report - they all default to "All"

for each of these parameters, i need to be able to either use the default value of "ALL" or to enter a list (comma separated ?)

q1) could i just enter the list as a string comma separated list (rather than as multivalued parameters) and then use this list to filter the data using the FILTERS tab on the dataset

q2) if q1 is not possible, if i use multivalue parameters, how do i get my list of parameters into the report as the application calling the report just has a list of values

thx

mark

View 11 Replies View Related

Passing Multivalue Parameters

May 22, 2007

Hello Friends,

I have a report (Say Report 1)which is asking for a multivalue parameter Period. The user selects three period from the dropdown list available for the Parameter. Lets say that the user select 0407,0507,0607. Now is this report 1 there is a particular field which is linked to another sub report. So if the top level Report 1 supplies the revenue for 3 regions say for the periods selected then clicking on any of the regions would open a second detailed report, say Report 2 for the three periods as selected.

Till this everything is fine.

Now the problem is that in this detailed Report 2 there would be a particular text box as "Click here to naviagate back to parent report". So that when the user clicks on this cell he is taken back to the parent report, Report 1 which was originally generated for the 3 periods.

Can anyone please advise as to how the Multivalue Parameter which was passed from Report 1 to Report 2 can again be looped back to Report 1 from Report 2.



Thanx Friends,

Raktim

View 2 Replies View Related

Multivalue Parameters And Wildcards

Dec 14, 2007



Greetings all
trying to get a multivalue parameter to accept either typed in data ex: 111111111,111111112 or if I want to return all id numbers type in %. Problem is when I test it by typing in 111111111,111111112 it throws an error saying " Incorrect syntax near ','.



I can enter 111111111 or % and get results, the error comes when I try to type in two or more id numbers. the parameter in the dataset looks like

where a.id_number LIKE (@id_number+ '%')


any suggesstions? Im sure there are threads out there but Im pressed to meet a deadline and wanted to see if there were any quick solutions

thanks
km

View 5 Replies View Related

Multivalue Parameters For Filter Not Working

Jan 12, 2008

Hi,

I've tried using the same method mentioned in the dozens of posts dedicated to this topic but it's not working.

My SQL statement is:

SELECT [University Origins].Origin, COUNT([University Origins].Origin) AS TotalCount, Hospitals.Name
FROM Posts INNER JOIN
Hospitals ON Posts.HospitalID = Hospitals.[Hospital ID] CROSS JOIN
[University Origins] INNER JOIN
Incumbents ON [University Origins].[Uni Origin ID] = Incumbents.[University Origin]
WHERE (NOT ([University Origins].Origin = 'No answer')) AND (Hospitals.Name IN (@HospitalFilter))
GROUP BY [University Origins].Origin, Hospitals.Name


The HospitalFilter parameter is set to Multivalue and it's populated with it's own SQL statement giving the Distinct Hospitals.

I have setup a filter on my table with the following expressions:

Expression: =Trim(Fields!Name.Value)
Operator: = IN
Value: =Trim(Parameters!HospitalFilter.Value.ToString )

I have used Trim to remove leading and trailing spaces.

Running the report always returns zero results and I'm puzzled by this. Would appreciate any advice and suggestions

Thanks,
John

View 8 Replies View Related

Restricting The Selection Of Multivalue Parameters...

Apr 25, 2007

hi all,



Is there any way to restrict the user from selecting the parameters. For example: I have a list of 100 values in a multi-value parameter. I want to to restrict the user to select at the most N number of values.

If possible, process only the N number of values will also do.



Thnx in advance...

View 3 Replies View Related

Showing MultiValue Parameters Under Report Title

Nov 16, 2007

Hi all,

I want to show the above. Can someone elighten me how I can loop thru to concatanate the values?

Regards,
Farouk Yew

View 1 Replies View Related

Passing Multivalue Parameters Through A Report Link

Jun 16, 2006

I've created a line chart in my report which lists number of transactions by month. I've also created a report to list transactions by day. My goal is for the user to select a month and link the report to the graph with the selected month divided up into days (in other words, the user wants to see the number of transactions for each day in the selected month).

Anyway, everything is working perfectly except for one thing. In the list of parameters that I am passing to the report for each day, I want to pass a multivalue parameter which contains all the transaction IDs the user selected in creating the report (these are supplied by a multivalue parameter). However, in the parameter dialog box where it asks for a value to send to the awaiting parameter, I do not know how to supply more than one value. If I need to pass one value, it will work fine. However, I would like to do something like:

JOIN(Parameters!TransactionID.value, ",")

for the value of TransactionID, but when I generate the report it is not accepting the values. I'm pretty sure its just a format issue, and I just need to know how exactly I should pass these values.

I already understand that when passing multivalue parameters in a URL you need something like:

TransactionID=1&TransactionID=2

...in order to select multiple values. However, this is a slightly different situation. I'm really running out of ideas, so any help would be much appreciated.

Thank you guys so much

View 6 Replies View Related

Multivalue Optional Parameters : Report Builder

Mar 14, 2007

Hi All,

Is there any way to make Multivalue parameter ( list parameter) in Report builder as optional ?

I was able to achieve optional functionalitiy for parameter is single value but not able to with respect to multivalue parameters

An early response is highly appreciated



Thanks

View 4 Replies View Related

Problem Creating Multivalue Parameters With An OLAP Cube

Jan 21, 2008

I am using a 2005 SSAS cube as a datasource for 2005 SSRS report with multivalue parameters. I use the design mode query builder to generate my MDX. I then add my parameter to my parameter field in the filters section. The name and value of the parameter in the dataset editor is as follows:

Name: FacilityFacilityNumber
Parameter: Parameters!FacilityFacilityNumber.Value

I then proceed to layout mode and then open up report parameters. I then check the multivalue box and select non-queried available values. I choose non-queried radio button because I want my asp page to be passing in the parameters, not a dropdown list.

The problem is that when I try and preview the report and I type in just one parameter I get the following:
"Query (1, 437) The restrictions imposed by the CONTRAINED flag in the STROSET function were violoated"

After looking through a couple of forums I found that adding the following Parameter value in the data tab and dataset editor would help me out a bit:
"[Facility].[Facility Number].&["+Parameters!FacilityFacilityNumber.Value+"]"

Ok so now (in layout mode) I type in one parameter and I get a report back, BUT when I try to type in a second or more parameter I get the same error as before:
"Query (1, 437) The restrictions imposed by the CONTRAINED flag in the STROSET function were violoated"


Here is the MDX query that I am using for this report:

SELECT NON EMPTY { [Measures].[Claim Count], [Measures].[Item Reasons Count], [Measures].[Charged Amount] } ON COLUMNS, NON EMPTY { ([Facility].[Facility Number].[Facility Number].ALLMEMBERS * [Claim Status Codes].[CLaim Status Code].[CLaim Status Code].ALLMEMBERS * [Claim Status Codes].[Claim Status Description].[Claim Status Description].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@FacilityFacilityNumber, CONSTRAINED) ) ON COLUMNS FROM [Claims Remittance]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


Please, if anybody know's what I am doing wrong I would greatly appreciate it.......I have to have my report accept non-queried multivalued parameters. Thanks

View 1 Replies View Related

Cascading Multivalue Parameters Fail To Populate In Subscription View

Jan 18, 2007

SSRS - SP2

We have many reports with cascading multivalue parameters. The reports and the parameters work as expected within the BI development and when deployed to the RS server. The issue we are having is that when we go to the Report Manager webpage for one of these reports and create a New Subscription the report parameters in the subscription field fail to populate. The first one or two parameters may properly populate but the 3rd, 4th, and 5th fail to populate. As a result we can not select the parameters to submit the subscription.

We have two example reports where one report uses only sql (text) and the other report uses only stored procedures. Both reports have 2 or more cascading parameters.

Any known issues with cascading multivalue parameters in the Report Manager subscription view?

View 1 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput 
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

Mar 12, 2008

Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlDbType

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")

Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)

testCMD.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)

RetValue.Direction = ParameterDirection.ReturnValue

Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)

auIDIN.Direction = ParameterDirection.Input

Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)

NumTitles.Direction = ParameterDirection.Output

auIDIN.Value = "213-46-8915"

PubsConn.Open()

Dim myReader As SqlDataReader = testCMD.ExecuteReader()

Console.WriteLine("Book Titles for this Author:")

Do While myReader.Read

Console.WriteLine("{0}", myReader.GetString(2))

Loop

myReader.Close()

Console.WriteLine("Return Value: " & (RetValue.Value))

Console.WriteLine("Number of Records: " & (NumTitles.Value))

End Sub

End Class

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.

Thanks in advance,
Scott Chang

View 29 Replies View Related

Output Parameters In SQL Sp's

Feb 26, 2008

Hi everyone, can anyone help me please
I have a stored procedure that works quite well and returns one of the following codes, 1 2 3 or 4 which is dependant on business criteria.  This sp works well and satsisfies all the criteria etc.
I am calling this SP in asp.net (c#) like this
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["blah"].ConnectionString))        {            int? intSQLCheck = null;
            con.Open();            SqlCommand cmd = new SqlCommand("check_values", con);            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@param_clothing_id", this.DropDownList2.SelectedValue.ToString()));            cmd.Parameters.Add(new SqlParameter("@empNo", this.DropDownList1.SelectedValue.ToString()));            cmd.Parameters.Add(new SqlParameter("@createdBy", this.txtEmpNo.Text.ToString()));            cmd.Parameters.Add(new SqlParameter("@price", this.txtPrice.Text));            cmd.Parameters.Add(new SqlParameter("@qty", this.txtQty.Text));            //cmd.Parameters.Add(new SqlParameter("@returnValue", 0));            cmd.Parameters.Add(new SqlParameter("@returnVal", 99));            cmd.Parameters["@returnVal"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
 
What I would like to do know is use a variable intSqlCheck to hold the return value from the SP (and then do some processing of business logic using the variable)
Any ideas anyone please

View 2 Replies View Related

SQL Output Parameters

Mar 5, 2008

 Hi, I have a form with a detailsview and a sqldatasource.  The sqldatasource uses a stored procedure as the select command.  This stored procedure requires 2 input parameters and returns 3 output parameters. A button on the form is enabled or disabled depending on the value of the output parameters. When the page loads, I get a "Object reference not set to an instance of an object" message, caused by the null value of an output parameter.  SQL Profiler shows that the input parameters are correct, and if I run the stored procedure directly, it's okay. Can anybody tell me why I wouldn't get the output parameters? Thanks,Neil 

View 1 Replies View Related

Sql Output Parameters

Oct 16, 2007

I am new to SQL and trying to work out how to do this

i have a table called projectAllocationLog with 4 fields, there can be many users of any particular project, ie
ID,projectID, User, Date
4 5000, 6, 10/02/2007
3 5000, 12, 08/07/2005
2 5004, 13, 08/05/2003
1 5000, 14, 04/05/2001

I want to write a stored proceedure that I can pass in the projectID, and return the most current user, this will always be the user with the highest ID for the project

ie for project 5000 it will be user 6

I was thinking along the lines of for each project select the top 1 from the table. where I can pass in the projectID, and pass out the ID field of the table. i am just not sure how to start this.

Any help would be appreciated.

View 4 Replies View Related

Output Parameters From Row With Max Id

Jul 31, 2007

I am trying to select multiple values from the row with the max id,and output them within my stored procedure. How would I combine thefollowing two rows so that I only need to use "from mytable whereIDnum = (select (max(IDnum)) from mytable))" once. I have at least 8other outputs I will be setting and would like this to work moreefficiently than setting each individually... if possible.set @outmtd = (select outmtd from mytable where IDnum = (select(max(IDnum)) from mytable))set @outytd = (select outytd from mytable where IDnum = (select(max(IDnum)) from mytable))Thanks for any help you can giveMatt

View 3 Replies View Related

Output Parameters

Apr 3, 2007

how do i include output parameters from a stored procedure into a report?

View 2 Replies View Related

Output Parameters From An SqlDataSource

Feb 21, 2008

Hi all,I have a stored procedure which sets an output variable. How could I access this after calling an SqlDataSource.Select()? ThanksChris 

View 9 Replies View Related

Output Parameters, Cookies And C#

Sep 9, 2004

Hello, i got this working using vb.net but becuase I'm learning asp.net I thought it would be a ggood idea to replicate the same function using C# as well1

The problem is I receive an output parameter through a stored procedure which is the users id.

I want to add this to a cookie as I know I'll be using it again and again, but the error I keep getting is cannot cxonvert type 'string' to type 'int'.

I've tried using conversion functions to make the value returned into an int value but I had no success!

I did this -

int id = (int)mycommand.Parameters["@id"].Value;

and then try to save that to the cookie but the error keeps appearing, I've also tried using the parse and ToInt32 function but the same/similar problems - any ideas?

Thanks

View 1 Replies View Related

SqlDataReader Or Output Parameters?

Oct 10, 2005

Hi all,Is there a data transfer difference between returning a datareader that only has one row or using output parameters?  For instance, I have a login page that shows the last login, member since, display level, number of file views, etc.  Currently I am returning a datareader that reads one row and assigns this data to labels.  But would it be quicker to use OUTPUT parameters instead of using a dataread and just get the values from the command object?ThanksJosh

View 3 Replies View Related

SP: Default Value For Output Parameters

Feb 8, 2005

When I call a proc with one input parameter and two output parameters, (all the three parameters having defaults in the proc), I was expecting to see these values defaulted to in the proc. But apparently, this not the case. Could someone tell me what am I doing wrong here? Appreciate your time.

USE Pubs
GO

CREATE PROCEDURE dbo.MyTestProc
(@InputParamVARCHAR(30) = 'Input',
@OutPutParam1VARCHAR(30) = 'OutputParam1' OUTPUT,
@OutPutParam2VARCHAR(30) = 'OuputParam2' OUTPUT)
AS
BEGIN
SELECT @InputParam, @OutPutParam1, @OutPutParam2
END
GO

-- Call to the proc
USE Pubs
GO

DECLARE @I1VARCHAR(30)
DECLARE @P1VARCHAR(30)
DECLARE @P2VARCHAR(30)

SET@I1 = 'PassedInput'


EXECdbo.MyTestProc
@I1,
@P1 OUTPUT,
@P2 OUTPUT

SELECT@I1, @P1, @P2

EXEC dbo.MyTestProc

View 3 Replies View Related

String Output Parameters

Feb 23, 2008

Hi can any one explain why when I specify an outparameter as a string I only get back the first letter of the string when I call the procedure from .net?

my procedure is...

ALTER PROCEDURE dbo.UpdateUserPACSPASDetails
@UserGUID uniqueidentifier,
@UserPACSName varchar(32),
@PASName varchar(32),
@UserPACSPassword varchar(95),
@UserPACSPasswordIV varchar(95),
@UserFriendlyName varchar(32),
@Message varchar(32) OUTPUT,
@MessageNumber int OUTPUT

AS

SET NOCOUNT ON

DECLARE @tmpGUID as uniqueidentifier /*stores the temp guid from the function */
DECLARE @ReturnDomainName as varchar(32) /*stores the tmp domainname of the user who has the PACS name */

SET @Message=CAST('' as varchar(32))
SET @MessageNumber= 0

/* first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPACSName(@UserPACSName)

IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN

/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 1
RETURN

END
END

/*now check the PAS name to see if it exists or is already in use by someone else
first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPASName(@PASName)

IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN

/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 2
RETURN

END
END




UPDATE tblUsers
SET UserPACSName=@UserPACSName,UserPACSPassword=@UserPACSPassword,
UserPACSPasswordIV=@UserPACSPasswordIV, PASName=@PASName, UserFriendlyName=@UserFriendlyName

WHERE UserGUID=@UserGUID

SET @Message=CAST('' as varchar(32))
SET @MessageNumber=3

RETURN

I call this proc via a vb.net program using a command.executenonquery and then look at the parameter.

For some reason I only get the first letter of the string back! i.e. if the string is LSDNET/nik I get 'L'.

If I run the proc from visual studio step in to procecure it works fine!

the vb I'm using is... (the createaddparameter simply creates a bunch of parameters to add to a command -with the specified names and dbtypes).

I'm obviously missing a trick but what it is I can't see.
I seem to be able to pass all other data types back easily!

Dim LoadUserCommand As New SqlCommand

'create add command
Dim CreateAddParameter() As String = {"@UserGUID", _
"@UserPACSName", "@PASName", "@UserPACSPassword", _
"@UserPACSPasswordIV", "@UserFriendlyName", "@Message", "@MessageNumber"}
Dim UpdateDBTypes() As DbType = {DbType.Guid, DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, _
DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, DbType.Int32}


LoadUserCommand = lclSQLHelper.CreateOLEDBCommand("UpdateUserPACSPASDetails", CreateAddParameter, UpdateDBTypes, lclDBConn.DBConnection)

LoadUserCommand.Parameters(6).Direction = ParameterDirection.Output
LoadUserCommand.Parameters(7).Direction = ParameterDirection.Output

'update the data in the data table in the database

LoadUserCommand.Parameters(0).Value = lclUser.UserGUID
LoadUserCommand.Parameters(1).Value = lclUser.UserPACSName
LoadUserCommand.Parameters(2).Value = lclUser.UserPASName
LoadUserCommand.Parameters(3).Value = lclUser.UserPACSPassword
LoadUserCommand.Parameters(4).Value = lclUser.PACSPasswordIV
LoadUserCommand.Parameters(5).Value = lclUser.UserFriendlyName
LoadUserCommand.Parameters(6).Value = ""
LoadUserCommand.Parameters(7).Value = 0

Try

Dim lclRet As Integer = LoadUserCommand.ExecuteNonQuery()

Dim tmpDomainName As String
tmpDomainName = LoadUserCommand.Parameters(6).Value.ToString 'read the name back

Dim tmpMsgNumber = LoadUserCommand.Parameters(7).Value 'read the message number back

Select Case tmpMsgNumber 'not written yet as I haven't got this far!!!
Case 1 'pacs username already exists
Case 2 'pas username already exists
Case 3 'execute fine and all names updated



End Select

View 3 Replies View Related

Sp_executesql + Output Parameters

Sep 4, 2007

can somebody let me understand how this Output parameters works in sp_executesql / Dynamic TSQL

I want to store a integer value resulting from executing a dynamic query. I was trying to follow the syntax but to be honest I didn't get it, and resulted in err. So can somebody help me in understanding how it works and how and where to declare the variables to be output and what command does that Output etc.

Thanks a lot in advance

View 4 Replies View Related

Stored Procedure With Output Parameters

Feb 11, 2008

i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.

can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.

View 3 Replies View Related

How Do I Retrieve Results Of The Output Parameters??

Feb 17, 2008

I am able to perform my Stored Procedure, but I can't figure out how to get access to the OUTPUT parameters!Here is my code:   Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim mySqlDataSource As SqlDataSource = New SqlDataSource

mySqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

mySqlDataSource.ConnectionString = "my connection string"

mySqlDataSource.InsertCommand = "MyStoredProc"

mySqlDataSource.InsertParameters.Add("someId", TypeCode.Int16, 166)

mySqlDataSource.InsertParameters.Add("someNumber", TypeCode.Int16, 4)

' Add Some Parameter
Dim someParameter As Parameter = New Parameter("someParameterName", TypeCode.String)
someParameter.DefaultValue = New String("Some Value")
someParameter.Direction = Data.ParameterDirection.Output

mySqlDataSource.InsertParameters.Add(someParameter)

mySqlDataSource.Insert()


End Sub
  Any ideas as to how I get the new value that was "hopefully" assigned to someParameter??? Thanks for any help 

View 3 Replies View Related

Output Parameters And Select Statements

Mar 17, 2004

Hi,

thanks for reading!

Here is my problem: I have a strored procedure that inserts some records

into one table and then selects some records from another table at the end.

The stored procedure takes several parameters, first one of them is marked as

OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.

If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.

if i try to return the @@identity as a plain select statement instead of through the parameter
i get System.DBNull.

I hope you can shed some light on this for me. Here is my stored procedure:



CREATE PROCEDURE cwSaveProductInquiry
@inquiryId int OUTPUT,
@libraryName nvarchar(500),
@contactName nvarchar(200),
@address nvarchar(100),
@city nvarchar(50),
@state nvarchar(3),
@zip nvarchar(10),
@phone nvarchar(50),
@email nvarchar(100),
@comment nvarchar(3000),
@productIds nvarchar(2000)

AS

INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)


--i tried including this statement at the end as well but that did not do the

--trick either

select @inquiryId=@@IDENTITY FROM INQUIRY





set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @objectId varchar(200) -- this holds each array value as it is returned

if(@productIds is not null)
begin
while patindex('%,%' , @productIds) <> 0
begin
select @separator_position = patindex('%,%' , @productIds)
select @objectId= left(@productIds, @separator_position - 1)
INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId)
select @productIds = stuff(@productIds, 1, @separator_position, '')
end
end
set nocount off

Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in
(Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)

GO

View 3 Replies View Related

Stored Procedure Output Parameters

Aug 3, 2004

Hi

I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.

I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?

View 3 Replies View Related

Stored Procedure And Output Parameters

Dec 3, 2005

EXEC('SELECT COUNT(docid) AS Total FROM docs  WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?

View 2 Replies View Related

Using SqlDatASource Programmatically - Output Parameters

May 13, 2006

I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues.
The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key:
ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY()
This works fine when run from Sql Server Management Studio and @NewID has the correct value.
My data access code is roughly as follows:
dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try   Dim rv As Integer = dsrc.Insert()   newid = parms("NewID")   Return newidCatch ex As Exception   Return -1End Try
The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value.
Has anyone tried to do this and can tell me what I am doing wrong?
Jon
 

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved