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

Question

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

Zohar Peled · Accepted Answer · 2016-04-10 06:15:55Z

1

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:

There is no need to write the update statement in both if and else parts.
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

answered Apr 10, 2016 at 6:15

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diether Silverious Over a year ago

It works like a charm. Sometimes it's really hard for me to explain my problem in programming in English. lol . You're right what i wanted to do is to keep the previous values of the school year and level of each student Thank you for your patience :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related