Assigning A Stored Procedure Return Value To A Control

Jan 11, 2008

Hi, I have an app where I am calling a stored procedure that runs and returns an integer.  I would like to assign this integer to a control on the page.  Here is a sample of code and below I will give the errors:
 Protected Sub NewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewButton.Click

        Dim tempconnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString)
        Dim tempproc As New Data.SqlClient.SqlCommand("SP_FetchProviders", tempconnection)
        Dim tempparam As New Data.SqlClient.SqlParameter

        tempproc.CommandType = Data.CommandType.StoredProcedure

        tempproc.Parameters.Add(New SqlParameter("@Agency_ID", Data.SqlDbType.Int))
        tempproc.Parameters("@Agency_ID").Value = "8" 'Using a number just to test, will be populated by a session variable later
        tempparam = tempproc.CreateParameter()
        tempparam.ParameterName = "@OutValue"
        tempparam.Direction = Data.ParameterDirection.ReturnValue
        tempparam.SqlDbType = Data.SqlDbType.Int
        tempconnection.Open()
        tempproc.ExecuteNonQuery()
        Dim labelfill As String
        labelfill = tempproc.Parameters("@OutValue").Value
        tempconnection.Close()

        CType(Me.DetailsView1.FindControl("OmbudINSDDL"), DropDownList).Items.Add(labelfill) 'This is inside of a detailsview templatefield
    End Sub
 
Here is the stored procedure
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER  procedure [dbo].[SP_FetchProviders]
    @Agency_ID     int
as

    Select Name, ProviderSubUnit_ID
    From ProviderSubunit a join Ombudsman b on a.contractproviders_id=b.contractproviders_id

    Where b.agency_id = @Agency_ID
       
    Order by Name
 
This takes the @Agency_ID as input variable, and has returns an integer (according to SQL management studio).  When I try to run my code I get the error that an sqlparameter with parametername @OutValue is not contained by this sqlparametercollection.
 This may be a simple question, but does the outvalue have to be declared in the stored procedure?  If so, can you provide the right syntax?  If not, can you offer a suggestion to populate the dropdownlist with the returned value?  Thanks in advance.

 

View 3 Replies


ADVERTISEMENT

Stored Procedure And Assigning @@IDENTITY Value

Nov 3, 2003

In the following stored procedure I would like to test whether @TopCategoryID is null. If so I would like to insert the @CategoryID value into the @TopCategoryID value. So, once @CategoryID recieves the @@IDENTITY value, how do I enter this same value into @TopCategoryID as well???



CREATE PROCEDURE sp_CT_InsertCategory



@Namevarchar(50),
@Description varchar(250),
@topCategory bit,
@ParentCategoryID int,
@TopCategoryID int,
@CategoryID int OUTPUT
AS
DECLARE @CurrID int


IF @CurrID IS NULL
BEGIN

INSERT
INTO CT_Category
(CategoryName, CategoryDescription, topcategory, parentcategoryid, topcategoryid)
VALUES
(@Name, @Description, @topcategory, @ParentCategoryID, @TopCategoryID)

SET @CategoryID = @@IDENTITY

IF @@ERROR > 0
BEGIN

RAISERROR ('Insert of Category failed', 16, 1)
RETURN 99

END

END
ELSE
BEGIN

SET @CategoryID = -1

END
GO



Thanks in advance

View 9 Replies View Related

How To Get Query Second Column Value/other Rows While Assigning To A Variable In A Stored Procedure?

Feb 6, 2008

Hi,
I'm try to get the query second column value when it is assinged to a varchar variable.
Ex:In SP below is statement I wrote
SET @Values  =  (SELECT COL1,COL2 FROM TABLE)
Question 1:  How to access the COL2 value from @Value2?
Question 2:  How to access the other row values if above returns more than a row?
Please send me the solution as soon as possible.
Thanks-Vikash/Bala

View 3 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

Help Assigning A Datasource To The Gmap Control In C#

Sep 18, 2007

