Expected parameter error

[Andy G]

I am getting an error when I try to call my stored procedure. Exception
adding account. Procedure 'stpCFSPH_CM_RG ST_USER' expects parameter
'@USER_DESCR', which was not supplied.

I have a radio button list w/ two items, Student w/ a value of 2 and
PrivUser w/ a value of 3. What I do on the click 'Submit' button is set all
of the variables and pass them to my Admin class. Below is specifically
what I define for the userRole (what I am getting the error on)
Dim userRole As Integer = radUserList.Sel ectedItem.Value

Admin.StoreAcco untDetails(txtU serName.Text, passwordHash, salt, tempAnswer,
qstn, userRole)

Here is my Admin.StoreAcco untDetails code:

Dim conn As SqlConnection = New SqlConnection(G etConn.GetCnxSt ring())

Dim cmd As SqlCommand = New SqlCommand("stp CFSPH_CM_RGST_U SER", conn)

cmd.CommandType = CommandType.Sto redProcedure

Dim sqlParam As SqlParameter = Nothing

[ALL OF THE OTHER PARAMS GO HERE, LEFT OUT FOR CLARITY]

sqlParam = cmd.Parameters. Add("@USER_DESC R", SqlDbType.Int, 10)

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQ uery()

Catch ex As Exception

' Code to check for primary key violation (duplicate account name)

' or other database errors omitted for clarity

Throw New Exception("Exce ption adding account. " + ex.Message)

Finally

conn.Close()

End Try

[Gopal \(FMS, Inc.\)]

Can you post SQL Script that defines the stored procedure
'stpCFSPH_CM_RG ST_USER' ?
Thanks
--
Gopal Rangaswamy
Microsoft Certified Solutions Developer
FMS, Inc.
<http://www.fmsinc.com/consulting>
<http://www.fmsinc.com/dotnet/SourceBook/>

"Andy G" <aj*****@iastat e.edu> wrote in message
news:er******** ******@tk2msftn gp13.phx.gbl...

I am getting an error when I try to call my stored procedure. Exception
adding account. Procedure 'stpCFSPH_CM_RG ST_USER' expects parameter
'@USER_DESCR', which was not supplied.

I have a radio button list w/ two items, Student w/ a value of 2 and
PrivUser w/ a value of 3. What I do on the click 'Submit' button is set all of the variables and pass them to my Admin class. Below is specifically
what I define for the userRole (what I am getting the error on)
Dim userRole As Integer = radUserList.Sel ectedItem.Value

Admin.StoreAcco untDetails(txtU serName.Text, passwordHash, salt, tempAnswer, qstn, userRole)

Here is my Admin.StoreAcco untDetails code:

Dim conn As SqlConnection = New SqlConnection(G etConn.GetCnxSt ring())

Dim cmd As SqlCommand = New SqlCommand("stp CFSPH_CM_RGST_U SER", conn)

cmd.CommandType = CommandType.Sto redProcedure

Dim sqlParam As SqlParameter = Nothing

[ALL OF THE OTHER PARAMS GO HERE, LEFT OUT FOR CLARITY]

sqlParam = cmd.Parameters. Add("@USER_DESC R", SqlDbType.Int, 10)

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQ uery()

Catch ex As Exception

' Code to check for primary key violation (duplicate account name)

' or other database errors omitted for clarity

Throw New Exception("Exce ption adding account. " + ex.Message)

Finally

conn.Close()

End Try

[Cowboy (Gregory A. Beamer) - MVP]

I would first reverse the logic so you can query things:

Dim sqlParam As SqlParameter
sqlParam = ("@USER_DESC R", SqlDbType.Int, 10)
sqlParam.Value = userDescr
cmd.Parameters. Add(sqlParam)

'Repeat for other parameters (in order preferably)

This gives you a bit more control over what is happening, as you can set a
breakpoint and query items.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************** ************
Think Outside the Box!
*************** ************
"Andy G" wrote:

