Stored Procedure with Output parameter

  • Thread starter Thread starter Phenom
  • Start date Start date
P

Phenom

I am trying to execute a stored procedure with an output parameter. I
believe I am missing a piece of code, because the execution fails. I
don't know why. When I run the proc in query analyzer it works fine, so
I don't believe it is the procedure itself, simply something I am
missing in creating the params and executing.
I appreciate any help I can get.
Thanks.

Here is the code:
If (Page.IsValid) Then
sqlChange.CommandType = CommandType.StoredProcedure
sqlChange.CommandText = "sp01_InsertChange"

sqlChange.Parameters.Add("@it_emp_resp_id", SqlDbType.Int)
sqlChange.Parameters.Add("@emp_requestor",
SqlDbType.VarChar, 50)
sqlChange.Parameters.Add("@obj_affected_id", SqlDbType.Int)
sqlChange.Parameters.Add("@subject", SqlDbType.VarChar,
8000)
sqlChange.Parameters.Add("@reason", SqlDbType.VarChar,
8000)
sqlChange.Parameters.Add("@priority", SqlDbType.Char, 2)
sqlChange.Parameters.Add("@comments", SqlDbType.VarChar,
8000)
sqlChange.Parameters.Add("@service_interupt",
SqlDbType.Char, 2)
sqlChange.Parameters.Add("@dt_implement_sched",
SqlDbType.DateTime)
sqlChange.Parameters.Add("@dt_implement_act",
SqlDbType.DateTime)
sqlChange.Parameters.Add("@interupt_time",
SqlDbType.Decimal, 18)
sqlChange.Parameters.Add("@event_id", SqlDbType.Int)

sqlChange.Parameters("@emp_requestor").Value =
ddlAssigned.SelectedValue
sqlChange.Parameters("@emp_requestor").Value =
txtRequestor.Text
sqlChange.Parameters("@obj_affected_id").Value =
ddlAffected.SelectedValue
sqlChange.Parameters("@subject").Value = txtSubject.Text
sqlChange.Parameters("@reason").Value = txtReason.Text
sqlChange.Parameters("@priority").Value =
ddlPriority.SelectedValue
sqlChange.Parameters("@comments").Value = txtNotes.Text
sqlChange.Parameters("@service_interupt").Value =
rdBtnInterrupt.SelectedValue
sqlChange.Parameters("@dt_implement_sched").Value =
txtSchedImplement.Text
sqlChange.Parameters("@dt_implement_sched").Value =
txtActualImplement.Text
sqlChange.Parameters("@interupt_time").Value =
txtInterruptMin.Text

sqlChange.Connection = cnnChange
cnnChange.Open()
Try
sqlChange.ExecuteNonQuery()
lblSuccess.Visible = True
btnSuccess.Visible = True
Catch ex As Exception

lblFail.Visible = True
btnFail.Visible = True
lbTest.Text = sqlChange.CommandText
Finally
cnnChange.Close()
End Try

End If

Here is the stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp01_InsertChange
--items inserted into tbl01_event
@it_emp_resp_id int,
@emp_requestor varchar(50),
@obj_affected_id int,
@subject varchar(8000),
@reason varchar(8000) = 'Null',
@priority char(2) = 'Null',
@comments varchar(8000) = 'Null',
@service_interupt char(2),
@dt_implement_sched datetime,
@dt_implement_act datetime,
@interupt_time decimal(18) = 'Null',
--items inserted into tbl04_doc_created
@event_id int output --generated from insert into tbl01_event

as

begin
--tbl01_event insert
insert into
tbl01_event(it_emp_resp_id,emp_requestor,dt_event,obj_affected_id,subject,
reason,
priority,comments,service_interupt,dt_implement_sched,dt_implement_act,
interupt_time,dt_created,dt_modified)

values(@it_emp_resp_id,@emp_requestor,getdate(),@obj_affected_id,@subject,
@reason,
@priority,@comments,@service_interupt,@dt_implement_sched,@dt_implement_act,
@interupt_time,getdate(),getdate())

select @event_id = @@identity

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
YOu need to set the Direction property of the parameter to
ParameterDirection.Output
 
Where do you ever set @it_emp_resp_id?

Change this:
sqlChange.Parameters.Add("@event_id", SqlDbType.Int).
to:
sqlChange.Parameters.Add("@event_id", SqlDbType.Int).Direction =
ParameterDirection.Output

Same parameter here twice...
sqlChange.Parameters("@dt_implement_sched").Value = txtSchedImplement.Text()
sqlChange.Parameters("@dt_implement_sched").Value =
txtActualImplement.Text()

@@IDENTITY is trouble, use SCOPE_IDENTITY() instead.

Greg
 
I appreciate the help. This is my first attempt at .NET with a proc
that involves an output param.

Greg - thanks for pointing out my multiple params. I've stared at this
so long, I didn't even notice it.

I've made the changes. However, I'm still not succeeding. I'll keep
playing with it. In the meantime, is there anything else I can try?
 