I'm trying to assign a datasource to a gmap control in 2.0 so that i can get pushpins in the map for all the locations in the sql db. I've enclosed my code below as well as a link to the site that provides the control and their sample. Can anyone tell me why it won't show the pins? What am I doing wrong?
Thanks.
 My Code (sqllocations is a datasource):
GMap1.DataSource = sqllocations;
GMap1.DataBind();
Their site: http://en.googlemaps.subgurim.net/ejemplos/ejemplo_991000_DataSource.aspx
Their example (part of their explanation is not english, sorry):
 List<DataSourceField> fields = new List<DataSourceField>(); fields.Add(new DataSourceField(45, 2, "hola"));fields.Add(new DataSourceField(46, 3));// Lo comentamos porque al utilizar la clase DataSourceField, // pero si fuera un dataset o cualquier otra cosa, // le deberíamos dar los nombres correspondientes// GMap1.DataLatField = "lat";// GMap1.DataLngField = "lng";// GMap1.DataGInfoWindowTextField = "gInfoWindowText";GMap1.DataSource = fields;GMap1.DataBind();  
 

View 1 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

Assigning Values Based On InitialToggleState Of A Control In SSRS

Dec 21, 2006

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya



View 1 Replies View Related

Return And Assigning Values In Functions

Dec 19, 2007

Hi ,

I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples or some simple sample code just to take a look ?

For eg I have written the following function which I called from a stored procedure.
BEGIN
--Declare the return variable here
DECLARE @Rows NUMERIC(10)
DECLARE @RETURN_ENABLED VARCHAR(1)
-- Add the T-SQL statements to compute the return value here

SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP
INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item
WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and
not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and
PP.APPLICATION_CODE = @is_appl AND
PP.MENU_NAME=@menu_name
Group By Profile_INdex

IF @Rows > 0
SELECT @RETURN_ENABLED = 'N'
ELSE
SELECT @RETURN_ENABLED = 'Y';


-- Return the result of the function
RETURN @RETURN_ENABLED;

END

Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ?

From the stored procedure I'm calling the function inside a CTE.

;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled)
AS
(--Anchor.
select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item ,
dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
From sys_menu_item as tb1
where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help')
And tb1.item_parent= @menu_name
--Members
UNION ALL
select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item ,
dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled
from sys_menu_item as tb2 , GetHierarchy
where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help')
And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name
)
select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled
From GetHierarchy

Am I doing it correctly ?

I would appreciated any help you could give me.

Thank you

View 5 Replies View Related

Stored Procedure Version Control

Sep 13, 2001

Is there anyone out there who has successfully setup version control of stored procedures with Source Safe and Visual Interdev?

View 1 Replies View Related

Stored Procedure Source Control

Jul 20, 2005

Hi,I am trying to put SQL Server Stored Procedures into Sourcesafe as perthe Microsoft Knowledge Base article 818368, but have run into aproblem.The web server is SQL Server 2000 running on Windows 2003 Server. Theclient dev environment is Visual Studio 2003 Enterprise DeveloperEdition.I have carried out the following steps successfully:-1. Installed Sourcesafe client tools on the Server (sourcesafe is onanother server)2. Run the MSSQLServer service under a domain account that has Readand Write access to the Sourcesafe database.3. Added the above user to Sourcesafe using the Administrator tool.4. Installed the VS6 Stored Procedure Version Control components onthe Server5. Enabled Version Control for Stored Procedures on the clientHowever when I right-click on the Stored Proc node in Server explorerI do not get any of the Sourcesafe menu options. They are not greyedout, they are simply not there!Any help would be appreciated.Alternatively if anyone has recommendations for other strategies orother tools to use for this purpose than Sourcesafe then this wouldalso be welcomeKarl

View 1 Replies View Related

Control Output Of Stored Procedure

Feb 19, 2008

I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.


CREATE PROC proc_Emp

@Sel_Country
AS
BEGIN


SELECT DISTINCT employee, hrid
FROM emp_table
WHERE country = @Sel_Country


@SEND_RESULTS =
CASE WHEN row_count = 0 THEN

SELECT 'No Country', 4444444444
ELSE

(send original results)
END


END

I need to:



Query for the employee list

