Solved

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

Posted on 2010-09-17
14
713 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:megnin
14 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33704131
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
 
LVL 1

Author Comment

by:megnin
ID: 33704197
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33704205
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 15

Expert Comment

by:AmmarR
ID: 33704208
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
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33704221
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33704243
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
 
LVL 1

Author Comment

by:megnin
ID: 33704316
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
 
LVL 1

Author Comment

by:megnin
ID: 33704392
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
 
LVL 1

Author Comment

by:megnin
ID: 33704410
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
 
LVL 1

Author Comment

by:megnin
ID: 33704591
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33704672
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33704679

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
 
LVL 1

Author Comment

by:megnin
ID: 33704781
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
 
LVL 1

Author Closing Comment

by:megnin
ID: 33704821
Thank you all for your suggestions.  rockiroads' solution was exactly what I needed.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question