I thought I would start back tracking to see if I could find where my
error is. 1st, I removed the output variable from the procedure and
tried it as a simple Insert statement. No luck. 2nd) I hard coded the
stored procedure as an 'exec [proc]' - simply to see if I could find
what was going on. I had the results displayed on a label in my app.
This didn't work in the .net app but did work in query analyzer.

So, 3rd - I used the same 'exec [proc]' but this time instead of hard
coding the parameters I contructed the sql command to pick up the
values from the form (i.e. exec sp01_InsertChange '" &
ddlAssigned.SelectedValue & "'," - and so on) No results. But when I
did it in query analyzer I found that I overlooked setting the value of
the dropdownlist to correspond to the displayed text's ID. When I
changed that - it still doesn't work.

So, now I'm at the point where the sqlCommand text generated from the
app doesn't work in the actual application, but is working in query
analyzer. I double checked the connection (although I think I would get
a specific connection error if that were my problem).

I'm still thinking this one out - but if someone that understands
ado.net better than I might give me a push in the right direction as to
what I need to look at next - Please do so!

Thank you and I appreciate your help.
 
It looks like you may have some parameters specified twice
still...@emp_requestor is one, carefully check for others.

As long as you have each paremeter name match the stored procedure
parameter name and supply a value for all input params, the procedure
should work fine. The direction setting is important too, and unless
you removed it from the stored procedure as well as the code, it will
be required for the command to execute.

HTH
Andy
 
I'll see if I can't take a closer look.

Real quick, I noticed in your sproc:

@reason varchar(8000) = 'Null',
@priority char(2) = 'Null',
@comments varchar(8000) = 'Null',


Not sure what you are doing here, but 'Null' is a string not the value of
Null. I assume this is not what you inteneded when you added this line
@interupt_time decimal(18) = 'Null',

That just doesn't make sense.

They should be:
@reason varchar(8000) = NULL,
@priority char(2) = NULL,
@comments varchar(8000) = NULL,
@interupt_time decimal(18) = NULL,

Greg
 
Here is a stripped down version that should run I would think.