If the row count is zero/null then return fixed values

Else return the orginal data from Step 1.
I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.

Rob

View 7 Replies View Related

Control The Output Of Stored Procedure

Nov 14, 2007



Hi all,
I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row.
The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.

Is there any way i could do this?


Exmaple:
In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!

Procedure dbo.AddressInsert(....)
as
BEGIN

Insert INTO Adress ....

SELECT * FROM AdressView WHERE AddressID = @@identity
END

Procedure dbo.CustomerInsert(...)
as
begin
-- transcation begin...

EXEC dbo.AddressInsert

INSERT INTO Customer....

-- end transcation

SELECT * FROM Customer WHERE CustomerID = @@identity

end

View 4 Replies View Related

How Can Control Transactions For Creating Stored Procedure ?

Apr 30, 2007

I create StringBuilder type forconcating string to create a lot of stored procedure at once However When I use this command BEGIN TRANSACTIONBEGIN TRY--////////////////////// SQL COMMAND /////////////////////////           -------------------- This any command --///////////////////////////////////////////////////////////    --COMMIT TRANEND TRYBEGIN CATCH    IF @@TRANCOUNT > 0        ROLLBACK TRANSACTION;END CATCHIF @@TRANCOUNT > 0    COMMIT TRANSACTION;   on any command If I use Create a lot of Tablessuch as  BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////CREATE TABLE [dbo].[Table1](      Column1 Int ,      Column2 varchar(50) NULL  ) ON [PRIMARY]CREATE TABLE [dbo].[Table2](
     Column1 Int ,
     Column2 varchar(50) NULL
 ) ON [PRIMARY]
CREATE TABLE [dbo].[Table3](

     Column1 Int ,

     Column2 varchar(50) NULL

 ) ON [PRIMARY]
--///////////////////////////////////////////////////////////
    --COMMIT TRAN
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;   It correctly works. But if I need create a lot of Stored procedure  as the following code :   BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////
CREATE PROCEDURE [dbo].[DeleteItem1]
    @ProcId        Int,
    @RowVersion        Int
AS
BEGIN
    DELETE FROM [dbo].[ItemProcurement]
    WHERE
        [ProcId]    =    @ProcId    AND
        [RowVersion]    =    @RowVersion   
END CREATE PROCEDURE [dbo].[DeleteItem2]

    @ProcId        Int

AS

BEGIN

    DELETE FROM [dbo].[ItemProcurement]

    WHERE

        [ProcId]    =    @ProcId 

END
CREATE PROCEDURE [dbo].[DeleteItem3]


    @ProcId        Int


AS


BEGIN


    DELETE FROM [dbo].[ItemProcurement]


    WHERE


        [ProcId]    =    @ProcId 


END

--///////////////////////////////////////////////////////////
    --COMMIT TRAN
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION; It occurs Error ?????  Please help me How should I solve them ?   

View 1 Replies View Related

ListBox Web Server Control And Stored Procedure

May 15, 2008

Hi, I've a  ListBox Web Server Control where I select a list of citiesnow I would like to create a stored procedure with the selected values... please note that I use metods like these for execute my Stored procedure public DataSet Getgfdfgh(SqlConnection connection, String IDAttivitaTipo, DateTime DataInizio, DateTime DataFine)        {            ConnectionState currState = connection.State;            if (((connection.State & ConnectionState.Open) != ConnectionState.Open))                connection.Open();            try            {                SqlParameter[] parameters = new SqlParameter[3];                parameters[0] = new SqlParameter("@IDAttivitaTipo", IDAttivitaTipo);                parameters[1] = new SqlParameter("@DataInizio", DataInizio);                parameters[2] = new SqlParameter("@DataFine", DataFine);                SqlCommand cmd = CreateStoreProcedureCommand("Getfhdfh", connection, parameters);                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                DataSet ds = new DataSet();                adapter.Fill(ds);                return ds;            }            finally            {                if ((currState == ConnectionState.Closed))                    connection.Close();            }        }   How Can I manage a list of values ??Thanks for help me!! 

View 1 Replies View Related

Passing Control Flags To Stored Procedure