I am getting an error when I try to call my stored procedure. Exception
adding account. Procedure 'stpCFSPH_CM_RG ST_USER' expects parameter
'@USER_DESCR', which was not supplied.

I have a radio button list w/ two items, Student w/ a value of 2 and
PrivUser w/ a value of 3. What I do on the click 'Submit' button is set all
of the variables and pass them to my Admin class. Below is specifically
what I define for the userRole (what I am getting the error on)
Dim userRole As Integer = radUserList.Sel ectedItem.Value

Admin.StoreAcco untDetails(txtU serName.Text, passwordHash, salt, tempAnswer,
qstn, userRole)

Here is my Admin.StoreAcco untDetails code:

Dim conn As SqlConnection = New SqlConnection(G etConn.GetCnxSt ring())

Dim cmd As SqlCommand = New SqlCommand("stp CFSPH_CM_RGST_U SER", conn)

cmd.CommandType = CommandType.Sto redProcedure

Dim sqlParam As SqlParameter = Nothing

[ALL OF THE OTHER PARAMS GO HERE, LEFT OUT FOR CLARITY]

sqlParam = cmd.Parameters. Add("@USER_DESC R", SqlDbType.Int, 10)

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQ uery()

Catch ex As Exception

' Code to check for primary key violation (duplicate account name)

' or other database errors omitted for clarity

Throw New Exception("Exce ption adding account. " + ex.Message)

Finally

conn.Close()

End Try

[Andy G]

Here is the Stored Proc...

CREATE PROCEDURE dbo.stpCFSPH_CM _RGST_USER

@LOGIN_NAME VARCHAR(255),
@PSWRD VARCHAR(40),
@SALT VARCHAR(10),
@ANSWER VARCHAR(50),
@QSTN_ID INT,
@USER_DESCR INT

AS
SET NOCOUNT ON

DECLARE @PRSN_ID INT

BEGIN
INSERT INTO dbo.tblCFSPH_CM _LOGIN (LOGIN_NAME, PSWRD, SALT, ANSWER, QSTN_ID)
VALUES(@LOGIN_N AME, @PSWRD, @SALT, @ANSWER, @QSTN_ID)
END

BEGIN
SELECT @PRSN_ID = SCOPE_IDENTITY( )
END

BEGIN
INSERT INTO dbo.tblCFSPH_CM _PRSN_ROLE_LINK (PRSN_ID, ROLE_ID)
VALUES (@PRSN_ID, @USER_DESCR)
END

"Gopal (FMS, Inc.)" <go**********@f msinc.com> wrote in message
news:Oe******** ******@tk2msftn gp13.phx.gbl...

Can you post SQL Script that defines the stored procedure
'stpCFSPH_CM_RG ST_USER' ?
Thanks
--
Gopal Rangaswamy
Microsoft Certified Solutions Developer
FMS, Inc.
<http://www.fmsinc.com/consulting>
<http://www.fmsinc.com/dotnet/SourceBook/>

"Andy G" <aj*****@iastat e.edu> wrote in message
news:er******** ******@tk2msftn gp13.phx.gbl...

I am getting an error when I try to call my stored procedure. Exception
adding account. Procedure 'stpCFSPH_CM_RG ST_USER' expects parameter
'@USER_DESCR', which was not supplied.

I have a radio button list w/ two items, Student w/ a value of 2 and
PrivUser w/ a value of 3. What I do on the click 'Submit' button is set

all

of the variables and pass them to my Admin class. Below is specifically
what I define for the userRole (what I am getting the error on)
Dim userRole As Integer = radUserList.Sel ectedItem.Value

Admin.StoreAcco untDetails(txtU serName.Text, passwordHash, salt,

tempAnswer,

qstn, userRole)

Here is my Admin.StoreAcco untDetails code:

Dim conn As SqlConnection = New SqlConnection(G etConn.GetCnxSt ring())

Dim cmd As SqlCommand = New SqlCommand("stp CFSPH_CM_RGST_U SER", conn)

cmd.CommandType = CommandType.Sto redProcedure

Dim sqlParam As SqlParameter = Nothing

