...is not a parameter for procedure...

[mharness]

Hello,

I'm plucking my head bald trying to figure this one out.

Here's the stored procedure qupdSlide:

UPDATE tblSlides
SET Title = @Title
WHERE (SlideID = @SlideID)

Here's the code that calls it:

Dim cmd As New SqlCommand
Dim prmTitle As New SqlParameter
Dim prmSlideID As New SqlParameter

With prmTitle
.ParameterName = "Title"
.SqlDbType = SqlDbType.NVarChar
.Value = Slide.Title
End With

With prmSlideID
.ParameterName = "SlideID"
.SqlDbType = SqlDbType.Int
.Value = Slide.SlideID
End With

With cmd
.Parameters.Add(prmSlideID)
.Parameters.Add(prmTitle)

.CommandType = CommandType.StoredProcedure
.CommandText = "qupdSlide"
.Connection = Connection()

.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
End With

and here's the error I get:

SlideId is not a parameter for procedure qupdSlide.

I'll admit that I'm new at this but I've used the same approach with several
other tables and procedures and it works perfectly. I can run the sp in an
access adp and it does what it's supposed to do but it will not run from
asp. Data types all match. Parameters have good values right up to the
point of executing the query. I tried updating any of several other fields
in the table but the error's the same. Transaction log filled up today and
I'm still trying to figure out how to empty it--could that have anything to
do with this error?

Do you see why this won't work?

Thanks,

Mike

 

[mharness]

Hello "P",

You are, of course correct and thank you for the suggestion to simplify the
code.

Best regards,

Mike

 

[P. Van Den Goess]

I believe your problem to be that the parameters names are not consistent.
If you modify code block as such, I believe it should work.

With prmTitle

.ParameterName = "@Title" 'Adding the @ symbol to match proc declaration
.SqlDbType = SqlDbType.NVarChar
.Value = Slide.Title
End With

With prmSlideID
.ParameterName = "@SlideID" 'Adding the @ symbol to match

proc declaration

.SqlDbType = SqlDbType.Int
.Value = Slide.SlideID
End With

As an aside unrelated to the problem you report, you can greatly simplify
the code you are writing by using an overloaded constructor of SqlParameter.
There are many, but for each With block that you are using, you can use a
one line declaration and initialisation:

cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Slide.Title
cmd.Parameters.Add("@SlideID", SqlDbType.Int).Value = Slide.SlideID

However please make note that this difference is only to save code in more
readable manner, something which many may argue. I bring it to your
attention only as a general note. The problem you are experiencing seems
only related to the first part of my answer.

 

[P. Van Den Goess]

You are most welcome.

Hello "P",

You are, of course correct and thank you for the suggestion to simplify the
code.

Best regards,

Mike