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


Post New Web Links

output parameter sp/c#

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :ASP.Net
 
 Hi I have a problem getting my code right. I got "String[1]: the Size property has an invalid size of 0." in my c# code. SP:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE sp_GetUserName ( @UserNames UserNamesType READONLY, @tmpUserNames nvarchar(max) OUTPUT) AS BEGIN SET NOCOUNT ON; DECLARE @tmpUser nvarchar(max); SELECT @tmpUser = (FirstName + ' ' + LastName + ',') FROM Users WHERE [Login] = (SELECT UserName FROM @UserNames) SET @tmpUserNames = @tmpUser RETURN END GO   public static string GetUserName(DataTable dtUser) { string err = String.Empty; string userResult; string sqlText = "sp_GetUserName"; try { using (SqlCommand cmd = new SqlCommand(sqlText, connect.GetSqlConnection(out err))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserNames", SqlDbType.Structured).Value = dtUser; cmd.Parameters.Add("@tmpUserNames", SqlDbType.Text).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); userResult = (string)cmd.Parameters["@tmpUserNames"].Value; return userResult; } } catch (SqlException ex) { return null; } } Can someon


View Complete Post


More Related Resource Links

Enterprize library 4.1 getting output parameter after adding record in db, plz guide

  

Hi,

I need to get output parameter (flag) from db after saving record in database. I am using Microsoft Enterprise Library 4.1 for DB. I am inserting record using I Data reader.

Please guide

Thanks


output cache to be used only when parameter has changed + old question of user control not answered

  

hello,

i have 2 questions about output cache:

1.is it just me , or does it seems that it is used only after the second time that the user reaches the webpage? if so, why, and how can i tweak it to my needs? my guess is that it wouldn't be logical to cache every time a user reach a webpage, but only when it happens enough times.

2.i know that i can use "varybyparam" for using the output cache when the parameter doesn't change , but it seems that it would use the output cache even if there is no parameter , or if the specified paramter is not there. is there a way to overcome this?

3.an old question that somehow marked as "answered" , yet i didn't solve : suppose i created a user control and i gave it values through the CTOR via the aspx file . is it possible to show the values from within the split/design mode? remember that i've talking about ASP.NET and not simply C# winforms . i write this because this question was on :

http://forums.asp.net/t/1588671.aspx

and the answer that i got was for C# and winforms . i've tried to do the same for ASP.NET , but i've failed.

can anyone please help me?


optional output parameter in CLR Stored Procedure

  
I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it. the declaration goes as follow   <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/> Public Shared Sub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> Optional ByVal De As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Para As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Copia As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Oculta As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Assunto As String = "", <SqlFacet(MaxSize:=-1)> Optional ByVal Mensagem As String = "", _<br/> <SqlFacet(MaxSize:=-1)> Optional ByVal Anexo As String = "", _<br/> <strong><SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""</strong> , _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Smtp As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Usuario As String = "",

SQL Server 2008 Stored Procedure output parameter

  
I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

SP Output parameter help

  
Hi,I hope someone can help me with sorting this out... I'm trying to build a stored procedure that returns multiple members' names when fed their memberIds.So far this is what I've got - it works fine with one exception - I'm not sure how to write the syntax so I can get the output parameter, @MemberNames? The code below outputs the names but without a column heading. I just want the names to be a string in the output parameter @MemberNamesCREATE PROCEDURE [dbo].[sp_NameResolution] --Add the parameters for the stored procedure here @ProfileId numeric, @MemberIds varchar(8000), -- this might be a string of ids numbers like 34,56,345 @MemberNames varchar(8000) Output, @SQL AS Varchar(8000) = NULL AS BEGIN SET NOCOUNT ON; SET @SQL = 'SELECT (RTRIM(Fname) + '' '' + RTRIM(Lname)) FROM MEMBERS WHERE (MemberIds IN (' + @MemberIds + ')) AND (ProfileID = '+ CAST(@ProfileId AS VarChar)+ ')' PRINT @SQL EXEC(@SQL) END I've tried different variations like..SELECT @MemberNames = (RTRIM(Fname) + '' '' + RTRIM(Lname)) ... etcbut because I'm executing it using EXEC(@SQL) it has a cow and won't output the parameter...Any ideas?Thanks a bunch,Jason