Jul 23, 2005

Wanted to know which among these options is better and why? Or if theircould be scenarios where we could opt for one of these.a) flags passed from code to control the execution of queries within astored procedure i.e. - where queries within a single stored procedureare controlled by flags passed to them.ORb) Break individual queries into separate stored procedure

View 4 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

Analysis :: Possible To Use Stored Procedure To Control SSAS Partitions?

Aug 19, 2015

I have defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.

Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0" get the following error (in German):

OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.

View 2 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

The Return Of Problem Assigning Value To Package Variable From Data Flow Script Component

Jul 10, 2006

I have a Data Flow Script Component(Destination Type) and in the properties I have a read/write variable called User::giRowCount

User::giRowCount is populated by a Row Count Component previously in the Data Flow.


After reading http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97494&SiteID=1 it is very clear that you can actually only use  variables in the PostExecute of a Data Flow Script Component or you will get an error
"Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute."




What I need to do is actually create a file in the PreExecute and write the number of records = User::giRowCount as second line as part of the header, I also need to parse a read/write variable such as gsFilename to save me hardcoding the path

(Me.Variables.gsFilename.ToString),(Me.Variables.giRowCount.ToString)

 -they must go in the PreExecute sub --workarounds please-here is the complete script component  that creates a file with header, data and trailer --Is there any workaround

Thanks in advance Dave
 
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.Text
Imports System.Configuration
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
 
Public Class ScriptMain
    Inherits UserComponent
    'Dim fs As FileStream
    Dim fileName As String = "F:FilePickUpMyfilename.csv"
    'Dim fileName = (Me.Variables.gsFilename.ToString)
 
    Dim myFile As FileInfo = New FileInfo(fileName)
    Dim sw As StreamWriter = myFile.CreateText
    Dim sbRecord As StringBuilder = New StringBuilder
 
 
    Public Overrides Sub PreExecute()
 
        sbRecord.Append("RECORD_START").Append(vbNewLine)
 
    End Sub
 
 
 
    Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
 
        sbRecord.Append(Row.ProjectID.ToString)
        sbRecord.Append(Row.TransactionRefNum.ToString)
        sbRecord.Append(Row.BillToCustomerNum.ToString)
        sbRecord.Append(Row.BillToAccountNum.ToString)
        sbRecord.Append(Row.BillToLineNum.ToString)
        sbRecord.Append(Row.BillToReassignmentNum.ToString)
        sbRecord.Append(Row.ChargeCode.ToString)
        sbRecord.Append(Row.NotificationMethod.ToString)
        sbRecord.Append(Row.AdjustmentAmount.ToString)
        sbRecord.Append(Row.AdjustmentDate.ToString)
        sbRecord.Append(Row.ReparationGivenFlag)
        sbRecord.Append(Row.BillingSystemProcessingErrorCode.ToString).Append(vbNewLine)
       
    End Sub
 
    Public Overrides Sub PostExecute()
        sbRecord.Append("RECORD_COUNT").Append((vbTab))
        sbRecord.Append(Me.Variables.giRowCount.ToString).Append(vbNewLine)
      sbRecord.Append("RECORD_END").Append(vbNewLine)
       'Now write to file before next record extract
        sw.Write(sbRecord.ToString)
        'Clear contents of String Builder
        sbRecord.Remove(0, sbRecord.Length)
 
 
       'Close file
        sw.Close()
 
    End Sub
 
 
End Class

Has anyone got a workaround

thanks in advance

Dave

View 6 Replies View Related

Using The Return Value From A Stored Procedure In VB

