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)

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#)
------------------------------------------------------------------------

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)



"Frans Bouma [C# MVP]" <(E-Mail Removed)> escreveu na mensagem
news:(E-Mail Removed)...
> 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#)
> ------------------------------------------------------------------------

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?...6-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

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


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


Thank you Cor
--



Bruno Alexandre
(Sintra, PORTUGAL)



"Cor Ligthert [MVP]" <(E-Mail Removed)> escreveu na mensagem
news:(E-Mail Removed)...
> 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?...6-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
>