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 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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 ...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
|
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...
| |