Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-17
14
Medium Priority
?
718 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 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