IF (SELECT COUNT(*) FROM Table) > 60 problem

In a registration form I need to change a Session variable depending on how many records have been entered into the database.

The variable needs to be one value if the number is 60 or less,
another value if the number of records is between 61 and 120,
another if between 121 and 180 and something else if 181 or more.

In another registration form I've used the technique below to change the value if the number of records exceeds a certain value, but this only works with one criteria.  I don't know how to formulate the "CASE WHEN" that this probably requires.  Some assistance with this would be greatly appreciated.  :-)
Protected Sub CountApplications()
    Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("JobfairsConnectionString1").ConnectionString)
    Dim DBCmd As New SqlCommand
    DBConn.Open()

    Try
        DBCmd = New SqlCommand("IF (SELECT COUNT(ApplicantID) FROM Applicants ) > 60 SELECT -1 as error  FROM Applicants", DBConn) ' ELSE BEGIN INSERT INTO Applicants (householdIncomeID) VALUES (@ddlHouseholdIncome); Select SCOPE_IDENTITY() End", DBConn)
        Dim priKey As Integer = DBCmd.ExecuteScalar()
        If priKey < 0 Then
            Session("EventTime") = "10/6/2010 12:45:00 AM"
        Else
            Session("EventTime") = "10/6/2010 9:45:00 AM"
        End If
    Catch exp As Exception
        Response.Write(exp.Message)
    End Try

    'Close Database connection 
    DBCmd.Dispose()
    DBConn.Close()
    DBConn = Nothing
End Sub

Open in new window

LVL 1
megninAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:

DBCmd = New SqlCommand("SELECT COUNT(ApplicantID) AS MyTotal FROM Applicants", DBConn)
Dim reader As SqlDataReader = DBCmd.ExecuteReader()

reader.read()

Dim lCount As Integer = reader("MyTotal")


Select Case lCount
    Case Is <= 22
        Session("Date") = " October 6, 2010 at 9:45 AM "
        Session("EventTime") = "10/6/2010 9:45:00 AM"

    Case Is > 22 And lCount <= 23
        Session("Date") = " October 6, 2010 at 12:45 AM "
        Session("EventTime") = "10/6/2010 12:45:00 AM"

    Case Is > 23 And lCount <= 24
        Session("Date") = " October 6, 2010 at 2:45 AM "
        Session("EventTime") = "10/6/2010 2:45:00 AM"

    Case Else
        Session("Date") = " I'm sorry.  It seems this workshop has been filled. "
        Session("EventTime") = " Please check back in two weeks for the November schedule. "

End Select

Open in new window

0
 
vdr1620Commented:
Try something like below

IF (SELECT COUNT(ApplicantID) FROM Applicants ) <= 60
---condition
IF (SELECT COUNT(ApplicantID) FROM Applicants ) between 61 and 120
--- Condition
IF (SELECT COUNT(ApplicantID) FROM Applicants ) between 121 and 180
--- Condition
IF (SELECT COUNT(ApplicantID) FROM Applicants ) > 181
----Condition
0
 
megninAuthor Commented:
Thank you for the reply.  Please bear with me; I'm relatively inexperienced.  I'm not sure how to get all the conditions into a single SqlCommand(" ") or set up the If/Then that would follow.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
rockiroadsCommented:
Why dont you just do one query

SELECT Count(ApplicantID) AS MyTotal FROM Applicants

then using the value returned, set the value of the session variable according to the value of MyTotal
0
 
AmmarRCommented:
select
'Noofrecords' = case
when count(*) <= 60 then
'60'
when (count(*) > 60 and count(*) <= 120) then
'between 61 and 120'
when (count(*) > 120 and count(*) <= 180) then
'between 121 and 180'
end
from table1
0
 
JoeNuvoCommented:
all of this can type into 1 line command
I just put multiple line for easy to see

Declare @cnt int
SELECT @cnt = count(*) FROM Applicants
IF @cnt <= 60
BEGIN
INSERT INTO Applicants (householdIncomeID) VALUES (@ddlHouseholdIncome)
Select SCOPE_IDENTITY()
END
ELSE IF @cnt < 120 SELECT -1 as Error
ELSE SELECT -2 as Error

note : must uses IF ... ELSE...
CASE in sql is for select, not to perform any statement
0
 
rockiroadsCommented:
Im a little confused with what your trying to do with the insert you got later

But try break it up to individual commands to make it easier

eg

        DBCmd = New SqlCommand("SELECT COUNT(ApplicantID) AS MyTotal FROM Applicants")
        Dim reader As SqlDataReader = DBCmd.ExecuteReader()
        lCount = reader("MyTotal")