Rounding problem for money output parameter from stored proc

  
I have a GridView sourced by a SQL Server stored procedure with several databound fields, including a money column for item values. My stored proc returns the data through a SELECT and everything looks good. The stored proc also has a single output parameter that sums the total value of the displayed data, and this value is displayed in a label above the GridView. The output parameter (defined as money on the SQL Server side) is passed back to the app in good shape, but is rounded when displayed in the DataSource_Selected event handler. So a total of $30,155.22 in SQL Server shows up as $30155 on my aspx.In debugging, I have confirmed that the value leaving SQL Server is correct (30155.2200), and I can see that the value coming IN to the ASP-side event handler is already rounded. What do I need to do here to coerce/force it to show as money/currency?Relevant snippets:Stored Proc:... @onHandValue money OUT...  CREATE TABLE #tempValueOnHand(... valueOnHand money )... set @onHandValue = (select round(sum(valueOnHand), 2) from #tempValueOnHand) set @onHandValue = (select sum(valueOnHand) from #tempValueOnHand)And again, the output parameter @onHandValue is coming back, just already rounded.On the ASP side:    Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourc

Exposing data contract without passing as input or output parameter for any operation contract

  

I have a method in  wcf which returns stream of custom object. I need to deserialize this stream at client side, but my custom class which is exposed as data contract is not visible through client proxy. When I add a dummy operation contract which returns the custome object I am able to get this custom class. I dont want to write this dummy method as there are many such custom classes.

Is there any way to expose data contract without writing any operation contract that returns or accepts data contract type object?

Example:

[

 

ServiceContract]

Exposing data contract without passing as input or output parameter for any operation contract

  

I have a method in  wcf which returns stream of custom object. I need to deserialize this stream at client side, but my custom class which is exposed as data contract is not visible through client proxy. When I add a dummy operation contract which returns the custome object I am able to get this custom class. I dont want to write this dummy method as there are many such custom classes.

Is there any way to expose data contract without writing any operation contract that returns or accepts data contract type object?


How to get total record count in an OUTPUT Parameter

  

I couldn't get the total record count in a, in my stored proc, in a given scenario. Please help

 

My SP looks like this:

create proc RecordCount

       @CustomerID varchar(10),

       @RecCnt int OUTPUT

as

begin tran

if(CustomerID = 'ABC')

@RecCnt = select count(*) from Customers where CustomerID='ABC'

commit

 

begin tran

if(customerID='XYZ')

@RecCnt = select count(*) from Customers where CustomerID='XYZ' and TranDate > '05/01/2009'

commit

GO

 

-- This is how I called my SP

declare @rc int

exec RecordCount 'XYZ', @RecCnt = @rc OUTPUT

select @rc


Function with output parameter

  

Hi

is it possible to create user define function with 3 output parameter. something like ref out parameter in C#.

Best Regards. Morteza

 


Retrieve function's output parameter

  

Hi,

 

Is it a way to retrieve the particular function's list of output parameter fields?

the following is the sample code for the list of input parameter fields.

SELECT 
    p.*
  FROM sys.objects vw
  INNER JOIN sys.parameters p ON p.object_id = vw.object_id
  WHERE vw.type IN (N'FN', N'IF', N'TF') and is_ms_shipped = 0

 


Meng Chew

Capturing SQL OUTPUT parameter

  

Hi All,

I'm trying to capture an output parameter from an SQL insert.

for

CREATE TABLE [dbo].[Test](
 [TestID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
 [Test1] [int] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED

INSERT INTO [Test] ([Test1]) OUTPUT inserted.TestID as TestOut VALUES 1))  works fine in SQL Server and throuws no errors asp.net, bu tI can't find a way to capture TestOut.

