Return BIGINT OUTPUT Param To VB!?

May 9, 2006

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function


PLEASE HELP.

Regards,

Sander

View 1 Replies


ADVERTISEMENT

SP Syntax Help - Return New Identity Value As Output Param

Feb 4, 1999

I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?

My current sp looks like this:

CREATE PROCEDURE sp_SaveNewLabel

-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)

AS

INSERT INTO tbLabel
(LabelType
, Logo
, Field01)

VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)


How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...

Zack

View 2 Replies View Related

.NET Framework :: Stored Procedure Can't Return Output Param Value

Oct 8, 2015

My stored procedure was running sqlserver 2012 but can't return output parameter value here i paste my SP ...

alter Procedure BD_InsertMobileClient_GpsLogPlanogram
(
@SalesPersonID int,
@MobileRowID int,
@StatusCode int output
)

[Code] .....

View 3 Replies View Related

@@RowCount Output Param

Sep 6, 2007

The following stored procedure sets a value for the @@RowCount global variable.
How do I make use of it in the Data Access Layer?
When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @@RowCount to a Label.Text
What should I do?ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
@selectDate datetime
,@selectCountry Int
AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_cmc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry
ORDER BY configname
SELECT @@RowCount As numberRows

GO 

View 2 Replies View Related

Output Param With A Stored Procedure

Aug 21, 2001

Hello everyone,

I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.

I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.

create procedure ShowPopStyle
@style varchar(30),
@artistname varchar(30) output
as
select @artistname = artistname
from artists
where style = @style
go

Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?

declare @returnname varchar(30) -- variable for the output from the procedure
exec showpopstyle 'Pop', @artistname = @returnname output
print @returnname

Thanks
Kevin

View 2 Replies View Related

Consuming Stored Procedure Output Param

Sep 14, 2007

