Asp.net Sql Parameter Missing

Question

I am trying to call a stored procedure with a number of parameters but getting the error of

"Procedure or function 'sp_User_Create' expects parameter '@adminUser', which was not supplied."

when I send a value of 0

But when I call the stored procedure with a value greater then 0 it works fine?

Here is my code....

SqlConnection con;
SqlCommand cmd = new SqlCommand();
con = new SqlConnection("server=XXX.XXX.XXX.XXX; database=xxxxxxx; uid=xxxxxxx; pwd=xxxxx");
cmd = new SqlCommand("sp_User_Create", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("fullName", txtFirstName.Text + " " + txtLastName.Text));
cmd.Parameters.Add(new SqlParameter("emailAddress", txtEmailAddress.Text));
cmd.Parameters.Add(new SqlParameter("password", txtPassword.Text));
cmd.Parameters.Add(new SqlParameter("phoneNumber", txtPhoneNumber.Text));
cmd.Parameters.Add(new SqlParameter("jobTitle", txtJobTitle.Text));
cmd.Parameters.Add(new SqlParameter("createDate", DateTime.Now));
cmd.Parameters.Add(new SqlParameter("companyId", 116));
cmd.Parameters.Add(new SqlParameter("adminUser", 0));
cmd.Parameters.Add(new SqlParameter("userExpireDate", DateTime.Now.AddDays(7)));
cmd.Parameters.Add(new SqlParameter("userAccessLeaderboardClips", 99));
cmd.Parameters.Add(new SqlParameter("userAccessLeaderboardUsers", 99));
cmd.Parameters.Add(new SqlParameter("userAccessLeaderboardVideos", 99));
cmd.Parameters.Add(new SqlParameter("userAccessShared", 99));
cmd.Parameters.Add(new SqlParameter("userAccessEmbed", 99));
cmd.Parameters.Add(new SqlParameter("userAccessTinyUrl", 1));
cmd.Parameters.Add(new SqlParameter("userAccessAllowCreatedVideos", 1));
cmd.Parameters.Add(new SqlParameter("userAccessDownload", 99));
cmd.Parameters.Add(new SqlParameter("companyName", "My Company Name"));
cmd.Parameters.Add(new SqlParameter("logoName", "the logo name"));
cmd.Parameters.Add(new SqlParameter("postcode", "XXXX XXX"));
cmd.Parameters.Add(new SqlParameter("optIn", true));
cmd.Parameters.Add(new SqlParameter("accountType", ""));
cmd.Parameters.Add(new SqlParameter("transactionDesc", ""));
cmd.Parameters.Add(new SqlParameter("transactionDate", ""));
cmd.Parameters.Add(new SqlParameter("transactionAmount", ""));

con.Open();
cmd.ExecuteNonQuery();
con.Close();

I have checked the stored procedure and it's expecting:

@adminUser as int

Here is the stored procedure:

