CODEWITHSUNDEEP
sql-servervb.netsql-server-2008-r2
Diether Silverious
Diether Silverious

Reputation: 199

How to check if record exist using vb.net and SQL Server

Assuming that I have two tables Historytable and studentInformation table.

What I would like to do is this:

If schoolyear and level exist in Historytable
    update studentInformationtable
else
   INSERT schoolyear,level to Historytable
    update studentInformationtable

My stored procedure code:

ALTER PROCEDURE [dbo].[InsertUpdate] 
    @studentID INT, 
    @SchoolYear Nvarchar(20),
    @levels Nvarchar(20),
    @FirstName Nvarchar(20),
    @SurName Nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

BEGIN TRAN
    IF exists (SELECT SchoolYear, Levels
               FROM StudentHistory WITH (updlock, serializable) 
               WHERE StudentID = @studentID)
    BEGIN
        UPDATE StudentInformation
        SET FirstName = @FirstName ,
            Surname = @SurName
        WHERE StudentID = @studentID
    END
    ELSE
    BEGIN
        INSERT INTO Studenthistory (StudentID, SchoolYear, Levels)
        VALUES (@studentID, @SchoolYear, @levels)

        UPDATE StudentInformation
        SET FirstName = @FirstName,
            Surname = @SurName
        WHERE StudentID = @studentID
    END

    COMMIT TRAN
END

vb.net code

Using cmd As New SqlClient.SqlCommand("dbo.InsertUpdate", cn)
    cmd.Parameters.AddWithValue("@studentID", frmView.dgv1.SelectedCells(0).Value)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@SurName", SqlDbType.VarChar, 100).Value = txtStudLN.Text
    cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 100).Value = txtStudFN.Text
    cmd.Parameters.Add("@SchoolYear", SqlDbType.VarChar, 100).Value = cboSchoolYear.Text
    cmd.Parameters.Add("@levels", SqlDbType.VarChar, 100).Value = cboGradeLevel.Text
    cmd.ExecuteNonQuery()
    MsgBox("Update New record successfully")
End Using

Problem: it does not INSERT a new record in my history table when I insert a new schoolyear and level. Can someone help me to fix my code? Thanks

Upvotes: 2

Views: 1603

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82534

Try this instead:

ALTER PROCEDURE [dbo].[InsertUpdate] 
    -- Add the parameters for the stored procedure here

@studentID INT, 
@SchoolYear Nvarchar(20),
@levels Nvarchar(20),
@FirstName Nvarchar(20),
@SurName Nvarchar(20)


AS
BEGIN

    SET NOCOUNT ON;

BEGIN TRAN

   UPDATE StudentInformation
   SET FirstName = @FirstName ,
   Surname = @SurName
   WHERE StudentID = @studentID

   INSERT INTO Studenthistory (StudentID,SchoolYear, Levels)
   SELECT @studentID,@SchoolYear,@levels
   WHERE NOT EXISTS(
        SELECT 1
        FROM Studenthistory
        WHERE StudentID = @studentID
   )


COMMIT TRAN
END

Notes:

  1. There is no need to write the update statement in both if and else parts.
  2. The insert statement will add a new record to the Studenthistory only if the select statement will return records. The condition in the where clause makes sure that only one record per student will be inserted into the history table.

However, I don't see much point in keeping just one historical record for each student. I think you mean you want to keep the previous values of the school year and levels of each student. If this is true, the query inside the exists should be this:

SELECT 1
FROM Studenthistory
WHERE StudentID = @studentID
AND SchoolYear = @SchoolYear,
AND Levels = @levels

Upvotes: 1

Related Questions