I'm trying to perform a simple insert. I have a stored proc that takes
3 params and inserts values into 2 tables. The proc itself works fine.
I tested it with query analyzer. However, I'm unable to get the query
to execute in .net.
Here is what I've tried:
1) Hard coding the sql command
2) hard coding the values for the btnImplementFile and btnBackoutFile
3)Checking the status of the connection  - 'just in case'
4)Making sure I actually am getting the value from the output variable
of the previous command. (This works. I'm able to display the value in
a label)
5)Setting the btnImplementFile.value.tostring - just to see if it would
make a difference. It didn't.


Here is a sample of the code.
Try
                sqlChange.ExecuteNonQuery()

                Dim event_id As Integer =
CInt(sqlChange.Parameters("@event_id").Value)
                'lblSuccess.Text = event_id
                'lblSuccess.Visible = True
                'Dim strConnection As String = cnnChange.State.ToString
                'lblSuccess.Text = strConnection
                'lblSuccess.Visible = True
                'lblSuccess.Text = btnImplementFile.Value
                'lblSuccess.Visible = True

                'sqlChange.CommandText = "exec sp02_InsertType_Docs '"
& event_id & "','" & _
                ' btnImplementFile.Value & "','" & btnBackoutFile.Value
& "'"
                sqlChange.CommandText = "sp02_InsertType_Docs"
                sqlChange.Parameters.Add("@event_id",
SqlDbType.Int).Value = event_id
                sqlChange.Parameters.Add("@doc_implement",
SqlDbType.VarChar, 250).Value = btnImplementFile.Value.ToString
                sqlChange.Parameters.Add("@doc_backout",
SqlDbType.VarChar, 250).Value = btnBackoutFile.Value.ToString
                'sqlChange.Parameters.Add("@doc_implement",
SqlDbType.VarChar, 250).Value = "some value"
                'sqlChange.Parameters.Add("@doc_backout",
SqlDbType.VarChar, 250).Value = "some value"

                Try
                    sqlChange.ExecuteNonQuery()
                    lblSuccess.Text = sqlChange.CommandText

                Catch ex As Exception
                    lblFail.Text = "Update failed on saving event type
and document paths."
                    lblFail.Visible = True
                    btnTypeFail.Visible = True
                End Try


I appreciate any help. I'm fairly new to .NET and seem to be having
trouble finding good online references for Stored Procs beyond the
basics and simple syntax examples.
Anyone want to write a good book on it? I'll buy one.

Show quote Hide quote Either hardcode the values into the SQLCommand.CommandText, or set the
SqlCommand.CommandText, the SqlCommand.CommandType to
CommandType.StoredProcedure and add parameters.  Don't do both.

David

This is 'hybrid' code - I'll repost what I'm actually using. I'm not
doing both at the same time - I had switched to hard coding for
debugging purposes.

I suggest you modify you Exception handler, so you can tell us what the
error is you are seeing.

Try
            sqlChange.ExecuteNonQuery()
Catch ex As Exception
            lblFail.Text = ex.ToString
            lblFail.Visible = True
           btnTypeFail.Visible = True
End Try

You should try running SQL Server Profiler.  That way you can "see" exactly
what SQL you are passing to the server.

A good book on sprocs:
http://www.amazon.com/gp/product/1590592875/104-0674121-4295956?v=glance&n=283155&v=glance
ISBN: 1-59059-287-5

Thanks. I'll check it out.

Select next item in DB without knowing what ID it has
Oracle Adventures
DataTable.Select() - is it possible to pass rows back to datatable
Stored Procedure with Output parameter
How to create an Empty XML file