Dim cn As New SqlConnection("data source=.;initial
catalog=dbname;integrated security=SSPI;persist security info=False;packet
size=4096;Application Name=test")
Dim cmd As New SqlCommand("sp01_InsertChange", cn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@it_emp_resp_id", SqlDbType.Int).Value =
CInt(ddlAssigned.SelectedValue)
cmd.Parameters.Add("@emp_requestor", SqlDbType.VarChar, 50).Value =
txtRequestor.Text
cmd.Parameters.Add("@obj_affected_id", SqlDbType.Int).Value =
CInt(ddlAffected.SelectedValue)
cmd.Parameters.Add("@subject", SqlDbType.VarChar, 8000).Value =
txtSubject.Text
cmd.Parameters.Add("@reason", SqlDbType.VarChar, 8000).Value =
txtReason.Text
cmd.Parameters.Add("@priority", SqlDbType.Char, 2).Value =
ddlPriority.SelectedValue
cmd.Parameters.Add("@comments", SqlDbType.VarChar, 8000).Value =
txtNotes.Text
cmd.Parameters.Add("@service_interupt", SqlDbType.Char, 2).Value =
rdBtnInterrupt.SelectedValue
cmd.Parameters.Add("@dt_implement_sched", SqlDbType.DateTime).Value
= CDate(txtSchedImplement.Text)
cmd.Parameters.Add("@dt_implement_act", SqlDbType.DateTime).Value =
CDate(txtActualImplement.Text)
cmd.Parameters.Add("@interupt_time", SqlDbType.Decimal, 18).Value =
CDec(txtInterruptMin.Text)
cmd.Parameters.Add("@event_id", SqlDbType.Int).Direction =
ParameterDirection.Output

cn.Open()
Try
cmd.ExecuteNonQuery()
Dim eventID As Integer = CInt(cmd.Parameters("@event_id").Value)

lblSuccess.Visible = True
btnSuccess.Visible = True

Catch ex As Exception
lblFail.Visible = True
btnFail.Visible = True

Finally
cn.Close()
End Try

Greg

Greg Burns said:
I'll see if I can't take a closer look.

Real quick, I noticed in your sproc:

@reason varchar(8000) = 'Null',
@priority char(2) = 'Null',
@comments varchar(8000) = 'Null',


Not sure what you are doing here, but 'Null' is a string not the value of
Null. I assume this is not what you inteneded when you added this line
@interupt_time decimal(18) = 'Null',

That just doesn't make sense.

They should be:
@reason varchar(8000) = NULL,
@priority char(2) = NULL,
@comments varchar(8000) = NULL,
@interupt_time decimal(18) = NULL,

Greg


Phenom said:
I thought I would start back tracking to see if I could find where my
error is. 1st, I removed the output variable from the procedure and
tried it as a simple Insert statement. No luck. 2nd) I hard coded the
stored procedure as an 'exec [proc]' - simply to see if I could find
what was going on. I had the results displayed on a label in my app.
This didn't work in the .net app but did work in query analyzer.

So, 3rd - I used the same 'exec [proc]' but this time instead of hard
coding the parameters I contructed the sql command to pick up the
values from the form (i.e. exec sp01_InsertChange '" &
ddlAssigned.SelectedValue & "'," - and so on) No results. But when I
did it in query analyzer I found that I overlooked setting the value of
the dropdownlist to correspond to the displayed text's ID. When I
changed that - it still doesn't work.

So, now I'm at the point where the sqlCommand text generated from the
app doesn't work in the actual application, but is working in query
analyzer. I double checked the connection (although I think I would get
a specific connection error if that were my problem).

I'm still thinking this one out - but if someone that understands
ado.net better than I might give me a push in the right direction as to
what I need to look at next - Please do so!

Thank you and I appreciate your help.
Click to expand...
Click to expand...
 
You're right. I know that - I don't know what I was thinking.
I don't think that would cause it to not work - just not get what
values I might expect when I execute.
 
Yes - I corrected that. I commented out all references to the output
param in both the proc and the code in a stab at slowly backing out and
seeign at what level everything is failing on.

I'll repost my code as is - sometimes it's easier to see the code than
have it explained.
 
I'll take a close look at that and see how my original code measures
up.
I'm posting what is executing now.
This code works. So - I'm guessing something somewhere is wrong with
how I address the stored procedure or params.
Granted, I won't leave it like this, but I wanted to find out what
values the parameters were, things like that to find any other errors I
couldn't see. Once I discover the root problem, I will add code back
in.

If (Page.IsValid) Then

sqlChange.CommandType = CommandType.CommandType.Text
sqlChange.CommandText = " exec sp01_InsertChange '" &
ddlAssigned.SelectedValue & _
"', '" & txtRequestor.Text & "','" &
ddlAffected.SelectedValue & "','" & txtSubject.Text & _
"','" & txtReason.Text & "', '" &
ddlPriority.SelectedValue & "','" & txtNotes.Text & _
"','" & rdBtnInterrupt.SelectedValue & "','" &
txtSchedImplement.Text & "','" & txtActualImplement.Text & _
"','" & txtInterruptMin.Text & "'"

'sqlChange.Parameters.Add("@it_emp_resp_id", SqlDbType.Int)
'sqlChange.Parameters.Add("@emp_requestor",
SqlDbType.VarChar, 50)
'sqlChange.Parameters.Add("@obj_affected_id",
SqlDbType.Int)
'sqlChange.Parameters.Add("@subject", SqlDbType.VarChar,
8000)
'sqlChange.Parameters.Add("@reason", SqlDbType.VarChar,
8000)
'sqlChange.Parameters.Add("@priority", SqlDbType.Char, 2)
'sqlChange.Parameters.Add("@comments", SqlDbType.VarChar,
8000)
'sqlChange.Parameters.Add("@service_interupt",
SqlDbType.Char, 2)
'sqlChange.Parameters.Add("@dt_implement_sched",
SqlDbType.DateTime)
'sqlChange.Parameters.Add("@dt_implement_act",
SqlDbType.DateTime)
'sqlChange.Parameters.Add("@interupt_time",
SqlDbType.Decimal, 18)
' sqlChange.Parameters.Add("@event_id",
SqlDbType.Int).Direction = ParameterDirection.Output

'sqlChange.Parameters("@it_emp_resp_id").Value =
ddlAssigned.SelectedValue
'sqlChange.Parameters("@emp_requestor").Value =
txtRequestor.Text
'sqlChange.Parameters("@obj_affected_id").Value =
ddlAffected.SelectedValue
'sqlChange.Parameters("@subject").Value = txtSubject.Text
'sqlChange.Parameters("@reason").Value = txtReason.Text
'sqlChange.Parameters("@priority").Value =
ddlPriority.SelectedValue
'sqlChange.Parameters("@comments").Value = txtNotes.Text
'sqlChange.Parameters("@service_interupt").Value =
rdBtnInterrupt.SelectedValue
'sqlChange.Parameters("@dt_implement_sched").Value =
txtSchedImplement.Text
'sqlChange.Parameters("@dt_implement_act").Value =
txtActualImplement.Text
'sqlChange.Parameters("@interupt_time").Value =
txtInterruptMin.Text

sqlChange.Connection = cnnChange
cnnChange.Open()
Try
sqlChange.ExecuteNonQuery()
lblSuccess.Visible = True
btnSuccess.Visible = True
Catch ex As Exception

lblFail.Visible = True
btnFail.Visible = True
lbTest.Text = sqlChange.CommandText
Finally
cnnChange.Close()
End Try
 
If I had to guess, it would be in your SqlDateTime parameters.

Make sure you are sucessfully converting them to datetime values before
assingning them to the parameters.

There is a thread a earlier titled "Saving to a DateTime field in Sql Server
2000 using ADO.Net" that talks about how parameters will behave differently
with dates than with a commandtext built up from a string like iin your
current test.

Greg
 
I think you're right. Because that was basically the only difference
between what I had vs. the code you shared.

I appreciate your help.
 
Back
Top