Record inserts into wrong table. Any ideas why and how to fix it?

Question

This is very weird to me.

I tried to get the stored proc version of this code but with no success.

This is a Training app. Users must first register to create an account.

Then they use their accounts to sign up for class.

First the code checks to see if this user has already signed up for a particular class.

If no, then sign the user up. If yes, notify that user that s/he has already signed up.

This bit works.

If the user has not signed and is trying to sign up, check to see if there are still seats available. If yes, sign the user up. If no more seats are available, put the user on waiting list by inserting into a table called tblWaitinglist.

So far, each time I attemp to insert a new record, it says, class is full, user is being put on waiting list.

This is not true. Class is completely empty.

There are a total of 45 seats.

So far, all seats are available.

What the heck am I doing wrong?

It is not even inserting into waitinglist. It just gives the message that it did.

Please see my code below and thanks a lot in advance.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    Dim username = Session("Username")
    Dim connStr As String = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString
    Dim conn As New SqlConnection(connStr)
    conn.Open()
    Try

        Dim s As String
        Dim counter As Integer

        'If user already registered for a class, alert user
        s = "SELECT Count(*) FROM tblTrainings WHERE Username = '" & username & "' AND CourseID = " & Request.QueryString("cosId") & "  AND LocationID = " & Request.QueryString("locid") & " AND dateId = " & Request.QueryString("iddate") & ""
        'Response.Write(s)
        'Response.End()
        Dim connSt As String = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString
        Dim connc As New SqlConnection(connSt)
        Dim cmdc As New SqlCommand(s, connc)
        connc.Open()
        cmdc.ExecuteNonQuery()
        counter = cmdc.ExecuteScalar()


        '   Now let's see if we found existing record of registration
        If counter = 0 Then 'User has not registered for this training. In that case, check to see there are still seats available.
            Dim SeatsAvailable As Integer
            SeatsAvailable = 0

            s = " SELECT SeatsAvailable = (Select Seating_Capacity  - (Select count(*) from tblTrainings where courseId = @cosId) from tblLocations WHERE LocationId = @Locid)"
            'Response.Write(s)
            'Response.End()
            Dim cmdB As New SqlCommand(s, conn)
            cmdB.Parameters.AddWithValue("@cosID", Request.QueryString("cosId"))
            cmdB.Parameters.AddWithValue("@locID", Request.QueryString("locid"))
            cmdB.ExecuteNonQuery()


            If SeatsAvailable > 0 Then 'Ok there are still seats available. Sign this user up.
                s = "INSERT INTO tblTrainings (CourseId, LocationId, dateId,username) VALUES (@CosID, @LocID, @dat, @Username)"
                Dim cmd = New SqlCommand(s, conn)
                cmd.Parameters.AddWithValue("@cosID", Request.QueryString("cosId"))
                cmd.Parameters.AddWithValue("@locID", Request.QueryString("locid"))
                cmd.Parameters.AddWithValue("@dat", Request.QueryString("iddate"))
                cmd.Parameters.AddWithValue("@UserName", username)
                'Response.Write(s)
                'Response.End()
                cmd.ExecuteNonQuery()
                Dim cmdGetKey As New SqlCommand("SELECT @@IDENTITY", conn)
                Dim skey As Integer = cmdGetKey.ExecuteScalar()
                Session("TrainingId") = skey
                conn.Close()
                confirmRegistraction()
                Label1.ForeColor = System.Drawing.Color.Red
                Label1.Text = "Congratulations! You have been registered for this class. Please check your email inbox for details"
            Else 'No seats remain. So, put user on waiting list
                s += "INSERT INTO tblWaitingList (CourseId, LocationId, dateId,username) VALUES (@CosID, @LocID, @dat, @Username)"
                Dim cmd = New SqlCommand(s, conn)
                cmd.Parameters.AddWithValue("@cosID", Request.QueryString("cosId"))
                cmd.Parameters.AddWithValue("@locID", Request.QueryString("locid"))
                cmd.Parameters.AddWithValue("@dat", Request.QueryString("iddate"))
                cmd.Parameters.AddWithValue("@UserName", username)
                'Response.Write(s)
                'Response.End()
                cmd.ExecuteNonQuery()
                Dim cmdGetKey As New SqlCommand("SELECT @@IDENTITY", conn)
                Dim skey As Integer = cmdGetKey.ExecuteScalar()
                Session("TrainingId") = skey
                conn.Close()
                onWaitingList()
                'Display some feedback to the user to let them know it was processed
                Label1.ForeColor = System.Drawing.Color.Red
                Label1.Text = "Sorry, but this class is full. However, you have been placed on waiting list."
            End If

        Else
            'Alert user that s/he has already registered for this class
            Label1.ForeColor = System.Drawing.Color.Red
            Label1.Text = "You have already signed up for this training."
        End If

    Catch

        'If the message failed at some point, let the user know
        Label1.ForeColor = System.Drawing.Color.Red
        Label1.Text = "Your record failed to save, please try again."

    End Try
End Sub

Answer 1

It looks like you are never setting SeatsAvailable to anything other than zero. Your s variable is probably what you want to be looking at.

The failure to INSERT is another issue altogether.