473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

output parameters

Hi

I've got this function that executes a stored procedure. One of the
parameters is an output parameter but for some reason it always returns 0.

Any clues?

Cheers
James
Public Shared Sub StoredProcExecuteNonQuery(ByVal strSP As String, ByVal
ParamArray aryParams() As SqlParameter)

'connection obj

Dim objConn As SqlConnection

objConn = DBConnect()

'create the command obj

Dim objCMD As New SqlCommand

objCMD.CommandText = strSP

objCMD.Connection = objConn

objCMD.CommandTimeout = 1440

objCMD.CommandType = CommandType.StoredProcedure

'parameters

Dim Ct As Integer

For Ct = 0 To UBound(aryParams)

If aryParams(Ct).ParameterName <> "" Then

Dim cmdParam As New SqlParameter

cmdParam.ParameterName = aryParams(Ct).ParameterName

cmdParam.SqlDbType = aryParams(Ct).SqlDbType

cmdParam.Direction = aryParams(Ct).Direction

cmdParam.Value = aryParams(Ct).Value

objCMD.Parameters.Add(cmdParam)

End If

Next

'create and exec the data reader

Try

objCMD.ExecuteNonQuery()

objConn.Close()

Catch ex As Exception

End Try

End Sub

I call this by

Dim sqlDatasetCode As New SqlParameter("@code", sCode)

Dim sqlDatasetID As New SqlParameter

sqlDatasetID.ParameterName = "@dataset"

sqlDatasetID.SqlDbType = SqlDbType.Int

sqlDatasetID.Size = 4

sqlDatasetID.Direction = ParameterDirection.Output

Database.StoredProcExecuteNonQuery("lookup_dataset _by_code", sqlDatasetCode,
sqlDatasetID)

If IsDBNull(sqlDatasetID.Value) = False Then

Return sqlDatasetID.Value

Else

Return 0

End If
Nov 18 '05 #1
3 1341
Can you post the stored procedure too?

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

"James Brett" <ja*********@unified.co.uk> wrote in message
news:u$**************@tk2msftngp13.phx.gbl...
Hi

I've got this function that executes a stored procedure. One of the
parameters is an output parameter but for some reason it always returns 0.

Any clues?

Cheers
James
Public Shared Sub StoredProcExecuteNonQuery(ByVal strSP As String, ByVal
ParamArray aryParams() As SqlParameter)

'connection obj

Dim objConn As SqlConnection

objConn = DBConnect()

'create the command obj

Dim objCMD As New SqlCommand

objCMD.CommandText = strSP

objCMD.Connection = objConn

objCMD.CommandTimeout = 1440

objCMD.CommandType = CommandType.StoredProcedure

'parameters

Dim Ct As Integer

For Ct = 0 To UBound(aryParams)

If aryParams(Ct).ParameterName <> "" Then

Dim cmdParam As New SqlParameter

cmdParam.ParameterName = aryParams(Ct).ParameterName

cmdParam.SqlDbType = aryParams(Ct).SqlDbType

cmdParam.Direction = aryParams(Ct).Direction

cmdParam.Value = aryParams(Ct).Value

objCMD.Parameters.Add(cmdParam)

End If

Next

'create and exec the data reader

Try

objCMD.ExecuteNonQuery()

objConn.Close()

Catch ex As Exception

End Try

End Sub

I call this by

Dim sqlDatasetCode As New SqlParameter("@code", sCode)

Dim sqlDatasetID As New SqlParameter

sqlDatasetID.ParameterName = "@dataset"

sqlDatasetID.SqlDbType = SqlDbType.Int

sqlDatasetID.Size = 4

sqlDatasetID.Direction = ParameterDirection.Output

Database.StoredProcExecuteNonQuery("lookup_dataset _by_code", sqlDatasetCode, sqlDatasetID)

If IsDBNull(sqlDatasetID.Value) = False Then

Return sqlDatasetID.Value

Else

Return 0

End If

