Expected parameter error

[Andy G]

I am getting an error when I try to call my stored procedure. Exception
adding account. Procedure 'stpCFSPH_CM_RGST_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.SelectedItem.Value

Admin.StoreAccountDetails(txtUserName.Text, passwordHash, salt, tempAnswer,
qstn, userRole)

Here is my Admin.StoreAccountDetails code:

Dim conn As SqlConnection = New SqlConnection(GetConn.GetCnxString())

Dim cmd As SqlCommand = New SqlCommand("stpCFSPH_CM_RGST_USER", conn)

cmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As SqlParameter = Nothing

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

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

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

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

' or other database errors omitted for clarity

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

Finally

conn.Close()

End Try

[Gopal \(FMS, Inc.\)]

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

"Andy G" <aj*****@iastate.edu> wrote in message
news:er**************@tk2msftngp13.phx.gbl...

I am getting an error when I try to call my stored procedure. Exception
adding account. Procedure 'stpCFSPH_CM_RGST_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.SelectedItem.Value

Admin.StoreAccountDetails(txtUserName.Text, passwordHash, salt, tempAnswer, qstn, userRole)

Here is my Admin.StoreAccountDetails code:

Dim conn As SqlConnection = New SqlConnection(GetConn.GetCnxString())

Dim cmd As SqlCommand = New SqlCommand("stpCFSPH_CM_RGST_USER", conn)

cmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As SqlParameter = Nothing

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

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

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

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

' or other database errors omitted for clarity

Throw New Exception("Exception 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_DESCR", 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_RGST_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.SelectedItem.Value

Admin.StoreAccountDetails(txtUserName.Text, passwordHash, salt, tempAnswer,
qstn, userRole)

Here is my Admin.StoreAccountDetails code:

Dim conn As SqlConnection = New SqlConnection(GetConn.GetCnxString())

Dim cmd As SqlCommand = New SqlCommand("stpCFSPH_CM_RGST_USER", conn)

cmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As SqlParameter = Nothing

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

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

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

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

' or other database errors omitted for clarity

Throw New Exception("Exception 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_NAME, @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**********@fmsinc.com> wrote in message
news:Oe**************@tk2msftngp13.phx.gbl...

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

"Andy G" <aj*****@iastate.edu> wrote in message
news:er**************@tk2msftngp13.phx.gbl...

I am getting an error when I try to call my stored procedure. Exception
adding account. Procedure 'stpCFSPH_CM_RGST_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.SelectedItem.Value

Admin.StoreAccountDetails(txtUserName.Text, passwordHash, salt,

tempAnswer,

qstn, userRole)

Here is my Admin.StoreAccountDetails code:

Dim conn As SqlConnection = New SqlConnection(GetConn.GetCnxString())

Dim cmd As SqlCommand = New SqlCommand("stpCFSPH_CM_RGST_USER", conn)

cmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As SqlParameter = Nothing

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

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

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

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

' or other database errors omitted for clarity

Throw New Exception("Exception 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*****@iastate.edu> wrote in message
news:e7**************@TK2MSFTNGP10.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_NAME, @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**********@fmsinc.com> wrote in message
news:Oe**************@tk2msftngp13.phx.gbl...

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

"Andy G" <aj*****@iastate.edu> wrote in message
news:er**************@tk2msftngp13.phx.gbl...

I am getting an error when I try to call my stored procedure. Exception adding account. Procedure 'stpCFSPH_CM_RGST_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.SelectedItem.Value

Admin.StoreAccountDetails(txtUserName.Text, passwordHash, salt,

tempAnswer,

qstn, userRole)

Here is my Admin.StoreAccountDetails code:

Dim conn As SqlConnection = New SqlConnection(GetConn.GetCnxString())

Dim cmd As SqlCommand = New SqlCommand("stpCFSPH_CM_RGST_USER", conn)

cmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As SqlParameter = Nothing

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

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

sqlParam.Value = userDescr

Try

conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

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

' or other database errors omitted for clarity

Throw New Exception("Exception 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.VarChar, 40)

paramPassHash.Value = passwordHash

cmd.Parameters.Add(paramPassHash)

"Gopal (FMS, Inc.)" <go**********@fmsinc.com> wrote in message
news:eF**************@TK2MSFTNGP09.phx.gbl...

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

"Andy G" <aj*****@iastate.edu> wrote in message
news:e7**************@TK2MSFTNGP10.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_NAME, @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**********@fmsinc.com> wrote in message
news:Oe**************@tk2msftngp13.phx.gbl...

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

"Andy G" <aj*****@iastate.edu> wrote in message
news:er**************@tk2msftngp13.phx.gbl...
> I am getting an error when I try to call my stored procedure. Exception > adding account. Procedure 'stpCFSPH_CM_RGST_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.SelectedItem.Value
>
> Admin.StoreAccountDetails(txtUserName.Text, passwordHash, salt,
tempAnswer,
> qstn, userRole)
>
> Here is my Admin.StoreAccountDetails code:
>
> Dim conn As SqlConnection = New SqlConnection(GetConn.GetCnxString()) >
> Dim cmd As SqlCommand = New SqlCommand("stpCFSPH_CM_RGST_USER", conn) >
> cmd.CommandType = CommandType.StoredProcedure
>
> Dim sqlParam As SqlParameter = Nothing
>
> [ALL OF THE OTHER PARAMS GO HERE, LEFT OUT FOR CLARITY]
>
> sqlParam = cmd.Parameters.Add("@USER_DESCR", SqlDbType.Int, 10)
>
> sqlParam.Value = userDescr
>
> Try
>
> conn.Open()
>
> cmd.ExecuteNonQuery()
>
> Catch ex As Exception
>
> ' Code to check for primary key violation (duplicate account name)
>
> ' or other database errors omitted for clarity
>
> Throw New Exception("Exception adding account. " + ex.Message)
>
> Finally
>
> conn.Close()
>
> End Try
>
>
>
>
>
>
>
>
>