?
Solved

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

Posted on 2010-09-17
14
Medium Priority
?
717 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 2000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses

765 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