How do I test an amount entered into a textbox is <= the largest number in a table?

[emaduddeen]

How do I test an amount entered into a textbox is <= the largest number in a table?

Hi Everyone,

Can you tell me how to fix my query so I can determine if an amount entered into a textbox is < the largest number in a database table?

Here is some sample data:

CategoryNumber
----------------
10
20
30
40
50

If the user enters 35 I'm looking for the query to indicate that this number 35 is < the largest number which happens to be 50.

Here is the query I tried to use:

        strSqlStatement =
            "Select 1 " & _
              "From Categories " & _
             "Where @CategoryNumber < Max(CategoryNumber) "

I get this error when running it so I know my query is wrong:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Can you show me how to do this query so it will work?

Thanks.

Truly,
Emad

 

[chris_asa]

The server already knows max(), so i guess it's the client that needs to know:

a stored procedure on the server?
CREATE PROCEDURE dbo.sproc_GetMaxCategory
AS
SET NOCOUNT ON;
BEGIN
SELECT MAX(c.CategoryNumber)
FROM dbo.Categories c;
END
GO

and .ExecuteScalar on it:
(imports system.data.sqlclient)
dim conn as new sqlconnection("your-connection-string-goes-here")
try
dim cmd as new sqlcommand
cmd.connection = conn
cmd.text = "dbo.sproc_GetTheMaxCategory"
cmd.type = commandtype.storedprocedure
conn.open
yourVarWithAppropriateType = cmd.executescalar
'or more conservative: yourVar = convert.toint32(cmd.ExecuteScalar()) ' or .toWhateverTypeItIs
finally
conn.close
end try

Chris

 

[cjard]

select case when @userentry < max(id) then "yes" else "no" end as is_less from tbl

 

[emaduddeen]

Hi cjard,

Thanks for the help.

It's very much appreciated

Truly,
Emad