This is my SProc:
CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
/* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT
AS
SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount
GO
And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue
Dim con As New SqlClient.SqlConnection
con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int)
myParam.Direction = ParameterDirection.Output
myCommand.Parameters.Add(myParam)
myCommand.Connection = con
con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected
numberParts.Text = rowCount.ToString
con.Close()
End Sub
 
What should I fix?

View 5 Replies View Related

Issues With An Output Param From A Sproc Using SQLDataSource

Nov 28, 2007

I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently:
ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT
AS
SET NOCOUNT ON ;
select @vcOutputMsg = 'asdf'
 
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
//handle error on return
string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value;
}
 
On the page source side, the params are defined declaratively:
<UpdateParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" />
</UpdateParameters>
 
 
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"
PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?

View 3 Replies View Related

Output Param In Oledb Transformation That Calls An Sp

Feb 17, 2008

is it true that I will not be able to use the returned value from an sp that is called on every row from an oledb command transformation? I see all kinds of complaints on the web but cant determine if this would be a waste of time. I'd like to append the returned value (which is calculated and cannot be joined in the buffer) to the data on its way out of the transformation.

View 3 Replies View Related

Casting Float Output Param Throws An Exception.

Jan 9, 2004

I keep getting an exception when trying to cast an output param as a float type. the SPROC is marked as float OUTPUT and the Cost column in the database is a float type as well. And there are no nulls in the cloumn either. here is how my code looks:


SqlParameter prmCost= new SqlParameter("@Cost", SqlDbType.Float,8);
prmCost.Direction=ParameterDirection.Output;
cmd.Parameters.Add(prmCost);

//...blah blah blah

//invalid cast gets throw on here (it happens with all my float types)
productDetails.Cost=(float)prmCost.Value;




Any suggestions as to what I am doing wrong?

View 3 Replies View Related

Output Param && Multiple Recordests From Stored Procedures

Jul 20, 2005

here's my code:my $sth = $dbhSQL->prepare('{call proc(?,?,?)}');$sth->bind_param(1,"asd");$sth->bind_param(2,"klm");$sth->bind_param_inout(3,$no_go, 1000);$sth->execute;print "no go = $no_go";while(my @row=$sth->fetchrow_array){print "@row";}$sth->finish;Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@iyt varchar(20),@no_go int OUTPUTASSET NOCOUNT ONDECLARE @id_err int,@ans_glue_err intBEGIN TRANSACTIONSELECT user_id FROM myTableWHERE user_id=@id AND iyt=@iytSET @id_err = @@ERRORIF @@ROWCOUNT <> 0BEGINSELECT date,date_mod FROM ans_glueWHERE user_id=@idSET @no_go = 0SET @ans_glue_err=@@ERRORENDELSEBEGINSET @no_go = 1ENDIF @id_err = 0 AND @ans_glue_err = 0BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONENDthe procedure runs perfectly in Cold Fusion, returning both recordsetsand output param, but in perl, it won't print the output param and Idon't know how to access the second recordsetHELP!

View 1 Replies View Related

Runtime Build Sql In Stored Procedures With Output Param Q?

Jul 20, 2005

HiI'm trying to make this to work and need helpHere my SP and I'm building sql with output param.Alter PROCEDURE lpsadmin_getSBWReorderDollars(@out decimal(10,2) output,@sType varchar(20),@dSearchDateFrom datetime,@dSearchDateTo datetime,@sOrderType char(1))ASDECLARE @sql as nvarchar(4000)SELECT @sql = 'SELECT @out = SUM(Price*Quantity)FROM PortraitReOrderOrder jcpreINNER JOIN Orders jcporON OrderID = OrderIDWHERE jcpor.Archive = 0AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),@dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),@dSearchDateTo, 101) + ''''IF @sOrderType <> 0SELECT @sql = @sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeNameFROM OrderTypes WHERE OrderTypeID = ' + @sOrderType + ')'IF @sType = 'Active'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 'IF @sType = 'Shared'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 'Print @sqlEXECUTE sp_executesql @sqlIt gives me an error messageMust declare the variable '@out'.Please help

View 2 Replies View Related

Output Param Of Stored Proc - Oledb Command

Jun 5, 2007

Hello

This is my first post so please be gentle!!

I have a stored proc that returns a value:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON

GO
-- =============================================
ALTER PROCEDURE [dbo].[spPK]
-- Add the parameters for the stored procedure here
@varNSC varchar(4),
@varNC varchar(2),
@varIIN varchar(7),
@varIMCDMC varchar(8),
@varOut as int output
AS
Declare
@varPK int
set @varPK = 0
BEGIN

--This checks Method 1
--NSC = @varNSC
--NC = @varNC
--IIN = @varIIN
begin
if exists
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varPK =
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varOut = @varPK
if @varPK <> 0 Return
end

[There are some more methods here]

Return

END



How do I get at the output value?

I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.

View 10 Replies View Related

How Do I Access The Value Of A Stored Proc Return Param In C# Using ExecuteNonQuery?

Jun 8, 2007

I've got a stored proc to insert a record and return the id of the record inserted in an output param.How do I access this value in my code after the proc is executed?
param = comm.CreateParameter();param.ParameterName = "@MemberID";param.Direction = ParameterDirection.Output;param.DbType = DbType.Int32;comm.Parameters.Add(param);
try{     rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);}catch {     rowsAffected = -1;}
 

View 1 Replies View Related

Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output &&amp; 2 Input Parameters In Param. Coll.?

Feb 23, 2008

Hi all,

In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":

CREATE PROC [dbo].[spInvTotal3]

@InvTotal money OUTPUT,

@DateVar smalldatetime = NULL,

@VendorVar varchar(40) = '%'



This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub printMyInvTotal()

Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"

Dim conn As SqlConnection = New SqlConnection(connectionString)

Try

conn.Open()

Dim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[dbo].[spInvTotal3]"

Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)

param.Direction = ParameterDirection.Output

cmd.Parameters.Add(param)

cmd.ExecuteNonQuery()

'Print out the InvTotal in TextBox1

TextBox1.Text = param.Value

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

conn.Close()

End Try

End Sub

End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?

Please help and advise.

Thanks in advance,
Scott Chang



View 7 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?

I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).

Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View 5 Replies View Related

Error - Output Param - SP - Size Property Has An Invalid Size Of 0

Nov 14, 2007

Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR.  I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0.   par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar);  par.Direction = ParameterDirection.Output;  cmd.Parameters.Add(par);  cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas 

View 6 Replies View Related

Output Parameter Not Return The Value

Nov 7, 2007

 In my SP I have an output parameter,
 
the SP work good,
but in the command parameter I alwase get null value/
here is my code:
 
and in the profiler I see that the query work and return the value, here the query that I copy from the profiler:
 thanks! 

View 3 Replies View Related

Return Output Parm

Oct 15, 2004

The stored Procedure I am using

CREATE PROCEDURE GetUserID
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserID int OUTPUT
)
AS
SET @UserID = (SELECT fldResourceID
FROM tblResources
WHERE fldEmail = @Email AND fldPassword = @Password)

GO

The Code Behind to call it.

Dim intUserID As Integer = 0
Dim strConnectionString As String = dbConnect()
Dim objConnection As SqlConnection = New SqlConnection(strConnectionString)
objConnection.Open()

Dim objCmd As SqlCommand = New SqlCommand("EXEC GetUserID '" & strEmail & "','" & strPassword & "', " & intUserID, objConnection)

intUserID = objCmd.ExecuteScalar()

objCmd = Nothing
objConnection = Nothing

Return intUserID

However nothing is returned from the objCmd.ExecuteScalar line. Can anyone help me out here. All I need it to return is a single value. Thanks

View 1 Replies View Related

Output Parameter Or Return Value

May 2, 2005

I'm having a terrible time getting a return value from my stored procedure. The sp sets the value  and returns it correctly in the query analyzer, but I can't get the calling code - using VB in VS2003 - to read the value.  I've tried putting it into an integer, and using a Return, and also putting it into a string, and using an output parameter.  Either way:  nada.  What am I doing wrong????
Here's the sp:
CREATE PROCEDURE sp_HOUSRoomAudits @Result VarChar(1) OUTPUT, @Dorm VarChar(12), @Room VarChar(5), @StuID VarChar(14)  AS
DECLARE @Capacity IntDECLARE @Assigned Int--DECLARE @Result VarChar(1)DECLARE @BD BIT
SET @BD=(SELECT Boarding FROM HOUS_StudentMaster WHERE StudentID=@StuID)
SET @Capacity=(SELECT [Student Capacity] FROM HOUS_Rooms WHERE Bldg=@Dorm AND [Room #]=@Room)
SET @Assigned=(SELECT COUNT(PSID) FROM HOUS_StudentMaster a JOIN HOUS_Dorms b ON a.Dormatory=b.Dormitory  WHERE b.Bldg=@Dorm AND a.Status='Active'  AND a.Room=@Room)
IF (@BD=1 AND @Room='N/A') OR (@BD=0 AND @Room<>'N/A')
 SET @Result='3'ELSE IF @Assigned<@Capacity   SET @Result='1' ELSE    SET @Result='2'
print @Result--RETURN @ResultGO
Here's the calling code; I'm leaving out all the dim's etc down to the output parameter:
Dim Parm4 As SqlParameter = scmdReturn.Parameters.Add("@Result", SqlDbType.VarChar)
Parm4.Direction = ParameterDirection.Output
Parm4.Value = "0"
Try
scmdReturn.ExecuteNonQuery()
strOut = Parm4.Value
Catch ex As Exception
Me.lblProblem.Text = "Failed to execute command, err = " & ex.Message
Me.lblProblem.Visible = True
Finally
scnnReturn.Close()
End Try
Hoping you can help, and thanks!
Ka
 

View 11 Replies View Related

Return Output Values From View

Jan 2, 2006

How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?

View 2 Replies View Related

Return 4 Output From A Function In Sqlserver

Jun 16, 2008

Hi I need a function which gets 3 parameters and input and returns 4 integers as output.
I don't know how to return 4 integers as output
any idea is appreciated.

View 7 Replies View Related

Transact SQL :: Return Value 1 For Output Parameter

Oct 7, 2015

I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.

Here is my code:
  
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)

[Code] ....

View 4 Replies View Related

How To Get Store Procedure's Return Value And Output Parameters From BLL?

Oct 10, 2007

ex:
myprocedure(@Cusname varchar(50), @Cusid int output)as
Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY
 i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter
{
get
{if (_CuslistAdapter == null)
_CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter;
}
}
Now how i write function in BLL to receive output paramter from creatcustomer function?

View 1 Replies View Related

Return Output Parameter From Stored Procedure?

Feb 6, 2008

I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable?
Below is the source code that I have:1 ' Insert question_text into Question table
2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 Dim QuestionID As Integer
5
6 ' Set connection to DB
7 cmd.Connection = conn
8
9 cmd.CommandText = "usp_QuestionResponse_ins"
10 cmd.CommandType = Data.CommandType.StoredProcedure
11
12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output
13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID))
14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID))
15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn))
16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp))
17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn))
18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder))
19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3))
20 QuestionID = sp1.Value
21 cmd.Connection.Open()
22 cmd.ExecuteNonQuery()
23
24 conn.Close()
25 cmd.Dispose()
26

Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins
2 (
3 @question_id int = null output,
4 @assessment_id int = null,
5 @domain_id int = null,
6 @question_text_en nvarchar(1000) = null,
7 @question_text_sp nvarchar(1000) = null,
8 @category_en nvarchar(500) = null,
9 @display_order smallint = null,
10 @question_template_id int = null
11 )
12 As
13 BEGIN
14 DECLARE @Err Int
15
16 DECLARE @QuestionID INT
17 DECLARE @ReturnValue INT
18 SET @ReturnValue = 0
19
20 DECLARE @CategoryId int
21 SELECT @CategoryId = NULL
22
23 -- Check to see if an answer has already been inserted (i.e. revisiting)
24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0)
25 IF ( @CategoryId IS NOT NULL )
26 BEGIN
27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0
28 SET @QuestionID = @ReturnValue
29 END
30
31 IF ( @QuestionID IS NOT NULL )
32 BEGIN
33 DECLARE @question_locale_id int
34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0
35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0
36
37 END
38
39 RETURN @QuestionID
40 End

What am I doing wrong?  How do I properly capture the QuestionID from the SP?  Running the SP from command line works fine.
Thanks

View 2 Replies View Related

Stored Procedure With Parameters OUTPUT And RETURN VALUE

Oct 26, 2000

Hi

How I building a stored procedure that
return a output parameter and value return that I can
call from other stored procedure or VB ?

thank you in advance

View 1 Replies View Related

Return Resultset As Output Parameter To VB Application

Nov 27, 2014

I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.

I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:

Code:
while (@I <= @count)
begin
BEGIN TRY
-- delete all previous rows inserted in @customerRow for previous counts @I
delete from @customerRow
-- this is inserting the current row that we want to save in database
insert into @customerRow

[Code] .....

This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:

Code:
begin catch
IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE();

[Code] ....

Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.

View 3 Replies View Related

Using SSIS To Output SP Return Data To A Share

May 4, 2008



I'm new to SSIS. I'm looking for basic pointers to return temp table created by a proc to be written to file shares. Any pointers/examples appreciated.

Thanks,
Siva

View 5 Replies View Related

I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar

Mar 21, 2008

My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ")        Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection)        cmdUpdate.CommandType = Data.CommandType.StoredProcedure        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int))        cmdUpdate.Parameters("@QuestionID").Value = QuestionID               cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output        'open connection        dbConnection.Open()        'Execute non query        cmdUpdate.ExecuteNonQuery()        'close connection        dbConnection.Close() 

View 8 Replies View Related

Can I Return Output From Stored Procedure Into An HTA To Display In Browser?

Jul 20, 2005

Hi all. I have a stored procedure on my sql server that returns asimple informtation about that tim a database was backed up. I owuldlike to create an HTA that operator types can look at to make surethat the backups finished, can someone help me do this?The stored procedure in called sp_lastback and the output looks likethis:database days since backup timestamp of backupdbase1 0 2004-10-14 00:41:21.000dbase2 0 2004-10-14 00:08:36.000dbase3 1 2004-10-13 22:46:57.000Can this be done? If someone could help me with this simple problem, Icould probably reuse the same method a 100 useful ways.Thanks in advance!

View 3 Replies View Related

SqlCommand Return And Output Parameters Not Working, But Input Does?

Feb 18, 2006

The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?

System::Boolean rs::sp(System::String ^ ssp){

System::String ^ k0s0; bool bOK;

System::Data::SqlClient::SqlParameter ^ parami0;

System::Data::SqlClient::SqlParameter ^ parami1;

System::Data::SqlClient::SqlParameter ^ parami2;

System::Data::SqlClient::SqlParameter ^ paramz0;

System::Data::SqlClient::SqlParameter ^ paramz1;

System::Int32 pz0=0;System::Int32 pz1=0;

k0s = ssp;

k0->CommandType=System::Data::CommandType::StoredProcedure;

k0->CommandText=k0s;

paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);

//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);

//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);

paramz0->Direction=System::Data::ParameterDirection::ReturnValue;

paramz0->DbType=System::Data::DbType::Int32;

parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");

parami0->Direction=System::Data::ParameterDirection::Input;

parami1=k0->Parameters->AddWithValue("@SRCXV","UU");

parami1->Direction=System::Data::ParameterDirection::Input;

//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);

paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);

paramz1->Direction=System::Data::ParameterDirection::InputOutput;

paramz0->DbType=System::Data::DbType::Int32;

//k0->Parameters->GetParameter("@RCOUNT");

r0=k0->ExecuteReader();

//pz0=System::Convert::ToInt32(paramz0->SqlValue);

bOK=k0->Parameters->Contains("@RCOUNT");

//k0->Parameters->GetParameter("@RCOUNT");

pz0=System::Convert::ToInt32(paramz0->Value);

pz1=System::Convert::ToInt32(paramz1->Value);

ndx = -1;

while(r0->Read()){

if (ndx == -1){

ndx=0;

pai0ndx=0;

pad0ndx=0;

r0nf=r0->FieldCount::get();

for (iG1_20=0;iG1_20<r0nf;iG1_20++){

this->psf0[iG1_20]=this->r0->GetName(iG1_20);

this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);

this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}

}



}

else {

pai0ndx=0;

pad0ndx=0;

for (iG1_20=0;iG1_20<r0nf;iG1_20++)

{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}

}

}

ndx++;

}

r0nr=ndx;

r0->Close();

k0->Parameters->Remove(paramz0);

k0->Parameters->Remove(parami1);

k0->Parameters->Remove(parami0);

k0->Parameters->Remove(paramz1);

return true;

}

View 5 Replies View Related

How To Display Return Value From Stored Procedure Output Parameter In Query Analyzer

Jul 20, 2004

I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:

CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS

SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO

If I run the query in Query Analyzer directly, I can display @UserName:

DECLARE @UserName as nvarchar(100)

SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password

Select @UserName

But how can I display @UserName if I call stored procedure:

exec UserLogin 'email', 'password', ''

I believed it return int, right?

Thanks in advance for help.

View 2 Replies View Related

To Bigint, Or Not To Bigint?

May 27, 2008

 Hey, quick question here. In my database for my webapp (The one I showed you guys in my previous post), in my stickies table, I am using a column called StickyId for the PK, and it is of type int. My concern is that eventually, I may get to a point with more then 2 million records in the stickies field. I am looking at using the bigint datatype for StickyId instead, but it takes up twice the memory. So I have two questions:1. Can I declare the StickyId column as type int for now, and if my DB ever starts approaching the 2 million mark, access my DB and change it over to a bigint type? Aka, will MS SQL convert the exisiting int values into a bigint and be transparent to the app and the users?2. Does the bigint always use 8 bytes to store its value? Aka does it pad, so that if I stored the value of '1' would that take the same space as if I stored the value '43563636'? Or is it more like varchar, only taking up the space that that particular value needs?Thanks in advance guys!

View 3 Replies View Related

Bigint

Mar 18, 2008

Hi there! My question is: I use a bigint data type as a primary key in my database, so
1. Can I define bigint autoincrement as unsigned?
2. In case of possible, what type should I use in C# code that equal to bigint unsigned - ulong?
3. In case of impossible, what type should I use in C# code - long?
Thanks

View 7 Replies View Related







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