Nov 20, 2007

 Hi guys I know this is a really common question, and I have read loads of replies on it but everything I try does not work.  I have written a small stored procedure in SQL server to upload images to a table and return the new ID using scope_identity.  I have tested it and it works fine.  here it is:*******    @siteID numeric(18,0),    @imgNum numeric(18,0),    @title NVarchar(50),    @MIMEtype nchar(10),    @imageData varbinary(max)ASBEGINSET NOCOUNT ONdeclare @imageID intINSERT INTO [site_images] ([img_siteID], [img_num], [img_title], [img_MIME], [Img_Data]) VALUES (@siteID, @imgNum, @Title, @MIMEType, @ImageData) SET @imageID = SCOPE_IDENTITY()RETURN @imageIDSET NOCOUNT OFF************If I run this in management studio express it runs fine and returns the ID under 'return value'. The problem I have is trying to actually call that return value in VB.  If I try using these lines:Dim returnParam As SqlParameterreturnParam = New SqlParameter("@imageID", SqlDbType.UniqueIdentifier)returnParam.Direction = ParameterDirection.OutputcmdTest.Parameters.Add(returnParam)withcnBKTest.Open()cmdTest.ExecuteNonQuery() imageIDparam = returnParam.value.toStringcnBKTest.Close()  I get the error "procedure has too many arguments specified"And if I try to access the return value like this: imageIDparam = cmdTest.Parameters("@return_value").ValueI get the error "@return_value is not contained by this sqlparametercollection"  What am I doing wrong?  Any help would be greatly appreciated. Robsa         

View 3 Replies View Related

No Return Value From This Stored Procedure

Nov 28, 2007

I have written this stored procedure but I get no return value (neither 0 nor 1). What I hope is when the transaction successful, return value 1. If fails, return value 0.1 set @TransactionOk = 0
2
3 BEGIN TRAN
4
5 UPDATE WhiteList_IMEI SET WhiteList_IMEI_Used = 1, Whitelist_IMEI_UsedDate = getdate()
6 WHERE WhiteList_IMEI_Code = @IMEICode_New
7
8 IF @@ERROR <> 0
9 BEGIN
10 ROLLBACK TRAN
11
12 PRINT ('Error. Contact Software Engineer.')
13 RETURN
14 END
15
16 COMMIT TRAN
17 set @TransactionOk = 1
  

View 6 Replies View Related

Return Value From Stored Procedure

May 16, 2008

Hi,
I have been trying to this this for quite a while with no joy can someone please tell me the error of my ways.  I am trying to add a new record by stored procedure, this I can do, but my problem lies with the returnvalue part of the procedure.  I cannot get it to work. When I debug it tells me that the "Specified cast is not valid" see C# code as i comment the line where it errors.  I enclose a sample stored procedure and its c# code.  Please can someone tell me where I am going wrong? as this is annoying me alot
SQL:create procedure SPUAddVehicleInsert
@VehicleDetailsRegistrationNumber varchar(50),
@VehicleDetailsMake varchar(50),
@VehicleDetailsModel varchar(50),
@NID bigint =null
as
insert into tblvehicledetails
(
VehicleDetailsRegistrationNumber,
VehicleDetailsMake,
VehicleDetailsModel,

)
values
(
@VehicleDetailsRegistrationNumber,
@VehicleDetailsMake,
@VehicleDetailsModel,

);
select @NID = scope_identity();c# code on sqldatasource:  protected void dsAddVehicle_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
Int64 VID = (Int64)e.Command.Parameters["NID"].Value; //errors with specified cast is invalid

Response.Redirect("details.aspx?VID=" + VID.ToString());

}
protected void dsAddVehicle_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
SqlParameter p = new SqlParameter("NID", SqlDbType.BigInt);
p.Direction= ParameterDirection.ReturnValue;
e.Command.Parameters.Add(p);

sql datasource:<asp:SqlDataSource ID="dsAddVehicle" runat="server" ConnectionString="<%$ ConnectionStrings:National %>"
InsertCommand="SPUAddVehicleInsert" InsertCommandType="StoredProcedure" SelectCommand="SPUAddVehicleSelect"
SelectCommandType="StoredProcedure" OnInserted="dsAddVehicle_Inserted" OnInserting="dsAddVehicle_Inserting">
<InsertParameters>
<asp:Parameter Name="VehicleDetailsRegistrationNumber" Type="String" />
<asp:Parameter Name="VehicleDetailsMake" Type="String" />
<asp:Parameter Name="VehicleDetailsModel" Type="String" />
</InsertParameters>
</asp:SqlDataSource> 

View 4 Replies View Related

