Stored Procedure - Return Count

Jun 16, 2005

Having a little trouble with this sp... just need to return the count.....CREATE  PROCEDURE dbo.getTotalObjectives @courseId   VARCHAR(20) ASBEGIN DECLARE @errCode     INT

  SELECT count(*)  FROM   objstructure  WHERE  courseId  = @courseId

 SET @errCode = 0  RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation  FROM   cmiDataModel  WHERE  studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath

View 4 Replies


ADVERTISEMENT

Calling A Stored Procedure Which Uses A Return @count

Aug 21, 2007

Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:-----------  ALTER PROCEDURE dbo.CountTEstAS   SET NOCOUNT ON    DECLARE @sql nvarchar(100);    DECLARE @recCount int;      DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*)            FROM Pool 'SET @parms = '@recCount1 int OUTPUT'             exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4  My code that calls this stored proc is:         Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True")        connect.Open()        Dim cmd As New SqlCommand("CountTEst", connect)        cmd.CommandType = CommandType.StoredProcedure        Dim MyCount As Int32        MyCount = cmd.ExecuteScalar        connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and  then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks, 

View 5 Replies View Related

SQL Server 2012 :: Can't Get Row Count To Return To Calling Stored Procedure

Jul 9, 2014

SQL Server 2012 Standard SP 1.

Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:

declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;

Print statement prints @NumBufferManagerRows as 0.

Here is the called stored procedure B:

CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT

[Code] ...

View 2 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

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

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

Sql Count Using Stored Procedure Withing Stored Procedure

Apr 29, 2008

I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks

View 10 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

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

Using Count(*) In A Stored Procedure

Jul 6, 2007

Hi,
I would like to add custom paging to my ASP.Net pages. How can I use Count(*) in a query such as below to retrieve a record count? 
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO
ALTER PROCEDURE [dbo].[view_icon_photos]    @Filenumber int = '0' , @Location nvarchar(50)= '-1' , @Rubric nvarchar(MAX)= '-1' , @Author nvarchar(75)= '-1' , @startRowIndex int= '-1' , @maximumRows int= '0'
As
IF @Filenumber = '0' AND @location > '-1' AND @Rubric = '-1' AND @Author = '-1' AND @startRowIndex > '-1' AND @maximumRows >'0'Select Icon, Filenumber, RowRankFrom(Select Icon, Filenumber,  Row_Number () Over ( Order By Filenumber ASC ) AS RowRankFrom dbo.photosWhere Location = @Location)As IconRank Where RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)
 

View 2 Replies View Related

How Can I Use COUNT In My Stored Procedure?

Jul 29, 2007

hello,
i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums
How can i use the COUNT function in my stored procedure to take only first n Categories?
I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...
here is my stored procedure (i use it for two nested Repeaters)ALTER PROCEDURE SubCategoriiInCategorii
CategoryID int)
AS
SELECT CategoryID, Name, Description FROM Categories WHERE CategoryID = @CategoryIDORDER BY Name
 
SELECT p.SubCategoryID, p.Name,p.CategoryID FROM SubCategorii p
ORDER BY p.Name
i hope you understand what i mean, thank you

View 4 Replies View Related

Using Two COUNT(*) In A Stored Procedure

Apr 19, 2005

what's wrong with the following code? COUNT for completed and exempted doesn't return correct values. they return same values. but when i remove one count it's returning right COUNT. i need to return the COUNT from two tables with a single query. how can i accomplish that??? is there anything wrong with my code below?
<code>
CREATE PROCEDURE dbo.Test AsSELECT s.StudentIdNum,  c.[Name], COUNT(*) AS Completed, COUNT(*) AS exempted FROM StudentEnrolled AS s JOIN StudentCourse AS sc ON s.StudentKey = sc.StudentKey JOIN Courses AS c ON sc.CourseID = c.CourseIDLEFT JOIN ModuleEnrolment AS m ON sc.StudentCourseID = m.StudentCourseIDLEFT JOIN ModuleExemption AS e ON sc.StudentCourseID = e.StudentCourseIDWHERE m.Status = "Completed"GROUP BY s.StudentIdNum, c.[Name]
</code>

View 5 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

Return Single Value From Stored Procedure

Jul 10, 2006

   I am have stored procedure that accepts one parameter and returns a single value
