What am I doing worng...?

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

 

[Frans Bouma [C# MVP]]

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
)

in that case, change
@idUser as numeric

into

@idUser as varchar(100)

Frans

ps: use parameters in dynamic sql as well.



--

 

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

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
)

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

 

[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

 

[Bruno Alexandre]

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


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


Thank you Cor
--



Bruno Alexandre
(Sintra, PORTUGAL)