[ALL OF THE OTHER PARAMS GO HERE, LEFT OUT FOR CLARITY]

sqlParam = cmd.Parameters. Add("@USER_DESC R", SqlDbType.Int, 10)

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQ uery()

Catch ex As Exception

' Code to check for primary key violation (duplicate account name)

' or other database errors omitted for clarity

Throw New Exception("Exce ption adding account. " + ex.Message)

Finally

conn.Close()

End Try

[Gopal \(FMS, Inc.\)]

Do all parameters before USER_DESCR have a value that is initialized (not
nothing)?

"Andy G" <aj*****@iastat e.edu> wrote in message
news:e7******** ******@TK2MSFTN GP10.phx.gbl...

Here is the Stored Proc...

CREATE PROCEDURE dbo.stpCFSPH_CM _RGST_USER

@LOGIN_NAME VARCHAR(255),
@PSWRD VARCHAR(40),
@SALT VARCHAR(10),
@ANSWER VARCHAR(50),
@QSTN_ID INT,
@USER_DESCR INT

AS
SET NOCOUNT ON

DECLARE @PRSN_ID INT

BEGIN
INSERT INTO dbo.tblCFSPH_CM _LOGIN (LOGIN_NAME, PSWRD, SALT, ANSWER, QSTN_ID) VALUES(@LOGIN_N AME, @PSWRD, @SALT, @ANSWER, @QSTN_ID)
END

BEGIN
SELECT @PRSN_ID = SCOPE_IDENTITY( )
END

BEGIN
INSERT INTO dbo.tblCFSPH_CM _PRSN_ROLE_LINK (PRSN_ID, ROLE_ID)
VALUES (@PRSN_ID, @USER_DESCR)
END

"Gopal (FMS, Inc.)" <go**********@f msinc.com> wrote in message
news:Oe******** ******@tk2msftn gp13.phx.gbl...

Can you post SQL Script that defines the stored procedure
'stpCFSPH_CM_RG ST_USER' ?
Thanks
--
Gopal Rangaswamy
Microsoft Certified Solutions Developer
FMS, Inc.
<http://www.fmsinc.com/consulting>
<http://www.fmsinc.com/dotnet/SourceBook/>

"Andy G" <aj*****@iastat e.edu> wrote in message
news:er******** ******@tk2msftn gp13.phx.gbl...

I am getting an error when I try to call my stored procedure. Exception adding account. Procedure 'stpCFSPH_CM_RG ST_USER' expects parameter
'@USER_DESCR', which was not supplied.

I have a radio button list w/ two items, Student w/ a value of 2 and
PrivUser w/ a value of 3. What I do on the click 'Submit' button is set

all

of the variables and pass them to my Admin class. Below is

specifically what I define for the userRole (what I am getting the error on)
Dim userRole As Integer = radUserList.Sel ectedItem.Value

Admin.StoreAcco untDetails(txtU serName.Text, passwordHash, salt,

tempAnswer,

qstn, userRole)

Here is my Admin.StoreAcco untDetails code:

Dim conn As SqlConnection = New SqlConnection(G etConn.GetCnxSt ring())

Dim cmd As SqlCommand = New SqlCommand("stp CFSPH_CM_RGST_U SER", conn)

cmd.CommandType = CommandType.Sto redProcedure

Dim sqlParam As SqlParameter = Nothing

[ALL OF THE OTHER PARAMS GO HERE, LEFT OUT FOR CLARITY]

sqlParam = cmd.Parameters. Add("@USER_DESC R", SqlDbType.Int, 10)

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQ uery()

Catch ex As Exception

' Code to check for primary key violation (duplicate account name)

' or other database errors omitted for clarity

Throw New Exception("Exce ption adding account. " + ex.Message)

Finally

conn.Close()

End Try

[Andy G]

Yes all values have been initialized. I have it working now. I've ran into
problems before declaring parameters. Sometimes a specific way that work
for one stored procedure didn't work for another one. Below is how I reset
all parameters in this class, it works now. Thanks for the help guys.

