SQLParameter - What is the difference?

[Jim Heavey]

Hello, I am trying to understand why one set of instructions work for
creating a SQLParameter and another set does not.

This set works just fine...

Dim parm As New SqlParameter()
parm.DbType = DbType.Int16
'parm.Size = 4
parm.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(parm)

This one does not

cmd.Parameters.Add(New SqlParameter("@RETURN_VALUE", DbType.Int16, 2,
ParameterDirection.ReturnValue))


When I run with the second version It gives me an error indicating that
I have not supplied any parms.

I am sure I have seen another version of this command which looks like
the following and allows you to set the value as well...

cmd.Parameters.Add(New SqlParameter("@SomeParm", DbType.Int16, 2,
ParameterDirection.ReturnValue).Value="My Value")

I presume that I should probably use the SQLDbType and not the DbType,
but it does not seem to care (at least on the 1st version).

Can you set me straight as to why the first version works and the second
does not.

Can I set the Name, value, type and direction with just a single
instruction?

Thanks in advance for your assistance!!!!!!!!

 

[William \(Bill\) Vaughn]

You should not even try to set a value for the ReturnValue. Try this:

cmd.Parameters.Add("@RETURN_VALUE", DbType.Int16).Direction =
ParameterDirection.ReturnValue

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jim Heavey]

Thanks Bill, (I have read more then one of your books!!)

Yes, I understand that I do not want to set the value for a @RETURN_VALUE -
that particular question I had intended to be asking more about the general
"input" parm.

If I don't see a way for me to get this all done in a single line of code,
setting the parm name, the dbType, the size, the direction and the value.
The best seems to be the following:

cmd.Parameters.Add("@StarID", DbType.Int, 4).Direction =
ParameterDirection.Input
cmd.Parameters("@StarID").Value = dltValue

Is there a better way?

Why does it not care if you use DBType or SQLDBType?

Again, thanks!!!

 

[David Sceppa]

Jim,

Dim p As New SqlParameter("@RETURN_VALUE", SqlDbType.Int, _
2, ParameterDirection.ReturnValue))

The fourth parameter of the constructor you're calling
corresponds to the SourceColumn property rather than the
Direction property.

As Bill pointed out, you could use one of the constructors
and set Direction immediately afterwards (or even in the same
line of code).

Another option is to use the "kitchen sink" constructor that
takes values for the ParameterName, SqlDbType, Size, Direction,
IsNullable, Precision, Scale, SourceColumn, SourceVersion, and
Value properties respectively.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[William \(Bill\) Vaughn]

By default, the Direction property is Input, so you don't need to set it.
This lets you set the value in a number of ways:

cmd.Parameters.Add("@MyParm", 1) ' 1 is the value

cmd.Parameters.Add("@MyParm").Value = 1 ' 1 is the value

I have about 20 of these in my book...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________