How to stop inserting records in SQL table when a limit is reached

Question

Hi,

I am using VB.net and SQL Server 2005.

My Problem is that I want to give user a message if the delegate limit has reached for that course.

See I have a course where I have MinDelegate and MaxDelegate limit. I want to stop inserting and give user message that "Max Delegate limit has reached can't have more delegates for this course"

And below is the insert commmand which is inserting records in my delegate table.

ALTER PROCEDURE [dbo].[uspInsertDelegate]
(
    @CourseID int,
    @CPUserID int,
    @StatusID int,
    @CreateUser varchar(25),
    @CourseDate smalldatetime

)
AS
    SET NOCOUNT OFF;
IF NOT EXISTS (SELECT *
        FROM tblDelegate
        WHERE CourseID = @CourseID and CPUserID = @CPUserID)
BEGIN
INSERT INTO tblDelegate                      
(
    CourseID, 
    CPUserID, 
    StatusID, 
    CreateUser 

)
VALUES     
(
    @CourseID,
    @CPUserID,
    @StatusID,
    @CreateUser
)
END

UPDATE tblTraining
SET 
TrainingDT = @CourseDate,
TrainingCompleted = @StatusID
WHERE CPUserID = @CPUserID

RETURN

Please also suggest what do in VB.NET code!

Thanks!

Best Regards,

Yuv

Answer 1

A solution is to write a "before INSERT" trigger which will enforce the business rules by checking how many students are readily registered for the class etc.) and prevent the insert to take place.

This error condition can then be detected at higher levels, and a proper error message provided to the user.

In re-reading your question, the business rule could as well be checked in the stored procedure you have in place, it would then just be a matter of making the store procedure return a condition code (say an integer: 0 = insert ok and -1 = class is full), and to test this value at the level of the application.

Edit: more details (but not quite the full code...)

Yuvraj, this looks seriously like homework, so I'd like to put you on the right track but also let you work at it enough that you learn the process of figuring things out.

With regards to the Store Procedure (SP), bniwredyc readily provided you the code: It is a slight modification compared with what you have in your question:

@minDelegate int,
@maxDelegate int

set @delegatesCount = (select count(*) from tblDelegate 
                       where CourseID = @CourseId)

if (@delegatesCount >= maxDelegate)
        return -1

Essentially you add 2 extra arguments to the procedure: minDelegate and maxDelegate and return prematurely from the procedure, with a -1 return value, in case there are too many delegates. (1 in bniwredyc 's example, but I prefer negative values for error conditions). I don't think minDelegate is used at all; you know better which rules have to applied...

Now, you need to write a VB program which will invoke this SP by way of ADO. This will involve using the ADODB.Command object This Microsoft MSDN page provides reference info about this object and following a few links not too far from this page, you'll also find details about the Connection object and the RecordSet Object.

There are 3 ways that a stored procedure can return some data to the calling method.

    1. By returning an integer value in the RETURN value.  This value is 
       the return value of the Command object's Execute() method. 
       This is the simpler approach and can be used in your case
    2. By returning values (integer, text or other) in one or several OUTPUT
       Parameters
    3. By returning a recordset 

    Method 2 can be combined with 1 or 3, however 1 and 3 are mutually
    exclusive since they both use return value of the Execute() method
    to provide an integer (1) or a Recordset (3).

The example at this page shows +/- all that you will need, but it uses a recordset for the data, which is not necessary in your case; Instead use an integer value for storing the return value of Execute(), and test it. If 0 : Record was added ok, If-1 : Failed the "too many" test.

Now, get to work :-) and please do tag your questions as "Homework" when appropriate.

Answer 2

You could create a validation stored procedure that just returns the total count of records. Call this first from your VB code then you check that count and then return the appropriate error message orelse call the insert sproc your provided.

Answer 3

You can pass a variable to the Stored Procedure with the MaxDelegates and do the check inside the Stored Procedure before inserting

Declare @CurrentNumberOfDelegates int
Select @CurrentNumberOfDelegates = Count(*)
From tblDelegate
Where CourseId = @CourseId

If @CurrentNumberOfDelegates > @MaxDelegates
 Return -1

In this case you will check on VB.Net the return value of the stored procedure and if it is -1 show the message to the user.
This solution should be safe enough because you check the count just before you insert, but you may need to add a transaction to ensure that the limit is never passed by another thread running at the same time.

