What am I doing worng...?

15 Nov 2005 9:13 AM

Bruno Alexandre

Hi guys....

I must be doing something wrong, but What?

This code works fine:
--------------------------------------
strSQL = "SELECT * FROM utiUtentes WHERE idUtente = " & idUtente
With cmd
..Connection = conn
..CommandText = strSQL
..CommandType = CommandType.Text
End With

conn.Open()
------------------------------------------------

this won't!
------------------------------------------------
strSQL = "spGetUser"

With cmd
..Connection = conn
..CommandText = strSQL
..CommandType = CommandType.StoredProcedure
..Parameters("@idUser").Value = idUtente
End With

conn.Open()
-----------------------------------------------

the spGetUser store Procedure is this:

ALTER PROCEDURE [spGetUser]
@idUser as numeric
AS (
SELECT * FROM utiUsers WHERE idUser = @idUser
)

-----------------------------------------------

the variable idUser in VB code is a string type.

--

Bruno Alexandre
(Sintra, PORTUGAL)

15 Nov 2005 9:21 AM

Frans Bouma [C# MVP]

Bruno Alexandre wrote:

Show quote

> Hi guys....
>
>
> I must be doing something wrong, but What?
>
> This code works fine:
> --------------------------------------
> strSQL = "SELECT * FROM utiUtentes WHERE idUtente = " & idUtente
> With cmd
> .Connection = conn
> .CommandText = strSQL
> .CommandType = CommandType.Text
> End With
>
> conn.Open()
> ------------------------------------------------
>
> this won't!
> ------------------------------------------------
> strSQL = "spGetUser"
>
> With cmd
> .Connection = conn
> .CommandText = strSQL
> .CommandType = CommandType.StoredProcedure
> .Parameters("@idUser").Value = idUtente
> End With
>
> conn.Open()
> -----------------------------------------------
>
> the spGetUser store Procedure is this:
>
> ALTER PROCEDURE [spGetUser]
> @idUser as numeric
> AS (
> SELECT * FROM utiUsers WHERE idUser = @idUser
> )
>
> -----------------------------------------------
>
> the variable idUser in VB code is a string type.

    in that case, change
@idUser as numeric

    into

@idUser as varchar(100)

        Frans

    ps: use parameters in dynamic sql as well.

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

15 Nov 2005 10:01 AM

Bruno Alexandre

even so...

the error message is the same:
"An SqlParameter with ParameterName '@idUser' is not contained by this
SqlParameterCollection"

:(

what's worng with VS? the @idUser is in the SP, if I use this sp in the
Query Analyser it works!

--

Bruno Alexandre
(Sintra, PORTUGAL)

Show quote

"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> escreveu na mensagem
news:xn0e9rnu64xm1x004@news.microsoft.com...
> Bruno Alexandre wrote:
>
>> Hi guys....
>>
>>
>> I must be doing something wrong, but What?
>>
>> This code works fine:
>> --------------------------------------
>> strSQL = "SELECT * FROM utiUtentes WHERE idUtente = " & idUtente
>> With cmd
>> .Connection = conn
>> .CommandText = strSQL
>> .CommandType = CommandType.Text
>> End With
>>
>> conn.Open()
>> ------------------------------------------------
>>
>> this won't!
>> ------------------------------------------------
>> strSQL = "spGetUser"
>>
>> With cmd
>> .Connection = conn
>> .CommandText = strSQL
>> .CommandType = CommandType.StoredProcedure
>> .Parameters("@idUser").Value = idUtente
>> End With
>>
>> conn.Open()
>> -----------------------------------------------
>>
>> the spGetUser store Procedure is this:
>>
>> ALTER PROCEDURE [spGetUser]
>> @idUser as numeric
>> AS (
>> SELECT * FROM utiUsers WHERE idUser = @idUser
>> )
>>
>> -----------------------------------------------
>>
>> the variable idUser in VB code is a string type.
>
> in that case, change
> @idUser as numeric
>
> into
>
> @idUser as varchar(100)
>
> Frans
>
> ps: use parameters in dynamic sql as well.
>
>
>
> --
> ------------------------------------------------------------------------
> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------

15 Nov 2005 11:18 AM

Cor Ligthert [MVP]

Bruno,

You have to add that parameter to the collection first, that can in one
statement or in more, here a very simple sample.

http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

Be aware that you only add it once and after that set it as you do.

This is 2002/2003 in VB2005 it works however it is changed in
AddWithValue

I hope this helps,

Cor

15 Nov 2005 1:53 PM

Bruno Alexandre

just changing this
..Parameters.("@idUtente").Add = idUtente

into this
..Parameters.AddWithValue("@idUtente", idUtente)

Thank you Cor

--

Bruno Alexandre
(Sintra, PORTUGAL)

Show quote

"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> escreveu na mensagem
news:eAxwnZd6FHA.4012@TK2MSFTNGP14.phx.gbl...
> Bruno,
>
> You have to add that parameter to the collection first, that can in one
> statement or in more, here a very simple sample.
>
> http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071
>
> Be aware that you only add it once and after that set it as you do.
>
> This is 2002/2003 in VB2005 it works however it is changed in
> AddWithValue
>
> I hope this helps,
>
> Cor
>