Parameter Limitations with ADO.NET?

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

Phenom

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.
 
Phenom said:
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
Click to expand...

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.
 
Phenom said:
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.
Click to expand...

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
 
Back
Top