Dim paramPassHash As New SqlParameter("@ PSWRD", SqlDbType.VarCh ar, 40)

paramPassHash.V alue = passwordHash

cmd.Parameters. Add(paramPassHa sh)

"Gopal (FMS, Inc.)" <go**********@f msinc.com> wrote in message
news:eF******** ******@TK2MSFTN GP09.phx.gbl...

Do all parameters before USER_DESCR have a value that is initialized (not
nothing)?

"Andy G" <aj*****@iastat e.edu> wrote in message
news:e7******** ******@TK2MSFTN GP10.phx.gbl...

Here is the Stored Proc...

CREATE PROCEDURE dbo.stpCFSPH_CM _RGST_USER

@LOGIN_NAME VARCHAR(255),
@PSWRD VARCHAR(40),
@SALT VARCHAR(10),
@ANSWER VARCHAR(50),
@QSTN_ID INT,
@USER_DESCR INT

AS
SET NOCOUNT ON

DECLARE @PRSN_ID INT

BEGIN
INSERT INTO dbo.tblCFSPH_CM _LOGIN (LOGIN_NAME, PSWRD, SALT, ANSWER,

QSTN_ID)

VALUES(@LOGIN_N AME, @PSWRD, @SALT, @ANSWER, @QSTN_ID)
END

BEGIN
SELECT @PRSN_ID = SCOPE_IDENTITY( )
END

BEGIN
INSERT INTO dbo.tblCFSPH_CM _PRSN_ROLE_LINK (PRSN_ID, ROLE_ID)
VALUES (@PRSN_ID, @USER_DESCR)
END

"Gopal (FMS, Inc.)" <go**********@f msinc.com> wrote in message
news:Oe******** ******@tk2msftn gp13.phx.gbl...

Can you post SQL Script that defines the stored procedure
'stpCFSPH_CM_RG ST_USER' ?
Thanks
--
Gopal Rangaswamy
Microsoft Certified Solutions Developer
FMS, Inc.
<http://www.fmsinc.com/consulting>
<http://www.fmsinc.com/dotnet/SourceBook/>

"Andy G" <aj*****@iastat e.edu> wrote in message
news:er******** ******@tk2msftn gp13.phx.gbl...
> I am getting an error when I try to call my stored procedure. Exception > adding account. Procedure 'stpCFSPH_CM_RG ST_USER' expects parameter
> '@USER_DESCR', which was not supplied.
>
> I have a radio button list w/ two items, Student w/ a value of 2 and
> PrivUser w/ a value of 3. What I do on the click 'Submit' button is set all
> of the variables and pass them to my Admin class. Below is specifically > what I define for the userRole (what I am getting the error on)
> Dim userRole As Integer = radUserList.Sel ectedItem.Value
>
> Admin.StoreAcco untDetails(txtU serName.Text, passwordHash, salt,
tempAnswer,
> qstn, userRole)
>
> Here is my Admin.StoreAcco untDetails code:
>
> Dim conn As SqlConnection = New SqlConnection(G etConn.GetCnxSt ring()) >
> Dim cmd As SqlCommand = New SqlCommand("stp CFSPH_CM_RGST_U SER", conn) >
> cmd.CommandType = CommandType.Sto redProcedure
>
> Dim sqlParam As SqlParameter = Nothing
>
> [ALL OF THE OTHER PARAMS GO HERE, LEFT OUT FOR CLARITY]
>
> sqlParam = cmd.Parameters. Add("@USER_DESC R", SqlDbType.Int, 10)
>
> sqlParam.Value = userDescr
>
> Try
>
> conn.Open()
>
> cmd.ExecuteNonQ uery()
>
> Catch ex As Exception
>
> ' Code to check for primary key violation (duplicate account name)
>
> ' or other database errors omitted for clarity
>
> Throw New Exception("Exce ption adding account. " + ex.Message)
>
> Finally
>
> conn.Close()
>
> End Try
>
>
>
>
>
>
>
>
>