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/>

 

[Guest]

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]

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

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/>

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

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

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/>

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)

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

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

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/>

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