Solved

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

Posted on 2010-09-17
14
711 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now