Now based on lCount do what you need to do
0
 
megninAuthor Commented:
Sorry JoeNuvo and rockiroads, I forgot to remove the part I have commented out.
DBCmd = New SqlCommand("IF (SELECT COUNT(ApplicantID) FROM Applicants ) > 60 SELECT -1 as error  FROM Applicants", DBConn) ' ELSE BEGIN INSERT INTO Applicants (householdIncomeID) VALUES (@ddlHouseholdIncome); Select SCOPE_IDENTITY() End", DBConn)

should just read:
DBCmd = New SqlCommand("IF (SELECT COUNT(ApplicantID) FROM Applicants ) > 60 SELECT -1 as error  FROM Applicants", DBConn)
I know that single quote character is hard to see in the code block.
0
 
megninAuthor Commented:
rockiroads, will "SELECT Count(ApplicantID) AS MyTotal FROM Applicants" work in...
DBCmd = New SqlCommand("SELECT COUNT(ApplicantID) AS MyTotal FROM Applicants)", DBConn)

"MyTotal" would not be set as a VB variable, but a SQL column, wouldn't it?  How would I read the value returned?  I don't think DBCmd.ExecuteScalar() would return the count value would it?  I'm not sure; I've never done this before.
0
 
megninAuthor Commented:
Oh, sorry rockiroads, I failed to read your last comment completely.  I think you already answed my question.  Let me give that a try...
0
 
megninAuthor Commented:
With the statements below I get this error:  "Invalid attempt to read when no data is present."


DBCmd = New SqlCommand("SELECT COUNT(ApplicantID) AS MyTotal FROM Applicants", DBConn)
Dim reader As SqlDataReader = DBCmd.ExecuteReader()
Dim lCount As Integer = reader("MyTotal")


Select Case lCount
    Case Is <= 22
        Session("Date") = " October 6, 2010 at 9:45 AM "
        Session("EventTime") = "10/6/2010 9:45:00 AM"

    Case Is > 22 And lCount <= 23
        Session("Date") = " October 6, 2010 at 12:45 AM "
        Session("EventTime") = "10/6/2010 12:45:00 AM"

    Case Is > 23 And lCount <= 24
        Session("Date") = " October 6, 2010 at 2:45 AM "
        Session("EventTime") = "10/6/2010 2:45:00 AM"

    Case Else
        Session("Date") = " I'm sorry.  It seems this workshop has been filled. "
        Session("EventTime") = " Please check back in two weeks for the November schedule. "

End Select

Open in new window

0
 
rockiroadsCommented:
I gave what I wrote a test and realised I missed the read, doh!

This is an example using msaccess, I cant use Sqlconnection with jet so used OleDb but the principle should be the same

Once you create the command and call executereader, issue the read

reader.Read()
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my.mdb"
        Dim conn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim reader As OleDbDataReader
        Dim sql As String

        conn = New OleDbConnection(connectionString)

        conn.Open()
        sql = "SELECT COUNT(*) AS MyTotal FROM Table1"
        cmd = New OleDbCommand(sql, conn)
        reader = cmd.ExecuteReader()
        reader.Read()

        MsgBox(reader.Item("MyTotal"))
        conn.Close()

Open in new window

0
 
megninAuthor Commented:
I had to make a slight modification to my "Case" statements.  I didn't have the syntax correct.  Other than that it works perfectly like this:
            DBCmd = New SqlCommand("SELECT COUNT(ApplicantID) AS MyTotal FROM Applicants", DBConn)
            Dim reader As SqlDataReader = DBCmd.ExecuteReader()
            reader.Read()
            Dim lCount As Integer = reader("MyTotal")


            Select Case lCount
                Case Is <= 29
                    Session("Date") = " October 6, 2010 at 9:45 AM "
                    Session("EventTime") = "10/6/2010 9:45:00 AM"

                Case 30 To 31
                    Session("Date") = " October 6, 2010 at 12:45 AM "
                    Session("EventTime") = "10/6/2010 12:45:00 AM"

                Case 32 To 33
                    Session("Date") = " October 6, 2010 at 2:45 AM "
                    Session("EventTime") = "10/6/2010 2:45:00 AM"

                Case Else
                    Session("Date") = " I'm sorry.  It seems this workshop has been filled. "
                    Session("EventTime") = " Please check back in two weeks for the November schedule. "

            End Select

Open in new window

0
 
megninAuthor Commented:
Thank you all for your suggestions.  rockiroads' solution was exactly what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.