I'm still new to programming and I was given the task of making a database application for the members of our organization using VB.NET and SQL Server. I have 3 tables: Person, Telephone and Employment. Person has the primary key that I set to autoincrement and it has a one-to-one relationship with Telephone and Employment. When entering data, Telephone and Employment can be optional since not everyone has a telephone number and a member of the organization can be unemployed sometime. I'm using stored procedure to insert data and this is my code:
USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertPerson]
@FirstName nvarchar(50),
@MiddleName nvarchar(50),
@LastName nvarcahr(50),
@IsMale bit,
@BirthDate date,
@CivilStatus nvarchar(13),
@Address nvarchar(100),
@TelephoneNumber nvarchar (12),
@Industry nvarchar(20),
@IsGovernment bit,
@WorkPlace nvarchar(100),
@JobStatus nvarchar(13),
@Salary decimal (9,2)
AS
declare @PersonId int
INSERT INTO Person(FirstName, MiddleName, LastName, IsMale, BirthDate, CivilStatus, Address) VALUES (@FirstName, @MiddleName, @ LastName, @IsMale, @BirthDate, @CivilStatus, @Address)
SELECT @PersonId = SCOPE_IDENTITY()
/*I used IF STATEMENTS to skip INSERT code in case a person has no telephone number or unemployed*/
IF (@TelephoneNumber IS NOT NULL) BEGIN
INSERT INTO Telephone(TelephonePersonId, TelephoneNumber)
VALUES(@PersonId, @TelephoneNumber)
END
IF (@Industry IS NOT NULL AND @IsGovernment IS NOT NULL AND @WorkPlace IS NOT NULL AND @JobStatus IS NOT NULL AND @Salary IS NOT NULL) BEGIN
INSERT INTO Employment(EmpId, Industry, IsGovernment, WorkPlace, JobStatus, Salary)
VALUES(@PersonId, @Industry, @IsGovernment, @WorkPlace, @JobStatus, @Salary)
END
This is the code I used in the application using VB.NET :
Dim sex as Boolean
Dim isGovt as Boolean
sex = IIf(optMale.Checked, 1, 0)
isGovt = IIf(optGyes.Checked,1,0)
Dim strCon As String = "my connection"
Dim sqlCon As SqlConnection
sqlCon = New SqlConnection(strCon)
Using (sqlCon)
Dim sqlComm As New SqlCommand
sqlComm.Connection = sqlCon
sqlComm.CommandText = "InsertPerson"
sqlComm.CommandType = CommandType.StoredProcedure
sqlComm.Parameters.AddWithValue("FistName", txtFirstName.Text)
sqlComm.Parameters.AddWithValue("MiddleName", txtMiddleName.Text)
sqlComm.Parameters.AddWithValue("LastName", txtLastName.Text)
sqlComm.Parameters.AddWithValue("IsMale", sex)
sqlComm.Parameters.AddWithValue("BirthDate", dtpBirthDate.Value.Date)
sqlComm.Parameters.AddWithValue("CivilStatus", cboCivilStatus.Text)
sqlComm.Parameters.AddWithValue("Address", txtAddress.Text)
/*I used a checkbox to enable or disable the textbox for telephone number*/
If chkTelephone.Checked = True Then
sqlComm.Parameters.AddWithValue("TelephoneNumber", txtTelephone.Text)
End If
/*Here I used a radiobutton to enable or disable the objects related to a person's employment*/
If optEmployed.Checked = True Then
sqlComm.Parameters.AddWithValue("Industry", cboIndustry.Text)
sqlComm.Parameters.AddWithValue("IsGovernment", isGovt)
sqlComm.Parameters.AddWithValue("WorkPlace", txtWorkPlace.Text)
sqlComm.Parameters.AddWithValue("JobStatus", cboJobStatus.Text)
sqlComm.Parameters.AddWithValue("Salary", txtSalary.Text)
Now when I run the application and click on the save button I get this error:
SqlException was unhandled
Procedure or function 'InsertPerson' expects parameter '@TelephoneNumber', which was not supplied.
Can you tell me where I went wrong? Is there another way to do this? I would really appreciate any help, link, or advice you can give me.
In your stored procedure you need to assign default values to the parameters that you are not going to give when you run the stored procedure
In this case for TelephoneNumber you need to give default value OR In your code you can pass default value for such parameters
CREATE PROCEDURE [dbo].[InsertPerson]
..
..
@Address nvarchar(100),
@TelephoneNumber nvarchar (12) = NULL
In SQL you can call such a stored procedure like below
exec [dbo].[InsertPerson] .. @Address='123 NY', @Industry ='test'