Nov 18 '05 #2
CREATE PROCEDURE [lookup_dataset_by_code]
@code VARCHAR(16),
@dataset INT OUTPUT
AS

SELECT @dataset = p_dataset
FROM dataset
WHERE code = @code
GO

cheers j

"Ben Lucas" <be*@nospam.solien.nospam.com> wrote in message
news:kI********************@comcast.com...
Can you post the stored procedure too?

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

"James Brett" <ja*********@unified.co.uk> wrote in message
news:u$**************@tk2msftngp13.phx.gbl...
Hi

I've got this function that executes a stored procedure. One of the
parameters is an output parameter but for some reason it always returns 0.
Any clues?

Cheers
James
Public Shared Sub StoredProcExecuteNonQuery(ByVal strSP As String, ByVal
ParamArray aryParams() As SqlParameter)

'connection obj

Dim objConn As SqlConnection

objConn = DBConnect()

'create the command obj

Dim objCMD As New SqlCommand

objCMD.CommandText = strSP

objCMD.Connection = objConn

objCMD.CommandTimeout = 1440

objCMD.CommandType = CommandType.StoredProcedure

'parameters

Dim Ct As Integer

For Ct = 0 To UBound(aryParams)

If aryParams(Ct).ParameterName <> "" Then

Dim cmdParam As New SqlParameter

cmdParam.ParameterName = aryParams(Ct).ParameterName

cmdParam.SqlDbType = aryParams(Ct).SqlDbType

cmdParam.Direction = aryParams(Ct).Direction

cmdParam.Value = aryParams(Ct).Value

objCMD.Parameters.Add(cmdParam)

End If

Next

'create and exec the data reader

Try

objCMD.ExecuteNonQuery()

objConn.Close()

Catch ex As Exception

End Try

End Sub

I call this by

Dim sqlDatasetCode As New SqlParameter("@code", sCode)

Dim sqlDatasetID As New SqlParameter

sqlDatasetID.ParameterName = "@dataset"

sqlDatasetID.SqlDbType = SqlDbType.Int

sqlDatasetID.Size = 4

sqlDatasetID.Direction = ParameterDirection.Output

Database.StoredProcExecuteNonQuery("lookup_dataset _by_code",

sqlDatasetCode,
sqlDatasetID)

If IsDBNull(sqlDatasetID.Value) = False Then

Return sqlDatasetID.Value

Else

Return 0

End If


Nov 18 '05 #3
In you procedure StoredProcExecuteNonQuery( ) you are making a copy of the
parameter that you sent in, and you pass the copy to the command object.
Thus, the parameter you are using will not have the data since it is not
used in the execution.

In order for that parameter to have the output value, you would have to use
that specific object. For example:
Public Shared Sub StoredProcExecuteNonQuery(ByVal strSP As String, ByVal ParamArray aryParams() As SqlParameter)

'connection obj

Dim objConn As SqlConnection

objConn = DBConnect()

'create the command obj

Dim objCMD As New SqlCommand

objCMD.CommandText = strSP

objCMD.Connection = objConn

objCMD.CommandTimeout = 1440

objCMD.CommandType = CommandType.StoredProcedure

'parameters

Dim Ct As Integer

For Ct = 0 To UBound(aryParams) //Note the change here objCMD.Parameters.Add(aryParams(Ct))

Next

'create and exec the data reader

Try

objCMD.ExecuteNonQuery()

objConn.Close()

Catch ex As Exception

End Try

End Sub

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com
"James Brett" <ja*********@unified.co.uk> wrote in message
news:Om**************@tk2msftngp13.phx.gbl...
CREATE PROCEDURE [lookup_dataset_by_code]
@code VARCHAR(16),
@dataset INT OUTPUT
AS

SELECT @dataset = p_dataset
FROM dataset
WHERE code = @code
GO

cheers j

"Ben Lucas" <be*@nospam.solien.nospam.com> wrote in message
news:kI********************@comcast.com...
Can you post the stored procedure too?

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

