Click here to Skip to main content
15,845,742 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
System.Data.SqlClient.SqlException: 'Procedure or function 'EmployeeAdd' expects parameter '@CommenceDate', which was not supplied.'



Below is all the codes:
SQL Table:
CREATE TABLE [dbo].[tbl_EmployeeAdd](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Surname] [nvarchar](50) NULL,
	[Department] [nvarchar](50) NULL,
	[EmploymentType] [nvarchar](50) NULL,
	[CommenceDate] [date] NULL,
	[JobTitle] [nvarchar](50) NULL,
	[TelephoneExt] [nchar](10) NULL,
	[Gender] [nvarchar](10) NULL,
	[Workstations] [nvarchar](10) NULL,
	[BusinessCentral] [nvarchar](15) NOT NULL,
	[CPAR] [nvarchar](15) NOT NULL,
	[WineMS] [nvarchar](15) NOT NULL,
	[OperaPMS] [nvarchar](15) NOT NULL,
	[MyMicros] [nvarchar](15) NOT NULL,
	[MicrosEMC] [nvarchar](15) NOT NULL,
	[JetProfessional] [nvarchar](15) NOT NULL,
	[FolderAccessRights] [nvarchar](250) NULL,
	[EmailGroupAccess] [nvarchar](250) NULL,
	[Authorisedby] [nvarchar](50) NULL
) ON [PRIMARY]
GO


Stored Procedure:

USE [NewUserReqForm]
GO

/****** Object:  StoredProcedure [dbo].[EmployeeAdd]    Script Date: 2020/04/28 16:32:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROC [dbo].[EmployeeAdd]

@Name nvarchar(50),
@Surname nvarchar(50),
@Department nvarchar(50),
@EmploymentType nvarchar(50),
@CommenceDate date,
@JobTitle nvarchar(50),
@TelephoneExt nchar(10),
@Gender nvarchar(10),
@Workstations nvarchar(10),
@BusinessCentral nvarchar (15),
@CPAR nvarchar (15),
@WineMS nvarchar (15),
@OperaPMS nvarchar (15), 
@MyMicros nvarchar (15), 
@MicrosEMC nvarchar (15),
@JetProfessional nvarchar (15),
@FolderAccessRights nvarchar(250),
@EmailGroupAccess nvarchar(250),
@Authorisedby nvarchar(50)


AS 
	INSERT INTO tbl_EmployeeAdd (
								Name,Surname,Department,EmploymentType,CommenceDate,JobTitle,TelephoneExt,Gender,
								Workstations,BusinessCentral,CPAR,WineMS,OperaPMS,MyMicros,MicrosEMC,JetProfessional,FolderAccessRights,EmailGroupAccess,Authorisedby							
	) 
	VALUES (@Name,@Surname,@Department,@EmploymentType,@CommenceDate,@JobTitle,@TelephoneExt,@Gender,@Workstations,
			@BusinessCentral,@CPAR,@WineMS,@OperaPMS,@MyMicros,@MicrosEMC,@JetProfessional,@FolderAccessRights,@EmailGroupAccess,@Authorisedby) 
GO


C# , Visual Studio Code:

sqlCon.Open();

               SqlCommand SqlCmd = new SqlCommand("EmployeeAdd", sqlCon);
               SqlCmd.CommandType = CommandType.StoredProcedure;
               SqlCmd.Parameters.AddWithValue("@Name", txtName.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@Surname", txtSurname.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@Department", txtDepartment.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@EmploymentType", ddlEmploymentType.Text.Trim());

               SqlCmd.Parameters.Add(new SqlParameter("@CommenceDate", SqlDbType.DateTime) { Value = date });
               SqlCmd.Parameters.AddWithValue("@JobTitle", txtJobTitle.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@TelephoneExt", txtTelephoneExt.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@Gender", ddlGender.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@Workstations", ddlWorkstations.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@BusinessCentral", txtBC.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@CPAR", txtCPAR.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@WineMS", txtWineMS.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@OperaPMS", txtOpera.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@MyMicros", txtMyMicros.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@MicrosEMC", txtMicrosEMC.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@JetProfessional", txtJet.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@FolderAccessRights", txtFolderAccessRights.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@EmailGroupAccess", ddlGender.Text.Trim());
               SqlCmd.Parameters.AddWithValue("@Authorisedby", txtAuth.Text.Trim());
               SqlCmd.ExecuteNonQuery();
               Clear();
               lblSuccessMessage.Text = "Submitted Successfully";



           }
       }

       void Clear()
       {
           txtName.Text = txtSurname.Text = txtDepartment.Text = Cal1.Text = ddlEmploymentType.Text = txtJobTitle.Text = txtTelephoneExt.Text = ddlGender.Text = txtBC.Text = txtWineMS.Text = txtOpera.Text = txtMyMicros.Text = txtMicrosEMC.Text = txtJet.Text = txtFolderAccessRights.Text = txtEmailGroupRights.Text = txtAuth.Text ="";
           hfEmployeeID.Value = "";
           lblSuccessMessage.Text = lblErrorMessage.Text = "";

       }

       protected void Calendar1_SelectionChanged(object sender, EventArgs e)
       {
           if (!IsPostBack)
           {

           }
       }
       protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
       {

           if (CommenceDate.Visible == false)
           {
               CommenceDate.Visible = true;

           }
           else
           {
               CommenceDate.Visible = false;
           }
           CommenceDate.Attributes.Add("style", "position:absolute");
       }

       protected void Calendar1_SelectionChanged1(object sender, EventArgs e)
       {
           Cal1.Text = CommenceDate.SelectedDate.ToString("dd/MM/yyyy");
           CommenceDate.Visible = false;
       }


What I have tried:

I am new to this, but i have tried this code for the Commence date
SqlCmd.Parameters.AddWithValue("@EmploymentType", ddlEmploymentType.Text.Trim());
Posted
Updated 29-Apr-20 7:33am

1 solution

The error message you posted is indicative of the parameter is not being added.

While you do have this line trying to add the value to the command; it may not add the parameter IF the variable's value is NULL.
C#
SqlCmd.Parameters.Add(new SqlParameter("@CommenceDate", SqlDbType.DateTime) { Value = date });
 
Share this answer
 
Comments
SuperJWP 29-Apr-20 12:34pm    
What do you mean, can you be more clearer please
MadMyche 29-Apr-20 12:41pm    
It means that "value" is null, so the parameter is not being passed in
#realJSOP 29-Apr-20 13:45pm    
it might also be that he's specified a date that is earlier than sql's minimum valid date

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900