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.
