R
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_queryStringData &
"' "
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("appSettings")("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_queryStringData &
"' "
' 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
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_queryStringData &
"' "
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("appSettings")("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_queryStringData &
"' "
' 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