"James Brett" <ja*********@unified.co.uk> wrote in message
news:u$**************@tk2msftngp13.phx.gbl...
Hi

I've got this function that executes a stored procedure. One of the
parameters is an output parameter but for some reason it always
returns
0.
Any clues?

Cheers
James
Public Shared Sub StoredProcExecuteNonQuery(ByVal strSP As String,

ByVal ParamArray aryParams() As SqlParameter)

'connection obj

Dim objConn As SqlConnection

objConn = DBConnect()

'create the command obj

Dim objCMD As New SqlCommand

objCMD.CommandText = strSP

objCMD.Connection = objConn

objCMD.CommandTimeout = 1440

objCMD.CommandType = CommandType.StoredProcedure

'parameters

Dim Ct As Integer

For Ct = 0 To UBound(aryParams)

If aryParams(Ct).ParameterName <> "" Then

Dim cmdParam As New SqlParameter

cmdParam.ParameterName = aryParams(Ct).ParameterName

cmdParam.SqlDbType = aryParams(Ct).SqlDbType

cmdParam.Direction = aryParams(Ct).Direction

cmdParam.Value = aryParams(Ct).Value

objCMD.Parameters.Add(cmdParam)

End If

Next

'create and exec the data reader

Try

objCMD.ExecuteNonQuery()

objConn.Close()

Catch ex As Exception

End Try

End Sub

I call this by

Dim sqlDatasetCode As New SqlParameter("@code", sCode)

Dim sqlDatasetID As New SqlParameter

sqlDatasetID.ParameterName = "@dataset"

sqlDatasetID.SqlDbType = SqlDbType.Int

sqlDatasetID.Size = 4

sqlDatasetID.Direction = ParameterDirection.Output

Database.StoredProcExecuteNonQuery("lookup_dataset _by_code",

sqlDatasetCode,
sqlDatasetID)

If IsDBNull(sqlDatasetID.Value) = False Then

Return sqlDatasetID.Value

Else

Return 0

End If



Nov 18 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bari Allen | last post by:
I have a Stored procedure in SQL, that works, when tested in SQL, with one input & several output parameters, as follows: CREATE PROCEDURE myProcedure @MyID int , @First varchar(80) OUTPUT ,...
5
by: vivienne.netherwood | last post by:
I am developing an Access Project front end with a SQL server database. I have written a stored procedure that returns a record set and also a value via an output parameter. The procedure is as...
4
by: Janaka | last post by:
Hi this is kind of an ASP.NET/ADO.NET question. I've got a stored procedure on SQL Server that returns a results set. It also sets 3 output parameters in a seperate Select statement. When...
2
by: Bari Allen | last post by:
ASP Classic question: I have a Stored procedure in SQL, that works, when tested in SQL, with one input & several output parameters, as follows: CREATE PROCEDURE myProcedure @MyID int , @First...
3
by: juststarter | last post by:
Hello all, I am executing a stored procedure (on an SQL Server) using ODBC but i can't get the output parameter's value on the client. The stored proc has 3 parameters ,2 of them are input and 1...
1
by: Joe Van Meer | last post by:
Hi all, I have an app that currently runs through 3 seperate stored procedures each returning a count of records. What I would like to do is combine these calls into one call, however I am...
1
by: Garth Wells | last post by:
Using an example in the Jan 2006 release of the Enterprise Library, I came up with the code shown below to create a DAL method for returning several columns of a single row. I place the output...
6
by: c676228 | last post by:
Hi everyone, I wrote a store procedure that fetch one row data in the database based on the parameter value I entered. After I created the store procedure, the store procedure code looks like...
1
by: John Bailo | last post by:
This is a my solution to getting an Output parameter from a SqlDataSource. I have seen a few scant articles but none of them take it all the way to a solution. Hopefully this will help some...
2
by: gabosom | last post by:
Hi! I've been breaking my head trying to get the output variables from my Stored Procedure. This is my SP code CREATE PROCEDURE GetKitchenOrderDetail( @idService int, --outPut Variables ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.