Parameter Order

20 Jan 2006 5:18 PM

Vayse

I never realised before that parameter order is important. It was only I was
posting another message in this newsgroup that I realised it.
Has it always been this way?

As in this will work:
Using connAsset As New OleDbConnection(My.Settings.AssetsConnectionString)
connAsset.Open()
Dim stSQL As String = "UPDATE Assets SET StatusID = ? WHERE (AssetCode = ?)"
Dim commandAsset As New OleDbCommand(stSQL, connAsset)
commandAsset.Parameters.Add("@StatusID", OleDbType.VarChar, 1).Value =
conSTATUS_DISPOSED
commandAsset.Parameters.Add("@AssetCode", OleDbType.VarChar, 10).Value =
stAssetCode

But this won't update the row. No error message, it just doesn't update the
row.
commandAsset.Parameters.Add("@AssetCode", OleDbType.VarChar, 10).Value =
stAssetCode
commandAsset.Parameters.Add("@StatusID", OleDbType.VarChar, 1).Value =
conSTATUS_DISPOSED

Don't know how this never came up before, I guess I always just added the
parameters in order.
Hmmm.
Vayse

20 Jan 2006 5:33 PM

Cor Ligthert [MVP]

Vayse,

>I never realised before that parameter order is important. It was only I
>was posting another message in this newsgroup that I realised it.
> Has it always been this way?
>

Only in OleDb in fact is the parametername without sense.

(This is really confusing written on MSDN were the SQLClient text is almost
direct copied).

See this sample for OleDb

http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

I hope that this gives an idea.

Cor

20 Jan 2006 5:42 PM

Ranjan Sakalley

Hello Vayse,

You are not using named parameters here, and therefore @StatusId and @AssetCode
are interchanged.

If you want the query to looklike

Update Assets Set StatusId = 'y' where AssetCode = 'Vayse'

it becomes

Update Assets Set StatusId = 'Vayse' where AssetCode = y' which will not
run becuase their might be no StatusId as 'Vayse' (seemingly varchar(1)).

This error will come only when you use '?' operator here, as there is no
way ADO.Net can understand just by reading @StatusId and place it likewise.

HTH,
r.

Show quote

> I never realised before that parameter order is important. It was only
> I was
> posting another message in this newsgroup that I realised it.
> Has it always been this way?
> As in this will work:
> Using connAsset As New
> OleDbConnection(My.Settings.AssetsConnectionString)
> connAsset.Open()
> Dim stSQL As String = "UPDATE Assets SET StatusID = ? WHERE (AssetCode
> = ?)"
> Dim commandAsset As New OleDbCommand(stSQL, connAsset)
> commandAsset.Parameters.Add("@StatusID", OleDbType.VarChar, 1).Value =
> conSTATUS_DISPOSED
> commandAsset.Parameters.Add("@AssetCode", OleDbType.VarChar, 10).Value
> =
> stAssetCode
> But this won't update the row. No error message, it just doesn't
> update the
> row.
> commandAsset.Parameters.Add("@AssetCode", OleDbType.VarChar, 10).Value
> =
> stAssetCode
> commandAsset.Parameters.Add("@StatusID", OleDbType.VarChar, 1).Value =
> conSTATUS_DISPOSED
> Don't know how this never came up before, I guess I always just added
> the
> parameters in order.
> Hmmm.
> Vayse

20 Jan 2006 6:48 PM

W.G. Ryan - MVP

Vayse:

The OleDbProvider uses the order in which paramaters are added to the
collection with respect to the sql statement, the same way String.Format
does (for a metaphor). You can name the parameters but that doesn't
supercede the position in which you add them. So the short answer is, with
respect to the OleDbProvider, yes, it's always been this way.
Show quote

"Vayse" <vayse@nospam.nospam> wrote in message
news:eFADRWeHGHA.344@TK2MSFTNGP09.phx.gbl...
>I never realised before that parameter order is important. It was only I
>was posting another message in this newsgroup that I realised it.
> Has it always been this way?
>
> As in this will work:
> Using connAsset As New OleDbConnection(My.Settings.AssetsConnectionString)
> connAsset.Open()
> Dim stSQL As String = "UPDATE Assets SET StatusID = ? WHERE (AssetCode =
> ?)"
> Dim commandAsset As New OleDbCommand(stSQL, connAsset)
> commandAsset.Parameters.Add("@StatusID", OleDbType.VarChar, 1).Value =
> conSTATUS_DISPOSED
> commandAsset.Parameters.Add("@AssetCode", OleDbType.VarChar, 10).Value =
> stAssetCode
>
> But this won't update the row. No error message, it just doesn't update
> the row.
> commandAsset.Parameters.Add("@AssetCode", OleDbType.VarChar, 10).Value =
> stAssetCode
> commandAsset.Parameters.Add("@StatusID", OleDbType.VarChar, 1).Value =
> conSTATUS_DISPOSED
>
> Don't know how this never came up before, I guess I always just added the
> parameters in order.
> Hmmm.
> Vayse
>