Parameter inconsistency

[rbb]

I have a class that accepts a command parameter as a parameter.

 

  Private cmd As SqlCommand
  Public Sub AppendParameter(ByVal p As SqlParameter)

     cmd.Parameters.Add(p)

  End Sub

 

The following code works perfectly:

cls.AppendParameter(New SqlParameter("@id", SqlDbType.VarChar, 25).Value = str)

 

But this code gives me a "Specified cast is not valid." error

cls.AppendParameter(New SqlParameter("@Manufacturer_ID", SqlDbType.Int).Value = 1)

 

If I create a parameter object and pass that, it works just fine. Any ideas?

 

Thanks,

 

Rob

[Iceplug]

You actually got that to compile? I can't get anything similar to that to compile. Unless that is some new terminology.

 

Have you tried it like this?

Dim arg As SqlParameter = New SqlParameter("@Manufacturer_ID", SqlDbType.Int)

arg.Value = 1

cls.AppendParameter(arg)

:)

 

Or perhaps it's a SqlParameter thing. Which line gives you the error?

Edited September 3, 2004 by Iceplug

[Nerseus]

I would think it would work if all the "Option Strict" stuff were turned off...

 

When you pass in:

New SqlParameter("@id", SqlDbType.VarChar, 25).Value = str

 

I'm guessing that works the way it used to, which is:

Creates SqlParameter object

Compares the Value to str

Passes the result of the comparison (or assignment, can't remember) to your function.

 

If str is null, you might have "no problems", though you'd get some later.

 

If that is indeed the way it's working (which would explain why "Value = 1" throws a runtime error, because it would be passing "1" to the method), then you'll need to do what IcePlug suggested.

 

Or, as an alternative, why not enhance your method to something like:

AppendParameter(ByVal parameterName As String, ByBal type As SqlDbType, ByVal size As Integer32, Value As Object)

 

Basically, expose the params of the SqlParameter constructor as individual arguments PLUS the value.

 

-ner

[rbb]

Option Strict is turned off, but the working code does pass an actual value and works as expected. If it turn Option Strict on, it does give me an error. Seems to be unpredictable results.

 

I will move to a method based on Nerseus's suggestion.

 

Thanks

 

Rob

[Nerseus]

I tried your exact code to test my theory - I get a runtime error every time on either line you showed:

 

cls.AppendParameter(New SqlParameter("@Manufacturer_ID", SqlDbType.Int).Value = 1)

cls.AppendParameter(New SqlParameter("@id", SqlDbType.VarChar, 25).Value = str)

 

I set cls = New Class1 (my test class). I defined str as a string and set it to "hello".

 

Why not turn on Option Strict? If you do, it will catch little things like this. In general, I can't see why you'd ever want it off unless you were converting a large project and couldn't live with the compile errors...

 

 

Try this test. In your class that has the AppendParameter function, define the following:

   Public Sub Test(ByVal s As String)
       Debug.WriteLine(s)
   End Sub

 

Now in the calling class, try the following line instead of the AppendParameter call:

Dim str As String = "hello"
cls.Test(str = "howdy")

 

What you see in the Output window may surprise you. It isn't "hello" or "howdy", it's "False". The compare is evaluated and the result is passed to the method/Sub. This isn't C++ where the results is also an expression which is passed to the Sub. At least, I've never seen any version of VB or VBET do that.

 

-ner