Stored Procedure Return Value

Jun 20, 2005

I have a stored procedure that returns an integer value, declared as:Public Function MyProc(..) As Int32   .   .   Return <integer>End FunctionI return an integer value, and can do this and get the value returned from the method, as such:declare @rc intexec @rc = dbo.MyProc <params>select @rcThis returns the value; how do I return the value to code and get the value; I've been debugging and that is my problem, I can't get the value to return.Thanks a lot.

View 2 Replies View Related

Return Value In Stored Procedure

Nov 20, 2005

Hello all of members, I have written a Stored Procedure.that creates a new account and then returns a value witch displays a result to me.if result is 1 "Username already exists" or 2 "E-Mail already exists".I did it with "Return" instruction.But, I don't know how can I get the returned value in ASP.NET(VB.NET)? Please help me. Thanks in advance

View 3 Replies View Related

Getting A Return Value From A Stored Procedure

Nov 25, 2005

Hi all,Is there anyway to get a returned value from a called Stored Procedure from within a piece of SQL? For example, I have the following code...DECLARE @testval AS INTSET @testval = EXEC u_checknew_dwi_limits '163'IF (@testval = 0)BEGIN     PRINT '0 Returned'ENDELSEBEGIN     PRINT '1 Returned'END...which
as you can see calls a SP called 'u_checknew_dwi_limits'. This SP
(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want to
assign that value to the '@testval' variable (as you can see in my
code) - but Query Analyser is throwing an error at me. Is this the
correct way to do this?ThanksTryst

View 2 Replies View Related

How To Get The Return Value Of A Stored Procedure

Apr 28, 2006

I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end: Return Scope_Identity()
I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string.
I have intReturn defined as an integer
I've tried :Dim retValParam As OleDbParameter = cmd.Parameters.Add("@RETURN_VALUE", OleDbType.Integer)retValParam.Direction = ParameterDirection.ReturnValueintReturn=cmd.Parameters("@RETURN_VALUE").Value
whenever I add this section - I get an error that there are too many arguments for the sProc.
I've tried:intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors.
What is the best way to do this in the code part (VB.Net)?
Thanks ahead of time

View 2 Replies View Related

Stored Procedure Return Value

May 16, 2006

I don't know whey this code does not return the values when I run it in sql server 2005 manager.set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[usp_usr_Add]

@FName nvarchar(30),
@LName nvarchar(30),
@UserName nvarchar(20),
@Password nvarchar(20),
@Email nvarchar(50),
@Country nvarchar(2),
@AIM nvarchar(10)
AS
SET NOCOUNT ON
BEGIN

DECLARE @usrID int
DECLARE @usrEmail int

SELECT @usrID = NULL
SELECT @usrEmail = NULL


SELECT @usrID = usrID FROM usr WHERE usrName = @UserName

IF (@usrID IS NOT NULL )
BEGIN
RETURN 1
END

SELECT @usrEmail = usrID FROM usr WHERE usrEmail = @Email

IF (@usrEmail IS NOT NULL)
BEGIN
RETURN 2
END



INSERT INTO usr (usrFName, usrLName, usrName, usrPassword, usrEmail, usrCountry, usrAIM, usrJoinDt)

VALUES (@FName, @LName, @UserName, @Password, @Email, @Country, @AIM, GetDate())
RETURN 0

END



 When i run this code i executes fine except when the two conditions become true they do not return thier values, nor does it return 0 when it inserts a row.

View 5 Replies View Related

Stored Procedure Return Value.

Oct 17, 2000

Hi

I have the following stored procedure which when exectuted within the Query Analyzer adds a record and returns the @SeqRunNo value. However when I call
it in the from ado the record is added but the value is empty.

I enter the parameter as:

.Parameters.Append .CreateParameter("@SeqRunNo", adVarChar, adParamOutput, 4)

and try and access it as:

SeqRunNo = oCommand.Parameters("@SeqRunNo").Value

Any ideas?

Thanks.


john








CREATE PROCEDURE up_mix_Set_Seq_Run_No
@IID int,
@TrackingID int,
@ADP_Code varchar(6),
@ClientID varchar(20),
@SeqRunNo varchar(4) OUTPUT
AS

DECLARE @Max_Seq_No int,
@Seq_No varchar(4),
@LastUpdate datetime

SELECT @Seq_No = QTR_SEQ_RUN_NO FROM Transmission_Quarter
WHERE IID = @IID
AND Tracking_ID = @TrackingID
AND ADP_Code = @ADP_Code

IF ISNULL(@Seq_No,0) = 0
BEGIN

SET @LastUpdate = GETDATE()

SELECT @Max_Seq_No = CAST(MAX(QTR_SEQ_RUN_NO) AS int)
FROM Transmission_Quarter
WHERE IID = @IID
AND ADP_Code = @ADP_Code

IF ISNULL(@Max_Seq_No,0) = 0
BEGIN
SET @SeqRunNo = '0001'
END
ELSE
BEGIN
IF @Max_Seq_No = 9999
BEGIN
SELECT @SeqRunNo = '0001'
END
ELSE
BEGIN
SELECT @SeqRunNo = REPLICATE('0', 4 - DATALENGTH(CAST(@Max_Seq_No AS VARCHAR))) + CAST((@Max_Seq_No + 1) AS VARCHAR)
END
END

INSERT INTO Transmission_Quarter
(IID, Tracking_ID, QTR_SEQ_RUN_NO, ADP_Code, Last_Updated_By, Last_Updated_Date)
VALUES
(@IID, @TrackingID, @SeqRunNo, @ADP_Code, @ClientID, @LastUpdate)

END
ELSE
BEGIN
SELECT @SeqRunNo = @Seq_No
END

GO

View 2 Replies View Related

Return A Value From Stored Procedure

Mar 27, 2002

How do I get then return value from an insert stored procedure?

for example:


CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS

INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)