I create a label and ds:

<asp:Label ID="lbl_ID" runat="server" Text=""></asp:Label

<asp:SqlDataSource ID="ds_Ins2" runat="server" ConnectionString="<%$ ConnectionStrings:cs_StatusBuildTest %>" 
        InsertCommand="INSERT INTO [Test] ([Test1]) OUTPUT inserted.TestID as @TestOut VALUES (@Test1))"  oninserted="ds_Ins2_Inserted"
        SelectCommand="SELECT [TestID], [Test1] FROM [Test]" 
        <InsertParameters>
            <asp:Parameter Name="Test1" Type="Int32" />
            <asp:Paramete

How do you set SSIS package variable equal to an output parameter from a stored procedure that is ty

  

I have a stored procedure insertXXX that has one OUTPUT parameter of type numeric. The stored procedure looks like:

ALTER

 

PROCEDURE [dbo].[insertIntoTable]
@outputID
AS numeric OUTPUT

AS

BEGIN

 

How to call Oracle Stored Procedure which has an output parameter from SSIS?

  

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

 

    PROCEDURE Interface_Begin

    (p_from_dttm       OUT varchar2,

     p_error_code            OUT number,

     p_error_text       OUT            varchar2,

     p_proc_name        OUT varchar2);  

 


DataReader or DataSet to read an output parameter not essential?

  

I always thought we had to use a DataReader or a Dataset to read a given SQL data but i realized that it is not necessary to use them to read an SQL Output Parameter (im not a SQL expert).

AM i right???
Is it okay to do it that way?
Is it faster than using a DataReader?

-----------------------------------------------------------------

.vb

...

Dim myConnectionDB As New SqlConnection(DbWrite)
Dim myCommand As SqlCommand = New SqlCommand("_AddUsers", myConnectionDB)

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add(New SqlParameter("@UserId", SqlDbType.VarChar))
myCommand.Parameters("@UserId").Value = UserId

.... others parameters ...

myCommand.Parameters.Add(New SqlParameter("@RowCount", SqlDbType.Int))
myCommand.Parameters("@RowCount").Direction = ParameterDirection.Output

        Dim rowsaffected As Integer

        Try
        

problem accesing output parameter from datalayer

  

Hi

in order to allow my design to keep recently inserted record on a formview i decided to use an output parameter.
Currently I have a data layer helper and a class that allows my table to have crud operations.

I have modified my insert stored procedure to the following:

ALTER PROCEDURE [dbo].[InsertContratos]
(
 @Contrato int,
 @Persona int,  
     @FechaLegalizacion datetime,
 @Vendedor smallint,
 @Programa smallint,
        @numero char(3),
       @Referencia1Nombre varchar(50)='',
 @Referencia1Apellido varchar(50)='',
 @Referencia1Telefono varchar(20)='',
 @Referencia2Nombre varchar(50)='',
 @Referencia2Apellido varchar(50)='',
 @Referencia2Telefono varchar(20)='',
        @Observaciones Text='',
        @Anulado char(1)='N',
 @ContratoInsertado int OUTPUT 

)
AS

INSERT INTO contrato
 (
 contnume ,
 persnume,
 contfere ,
 contfele ,
 vendnume,
 prognume,
 

How to get output parameter asp.net 3.5 database is mysql 5.1

  

Hi

I am using mysql 5.1. odbc driver 5.1.

I have created a stored proc and declared a parameter as output. In asp.net I am getting an error.

ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.11]OUT or INOUT argument 7 for routine facebookdb.SP_FB_Add_Registration is not a variable or NEW pseudo-variable in BEFORE trigger

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.11]OUT or INOUT argument 7 for routine facebookdb.SP_FB_Add_Registration is not a variable or NEW pseudo-variable in BEFORE trigger

please help me out how to get the value of output parameter


Categories: 
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