USE [vCreateDev]
GO
/****** Object:  StoredProcedure [dbo].[sp_User_Create]    Script Date: 29/08/2013 10:47:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Trevor Daniel
-- Create date: 28/8/2013
-- Description: Create a user
-- =============================================
ALTER PROCEDURE [dbo].[sp_User_Create]

-- UserLogin Fields
@fullName as varchar(100),
@emailAddress as varchar(100),
@password as varchar(20),
@phoneNumber as varchar(100),
@jobTitle as varchar,
@createDate as datetime,
@companyId as int,
@adminUser as int,
@userExpireDate as datetime,
@userAccessLeaderboardClips as int,
@userAccessLeaderboardUsers as int,
@userAccessLeaderboardVideos as int,
@userAccessShared as int,
@userAccessEmbed as int,
@userAccessTinyUrl as int,
@userAccessAllowCreatedVideos as int,
@userAccessDownload int,

-- ClientCompanyDetailsPerUser
@companyName varchar(200),
@logoName varchar(200),

-- UserCustomerDetails
@postcode varchar(15),
@optIn bit,
@accountType varchar(50),

-- UserTransactions
@transactionDesc varchar(150),
@transactionDate datetime,
@TransactionAmount smallmoney

-- Outputs
--@UserLoginID INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO UserLogin(
Username,
Password,
AuthID,
DateCreated,
LastLogin,
Fullname,
Title,
Mobile,
DDI,
Email,
AdminUser,
CompanyID,
UserExpireDate,
CurrentLogin,
LastActionDateTime,
ActiveLicense,
UserAccessLeaderboardClips,
UserAccessLeaderboardUsers,
UserAccessLeaderboardVideos, 
UserAccessShared, 
UserAccessEmbed,
UserAccessTinyUrl,
UserAccessAllowCreatedVideos,
UserAccessDownload)
VALUES(
@emailAddress,
@password,
null,
@createDate,
null,
@fullName,
@jobTitle,
@phoneNumber,
null,
@emailAddress,
@adminUser,
@companyId,
@userExpireDate,
null,
null,
null,
@userAccessLeaderboardClips,
@userAccessLeaderboardUsers,
@userAccessLeaderboardVideos,
@userAccessShared,
@userAccessEmbed,
@userAccessTinyUrl,
@userAccessAllowCreatedVideos,
@userAccessDownload)

DECLARE @NewUserID as int

SET @NewUserID = SCOPE_IDENTITY()

INSERT INTO ClientCompanyDetailsPerUser(CompanyName, CompanyLogoLocation, LargeCompanyLogoLocation, UserID)
VALUES (@companyName,@logoName,@logoName,@NewUserID)

INSERT INTO UserCustomerDetails(UserID, PostCode, OptIn, AccountType)
VALUES (@NewUserID,@postcode,@optIn,@accountType)

INSERT INTO UserTransactions(UserID, TransactionDesc, TransactionDate, TransactionAmount, AccountType, ResellerParameter, CampaignParameter)
VALUES (@NewUserID,@transactionDesc,@transactionDate,@TransactionAmount,@accountType,NULL,NULL)

END

I just cannot understand why when a zero is sent it says the parameter is missing but when I send something greater then zero it works perfectly?

Is it just the adminUser parameter or any other int parameters behaving the same way?? — Nilesh, Aug 30 '13 at 09:47
Side-note: even if it works without you should add the `@`-sign at the beginning of your named parameters. According to the [documentation](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.parametername.aspx) it is recommended even if the ADO.NET code adds it if it's missing. Since it is an undocumented feature it might be removed in future. So instead of `new SqlParameter("fullName" ...` this: `new SqlParameter("@fullName" ...`. http://stackoverflow.com/a/10245581/284240 — Tim Schmelter, Aug 30 '13 at 09:47
the adminuser is the first one to cause a problem. The problem also occurs if I change the CompanyID from 116 to 0 — Trevor Daniel, Aug 30 '13 at 09:54
@TrevorDaniel Try to run your query in `SSMS` with values for `adminUser` and `CompanyID` as `0` — Suraj Singh, Aug 30 '13 at 10:50

Suraj Singh · Accepted Answer · 2013-08-30T11:18:00.120

Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero.

If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameter (string, SqlDbType) constructor overload.-MSDN

Try using this

cmd.Parameters.Add(new SqlParameter("@adminUser", SqlDbType.Int).Value=0);

score 0 · Answer 2 · answered Aug 30 '13 at 09:52

0

you need to add "@" just before parameter name like this for all parameter you are passing to procedure

cmd.Parameters.Add(new SqlParameter("@adminUser", 0));

answered Aug 30 '13 at 09:52

Sain Pradeep

3,119
1
22
31

Thanks for the suggestion. I just tried adding the @ sign in front of all the parameter names and I am still getting the Procedure or function 'sp_User_Create' expects parameter '@adminUser', which was not supplied error :( – Trevor Daniel Aug 30 '13 at 10:01

score 0 · Answer 3 · answered Aug 30 '13 at 10:29

0

BINGO! - fixed it.

I have changed the code to this:

cmd.Parameters.AddWithValue("@adminUser", 0);

Thanks for looking and help!

Trev

answered Aug 30 '13 at 10:29

Trevor Daniel

3,785
12
53
89

score 0 · Answer 4 · answered Aug 30 '13 at 11:34

0

you need to put @ symbol before every parameter name like you have added in
cmd.Parameters.AddWithValue("@adminUser", 0); for cmd.Parameters.Add(new SqlParameter("adminUser", 0));

answered Aug 30 '13 at 11:34

Gemini

89
1

Asp.net Sql Parameter Missing

4 Answers4