SELECT @Iden=@@Identity
GO


Thanks

View 1 Replies View Related

Return A Value From Stored Procedure

Mar 27, 2002

How do I get then return value from an insert stored procedure?

for example:


CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS

INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)

SELECT @Iden=@@Identity
GO


Thanks

View 2 Replies View Related

Return Value In A Stored Procedure

Jul 20, 2005

Hello Newsgroup !My Tools are:Windows 2000, VBA(Access 2000) and MS SQL Server 7.0I wrote in an *.adp project (Access 2000) a Stored Procedure "xyz"with parameters a,bIn my VBA Code i wrote:Dim par As New ADODB.ParameterCmd.CommandType = adCmdStoredProcCmd.CommandText = "[prcSucheUNRWIAEStichtag]"Set par = Cmd.CreateParameter("@a", adInteger)Cmd.Parameters.Append parSet par = Cmd.CreateParameter("@b", adVarChar, adParamInput, 5)Cmd.Parameters.Append par........Cmd.Parameters(0) = aCmd.Parameters(1) = bSet rsTemp = Cmd.ExecuteNow my Problem is the following:there is an error in the stored procedure and i want to use something likethis:if @idontknow = '000000000'Beginreturn(1) -- Something <>0endHow can i use this return value in my VBA code ? Maybe i should ask thisquestion in an other Newsgroup. Pleaselet me know in which oneGreetingsFrank

View 2 Replies View Related

Get Return Value From Stored Procedure

Apr 22, 2008

In one of my SSIS jobs I have an 'Execute SQL Task' which is calling a stored procedure. This SP returns 0 or 1, if the SP returns 1 the package needs to stop executing. Is there a way to get the value that is returned from the SP and either continue or stop the job from continuing to run?

so if value is 0 it goes to the next step, if its 1 it stops running

View 8 Replies View Related

Get Return Value From Stored Procedure

Mar 14, 2007

Greetings All,
I am a newbie to SSIS and need some help. I need to get the return value from a stored procedure into a SSIS variable. I'm assuming I would use an OLE DB Command but I havn't a clue on how to capture the return value. Can someone get me started on how I can do this?

Note, the return value is actually an identity of the inserted value. I need this value in my data flow for further processing.

Thanks is advance!

View 6 Replies View Related







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