The sp works fine.
My question is how do I set the c# webpage to return the single value.
I need a specific example of how to code.
this is how the code should work.
A blank form is loaded, when the user clicks submit I need the code to pull the Login name from the text box and check to see if it exist. I have the sp working the way I need it to. But I can figure how to pull the single value into a variable in the code behind.
I am using dataset elsewhere on the site, so I created a ds the calls the sp. Can it work this way?
thanks,
 

View 3 Replies View Related

Catching A Return From SQL Stored Procedure

Sep 6, 2006

Hi AllHere is my SPALTER PROCEDURE dbo.InsertPagerDays @ReportEndDate datetime, @PagerDays int,@UserID varchar(25)ASIF EXISTS(-- you cannot add a pager days more than once per report dateSELECT ReportEndDate, UserId from ReportPagerDays where ReportEndDate = @ReportEndDate and UserId = @UserID)Return 1 elseSET NOCOUNT OFF;INSERT INTO [ReportPagerDays] ([ReportEndDate], [PagerDays], [UserID]) VALUES (@ReportEndDate, @PagerDays, @UserID)RETURNMy Question is, this SP will not let you enter in a value more than once (which is what i want) but how do I write my code to inform the user? Here is my VB code becuase the SP does not error out (becuase it works it acts as if the record updates)How can I catch the Return 1'set parameters for SPDim cmdcommand = New SqlCommand("InsertPagerDays", conn)cmdcommand.commandtype = CommandType.StoredProcedurecmdcommand.parameters.add("@ReportEndDate", rpEndDate)cmdcommand.parameters.add("@PagerDays", PagerDays)cmdcommand.parameters.add("@UserId", strUserName)Try'open connection hereconn.Open()'Execute stored proccmdcommand.ExecuteNonQuery()Catch ex As Exceptionerrstr = ""'An exception occured during processing.'Print message to log file.errstr = "Exception: " & ex.Messagelblstatus.ForeColor = Drawing.Color.Redlblstatus.Text = "Exception: " & ex.Message'MsgBox(errstr, MsgBoxStyle.Information, "Set User Report Dates")FinallyIf errstr = "" Thenlblstatus.ForeColor = Drawing.Color.Whitelblstatus.Text = "Pager Days Successfully Added!"End If'close the connection immediatelyconn.Close()End Try

View 1 Replies View Related

Stored Procedure - Return DataSet

Nov 2, 2006

I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset.  What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath

View 5 Replies View Related

Calculate And Return % In Stored Procedure

Apr 22, 2007

This is my SP:SELECT CAST(id AS varchar(10)) + ' - ' + UserName AS 'User List', Scanned, Scripts AS 'Total Scripts', Processed,CAST((Processed/ Scripts)* 100.0 as int) AS 'DONE (%)'FROM tblworkQueueProcessed and Scripts are both DataTypes of intMy DONE(%) column comes back with 0Any idea where I am going wrong? 

View 2 Replies View Related

Return Stored Procedure Parameter

Apr 27, 2007

I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e)
{
/*variables need for the update of the old staff record
and the creation of the new staff record.
*/
BMSUser bmsUser = (BMSUser)Session["bmsUser"];
int staffid = Convert.ToInt32(bmsUser.CmsStaffID);
int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]);
string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text);
int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);


SqlConnection conn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_BMS_MoveStaffMember";
cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int);
cmd.Parameters["@OldStaffProfileID"].Precision = 9;
cmd.Parameters["@OldStaffProfileID"].Scale = 0;
cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID;
cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int);
cmd.Parameters["@NewManagerProfileID"].Precision = 9;
cmd.Parameters["@NewManagerProfileID"].Scale = 0;
cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int);
cmd.Parameters["@LastModifiedBy"].Precision = 9;
cmd.Parameters["@LastModifiedBy"].Scale = 0;
cmd.Parameters["@LastModifiedBy"].Value = staffid;

SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);

lbl_ERROR.Text = Convert.ToString(sp);

conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
conn.Close();
 My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null.
 Here is the parts of my stored proc returning the value...
DECLARE @OLDManagerReturn numeric(9)
SELECT @OLDManagerReturn =  ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear
RETURN @OLDManagerReturn

View 5 Replies View Related

Get Return Value From Stored Procedure And Trigger

Oct 2, 2007

Hello there,
I searched for answers to the above topic, but could not find what I want. My stored procedures and triggers are returning a message based on the result, mostly error messages. How can I get that message using ASP.Net? Should I use an output parameter?
 Thank you for your help.

View 2 Replies View Related

How To Return A Value From Stored Procedure When Select Nothing

Oct 8, 2007

Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me.... Thank

View 4 Replies View Related







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