Stored Procedure with Output parameter

8 Nov 2005 8:53 PM

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

8 Nov 2005 9:09 PM

W.G. Ryan - MVP

YOu need to set the Direction property of the parameter to
ParameterDirection.Output
Show quote

"Phenom" <tjon***@phenom-biz.com> wrote in message
news:1131483218.040830.60300@g14g2000cwa.googlegroups.com...
>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
>

8 Nov 2005 9:21 PM

Greg Burns

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

Show quote

"Phenom" <tjon***@phenom-biz.com> wrote in message
news:1131483218.040830.60300@g14g2000cwa.googlegroups.com...
>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
>

9 Nov 2005 2:03 PM

Phenom

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?

9 Nov 2005 4:47 PM

Phenom

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.

9 Nov 2005 6:16 PM

Andy

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

9 Nov 2005 7:08 PM

Phenom

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.

9 Nov 2005 7:28 PM

Phenom

Greg

Your last example worked. I appreciate your help.
TJ

9 Nov 2005 6:17 PM

Greg Burns

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

Show quote

"Phenom" <tjon***@phenom-biz.com> wrote in message
news:1131554861.046360.138770@z14g2000cwz.googlegroups.com...
>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.
>

9 Nov 2005 6:40 PM

Greg Burns

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

Show quote

"Greg Burns" <bluebunny@newsgroups.nospam> wrote in message
news:%23T2OcnV5FHA.2604@TK2MSFTNGP11.phx.gbl...
> 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" <tjon***@phenom-biz.com> wrote in message
> news:1131554861.046360.138770@z14g2000cwz.googlegroups.com...
>>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.
>>
>
>

9 Nov 2005 7:14 PM

Phenom

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

9 Nov 2005 7:24 PM

Greg Burns

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

Show quote

"Phenom" <tjon***@phenom-biz.com> wrote in message
news:1131563652.576906.48170@g14g2000cwa.googlegroups.com...
> 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
>

9 Nov 2005 9:50 PM

Phenom

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.

9 Nov 2005 7:06 PM

Phenom

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.