Answer 4

I think you can use this code for stored procedure:

ALTER PROCEDURE [dbo].[uspInsertDelegate]
    (
        @CourseID int,
        @CPUserID int,
        @StatusID int,
        @CreateUser varchar(25),
        @CourseDate smalldatetime,
        @minDelegate int,
        @maxDelegate int
    )
    AS
        SET NOCOUNT OFF;
    IF NOT EXISTS (SELECT *
            FROM tblDelegate
            WHERE CourseID = @CourseID and CPUserID = @CPUserID)
    BEGIN
        set @delegatesCount = (select count(*) from tblDelegate where CourseID = @CourseId)

        if (@delegatesCount >= maxDelegate)
         return 1
        else
        begin
         INSERT INTO tblDelegate                      
         (
          CourseID, 
          CPUserID, 
          StatusID, 
          CreateUser 
         )
         VALUES     
         (
          @CourseID,
          @CPUserID,
          @StatusID,
          @CreateUser
         )
        end
    END

    UPDATE tblTraining
    SET 
    TrainingDT = @CourseDate,
    TrainingCompleted = @StatusID
    WHERE CPUserID = @CPUserID

    RETURN 0

In VB code just check value that returned by execution of stored procedure: if it's 1 than max delegate limit has reached. You can also add some code to stored procedure to return value 2 in case of min limit have reached.

Answer 5

Hi Guys,

First of all thanks to all members who responded my Question

I solve this problem using below logic

In Sql Procedure I changed my procedure.

ALTER PROCEDURE [dbo].[uspInsertDelegate]
(
    @CourseID int,
    @CPUserID int,
    @StatusID int,
    @CreateUser varchar(25),
    @CourseDate smalldatetime,
    @MaxDelegate int

)
AS
    SET NOCOUNT OFF;
IF NOT EXISTS (SELECT * FROM tblDelegate WHERE CourseID = @CourseID and CPUserID = @CPUserID)
BEGIN
    Declare @DelegateBooked int
    set @DelegateBooked = (SELECT count(*) FROM tblDelegate WHERE CourseID = @CourseID)
    IF @DelegateBooked >= @MaxDelegate
     SELECT 1
    ELSE
    BEGIN
     INSERT INTO tblDelegate                      
     (
      CourseID, 
      CPUserID, 
      StatusID, 
      CreateUser 

     )
     VALUES     
     (
      @CourseID,
      @CPUserID,
      @StatusID,
      @CreateUser
     )

     UPDATE tblTraining
     SET 
     TrainingDT = @CourseDate,
     TrainingCompleted = @StatusID
     WHERE CPUserID = @CPUserID
    END
END

And in my VB.net code I write:

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If Not Session("CourseDate") Is Nothing Then
            Try
                Dim conString As String = WebConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
                Dim con As New SqlConnection(conString)
                con.Open()
                Dim cmd As SqlCommand
                For Each curRow As GridViewRow In GridView1.Rows
                    Dim chkSelect As CheckBox = CType(curRow.Cells(1).FindControl("chkSelect"), CheckBox)
                    If chkSelect.Checked Then
                        cmd = New SqlCommand("uspInsertDelegate", con)
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.Parameters.Add("@CourseID", SqlDbType.Int).Value = Session("CourseID")
                        cmd.Parameters.Add("@CPUserID", SqlDbType.Int).Value = CType(curRow.Cells(1).FindControl("lblCPUserID"), Label).Text
                        cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = 25
                        cmd.Parameters.Add("@CreateUser", SqlDbType.VarChar).Value = Session("LoggedInUser")
                        cmd.Parameters.Add("@CourseDate", SqlDbType.DateTime).Value = Session("CourseDate")
                        cmd.Parameters.Add("@MaxDelegate", SqlDbType.Int).Value = Session("MaxDelegate")
                        Dim retValue As Integer = CType(cmd.ExecuteScalar(), Integer)
                        If retValue = 1 Then
                            lblError.Visible = True
                            lblError.Text = "Max Delegate limit has reached can't have more delegates for this course"
                            Exit For
                        Else
                            lblError.Visible = False
                        End If
                    End If
                Next
                GridView1.DataBind()
            Catch ex As Exception
                ErrorHandler.WriteError(ex.Message)
            End Try
        End If

Please have a look and provide your feedbacks in case it is wrong