Stored Proc with parameters

[Rob Meade]

Hi all,

I was wondering if anyone could give me a small example of running a stored
procedure with both output and input parameters via asp.net (vb).

I have tried a couple of things, one of which worked, but now I'm trying to
change my code so that its the 'proper' way of doing things..

My stored proc expects the following:

@ErrorCode int OUTPUT,
@CDSPatientID int OUTPUT,
@OrgID int,
@NHSNumber varchar(7),
@SQLRIID varchar(7),
@HospitalNumber varchar(17),
@HospitalType varchar(3)

and is called writePatientUBHT

The code I initially had was as follows:

' exec stored procedure to add patient details
strSQL = "DECLARE "
strSQL += "@ErrorCode int, @CDSPatientID int "
strSQL += "EXEC writePatientUBHT "
strSQL += "@ErrorCode OUTPUT, @CDSPatientID OUTPUT, "
strSQL += "'" & Session("OrgID") & "', "
strSQL += "'" & m_params.GetParameterByName("NHS Number").m_queryStringData
& "', "
strSQL += "'" & m_params.GetParameterByName("SQLRI ID").m_queryStringData &
"', "
strSQL += "'" & m_params.GetParameterByName("Hospital
Number").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("Hospital").m_queryStr ingData &
"' "
strSQL += "SELECT @ErrorCode AS ErrorCode, @CDSPatientID AS CDSPatientID"

I then have a datareader which reads and set two variables to equal the
values returned from @ErrorCode and @CDSPatientID

I have done a few sql statements where I now add parameters as the values
going in - but trying the same method for getting them out seems a bit more
complex...I was heading down this road when I ran into problems..

' declare variables
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objReader As SqlDataReader
Dim strSQL As String
Dim intCDSPatientID As Integer
Dim intErrorCode As Integer

' Create and open our database connection
objConnection = New
SqlConnection(ConfigurationSettings.GetConfig("app Settings")("connString"))
objConnection.Open()

objCommand = New SqlCommand
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = objConnection

strSQL += "EXEC writePatientUBHT "
strSQL += "'" & Session("OrgID") & "', "
strSQL += "'" & m_params.GetParameterByName("NHS Number").m_queryStringData
& "', "
strSQL += "'" & m_params.GetParameterByName("SQLRI ID").m_queryStringData &
"', "
strSQL += "'" & m_params.GetParameterByName("Hospital
Number").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("Hospital").m_queryStr ingData &
"' "

' testing the parameters stuff
Dim sqlParameter As SqlParameter
Dim sqlParameter2 As SqlParameter

sqlParameter = New SqlParameter
sqlParameter2 = New SqlParameter

sqlParameter = objCommand.Parameters.Add("@ErrorCode", SqlDbType.Int)
sqlParameter.Direction = ParameterDirection.Output

sqlParameter2 = objCommand.Parameters.Add("@CDSPatientID", SqlDbType.Int)
sqlParameter2.Direction = ParameterDirection.Output

' set our command object
objCommand.CommandText = strSQL

' execute
objReader = objCommand.ExecuteReader()

' Close reader
objReader.Close()

' try to extract parameters
intCDSPatientID = objCommand.Parameters("@CDSPatientID").Value()
intErrorCode = objCommand.Parameters("@ErrorCode").Value()

' then all the tidy up code...
I had assumed (in the example above) that I would now not need to specify
the 'OUTPUT' parameters immediately after the 'EXEC writePatientUBHT', so I
removed those, however, when I ran the code I got an error saying it could
find the stored procedure named 'EXEC writePatientUBHT 'value', 'value',
'value', 'value' - I put this down to the fact that I was now specifying
the commandType as a stored procedure and that somewhere I probably now need
to say storeProc.name = "writePatientUBHT" or something...which would
suggest that I then need to add all my other values as parameters as well
and specify their directions?

Could anyone please advise me on this, and if possible perhaps a small
example using my code (where appropriate) from the above...

Thanks in advance for any help

Regards

Rob

[Laiju Skaria]

Hello Rob...
I dont know whether this will suite u or not.. But there is something knows
as Microsoft. Application Blocks for data which u can download from the
microsoft website. It contains a file called sqlhelper.cs (available in vb
also). It has a lot of static methods which return different objects which
will suite ur requirement. try using that. It reduces the codes that u've to
write......

cheers,
Laiju
"Rob Meade" <ro**********@NOSPAMubht.swest.nhs.uk> wrote in message
news:OD**************@TK2MSFTNGP10.phx.gbl...

Hi all,

I was wondering if anyone could give me a small example of running a stored procedure with both output and input parameters via asp.net (vb).

I have tried a couple of things, one of which worked, but now I'm trying to change my code so that its the 'proper' way of doing things..

My stored proc expects the following:

@ErrorCode int OUTPUT,
@CDSPatientID int OUTPUT,
@OrgID int,
@NHSNumber varchar(7),
@SQLRIID varchar(7),
@HospitalNumber varchar(17),
@HospitalType varchar(3)

and is called writePatientUBHT

The code I initially had was as follows:

' exec stored procedure to add patient details
strSQL = "DECLARE "
strSQL += "@ErrorCode int, @CDSPatientID int "
strSQL += "EXEC writePatientUBHT "
strSQL += "@ErrorCode OUTPUT, @CDSPatientID OUTPUT, "
strSQL += "'" & Session("OrgID") & "', "
strSQL += "'" & m_params.GetParameterByName("NHS Number").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("SQLRI ID").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("Hospital
Number").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("Hospital").m_queryStr ingData & "' "
strSQL += "SELECT @ErrorCode AS ErrorCode, @CDSPatientID AS CDSPatientID"

I then have a datareader which reads and set two variables to equal the
values returned from @ErrorCode and @CDSPatientID

I have done a few sql statements where I now add parameters as the values
going in - but trying the same method for getting them out seems a bit more complex...I was heading down this road when I ran into problems..

' declare variables
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objReader As SqlDataReader
Dim strSQL As String
Dim intCDSPatientID As Integer
Dim intErrorCode As Integer

' Create and open our database connection
objConnection = New
SqlConnection(ConfigurationSettings.GetConfig("app Settings")("connString")) objConnection.Open()

objCommand = New SqlCommand
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = objConnection

strSQL += "EXEC writePatientUBHT "
strSQL += "'" & Session("OrgID") & "', "
strSQL += "'" & m_params.GetParameterByName("NHS Number").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("SQLRI ID").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("Hospital
Number").m_queryStringData & "', "
strSQL += "'" & m_params.GetParameterByName("Hospital").m_queryStr ingData & "' "

' testing the parameters stuff
Dim sqlParameter As SqlParameter
Dim sqlParameter2 As SqlParameter

sqlParameter = New SqlParameter
sqlParameter2 = New SqlParameter

sqlParameter = objCommand.Parameters.Add("@ErrorCode", SqlDbType.Int)
sqlParameter.Direction = ParameterDirection.Output

sqlParameter2 = objCommand.Parameters.Add("@CDSPatientID", SqlDbType.Int)
sqlParameter2.Direction = ParameterDirection.Output

' set our command object
objCommand.CommandText = strSQL

' execute
objReader = objCommand.ExecuteReader()

' Close reader
objReader.Close()

' try to extract parameters
intCDSPatientID = objCommand.Parameters("@CDSPatientID").Value()
intErrorCode = objCommand.Parameters("@ErrorCode").Value()

' then all the tidy up code...
I had assumed (in the example above) that I would now not need to specify
the 'OUTPUT' parameters immediately after the 'EXEC writePatientUBHT', so I removed those, however, when I ran the code I got an error saying it could
find the stored procedure named 'EXEC writePatientUBHT 'value', 'value',
'value', 'value' - I put this down to the fact that I was now specifying
the commandType as a stored procedure and that somewhere I probably now need to say storeProc.name = "writePatientUBHT" or something...which would
suggest that I then need to add all my other values as parameters as well
and specify their directions?

Could anyone please advise me on this, and if possible perhaps a small
example using my code (where appropriate) from the above...

Thanks in advance for any help

Regards

Rob

[Rob Meade]

The following seems to work nicely :o)

' declare variables
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objReader As SqlDataReader
Dim strSQL As String
Dim intCDSPatientID As Integer
Dim intErrorCode As Integer

' Create and open our database connection
objConnection = New
SqlConnection(ConfigurationSettings.GetConfig("app Settings")("connString"))
objConnection.Open()
objCommand = New SqlCommand
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = objConnection

strSQL = "writePatientUBHT"

' add parameters
objCommand.Parameters.Add(createParameter("@ErrorC ode", SqlDbType.Int,
ParameterDirection.Output))
objCommand.Parameters.Add(createParameter("@CDSPat ientID", SqlDbType.Int,
ParameterDirection.Output))
objCommand.Parameters.Add(createParameter("@OrgID" , SqlDbType.Int,
ParameterDirection.Input, Session("OrgID")))
objCommand.Parameters.Add(createParameter("@NHSNum ber", SqlDbType.VarChar,
ParameterDirection.Input, m_params.GetParameterByName("NHS
Number").m_queryStringData))
objCommand.Parameters.Add(createParameter("@SQLRII D", SqlDbType.VarChar,
ParameterDirection.Input, m_params.GetParameterByName("SQLRI
ID").m_queryStringData))
objCommand.Parameters.Add(createParameter("@Hospit alNumber",
SqlDbType.VarChar, ParameterDirection.Input,
m_params.GetParameterByName("Hospital Number").m_queryStringData))
objCommand.Parameters.Add(createParameter("@Hospit alType",
SqlDbType.VarChar, ParameterDirection.Input,
m_params.GetParameterByName("Hospital").m_queryStr ingData))

' set our command object
objCommand.CommandText = strSQL

' execute
objReader = objCommand.ExecuteReader

' get values from reader object
'While objReader.Read()
' intCDSPatientID = objReader("CDSPatientID")
' intErrorCode = objReader("ErrorCode")
'End While
' Close reader
objReader.Close()
' try to extract parameters
intCDSPatientID = objCommand.Parameters("@CDSPatientID").Value()
intErrorCode = objCommand.Parameters("@ErrorCode").Value()
Response.Write("CDSPatientID: " & intCDSPatientID)
Response.Write("<BR>")
Response.Write("ErrorCode: " & intErrorCode)
Response.End()