Classic ASP Discussion - Comparing Numeric Values

Print Page | Close Window

Comparing Numeric Values

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=12046
Printed Date: 29 March 2026 at 3:12am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Comparing Numeric Values

Posted By: Misty
Subject: Comparing Numeric Values
Date Posted: 05 October 2004 at 2:06pm

I have a desire to be able to find out if a certain number (AreaID) exists in a database or not. If the areaID exists in the database, I don't want any updates to the database. Please look at the bolded line below. Both Area and rsCity("Area") are numbers. I know how to do this with letters, but I am not sure how to do with numbers. With letters, you would do something like: If Lcase(City) = Lcase(rs2("City")) Then

Here's some of the code:
'Initalize Error
Error = False
'Loop through all the records in the recordset to check that the areaID is not already in the database
Do While NOT rsCity.EOF
'Check to see if the database already has this area
If Area = rsCity("AreaID") Then
Error = True %>

Replies:

Posted By: Gullanian
Date Posted: 05 October 2004 at 2:29pm

If Area ID isn't a numeric datatype, change it to:

cLng(rsCity("areaID"))

And change Area to:

clng(area)

So it might read:

cLng(Area) = cLng(rsCity("AreaID"))

Posted By: Gullanian
Date Posted: 05 October 2004 at 2:31pm

PS: An explanation of cLng()

cLng() converts the passed value into a long integer. You also have cInt() which convers the passed value into an integer. If either of the two things you are comparing are not numerical, but string, then you can't compare them until they are both integers.

Posted By: Misty
Date Posted: 05 October 2004 at 2:48pm

Gullanian,

Thank you for the information!

Both are int in datatype in my SQL Server database. Which cInt or cLng would be best for this case? I am just not sure because my database will only allow up to 4 numbers for the AreaID.

Posted By: theSCIENTIST
Date Posted: 05 October 2004 at 3:29pm

Misty wrote:

Error = False
Do While NOT rsCity.EOF
If Area = rsCity("AreaID") Then
Error = True %>

Just spotted something that is unrelated to your query, but that can be improved. I don't know the rest of the code, but I'm assuming you are going through the recordset to determine if AreaID exists or not, and if it does Error becomes true, why not exiting the Do ... Loop you are in by using a Exit Do? If you don't, the recordset will continue to be probed even thou your condition has been met, wasting cycles and all.

Error = False

Do While NOT rsCity.EOF

If Area = rsCity("AreaID") Then

Error = True

Exit Do %>

Just a point.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: Misty
Date Posted: 05 October 2004 at 3:47pm

theScientist,

Thank you for noticing! I already had Exit Do in my code. I just chose to not post all of the code because I was focused on that one line.

Posted By: Misty
Date Posted: 05 October 2004 at 4:11pm

I cannot get the query to work. I am getting the following error message:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Can someone please help me to figure out what is wrong? The querystring is right. I suspect that it might have to do something with the comparison of the integers.

Let me show more code:

'Create a recordset

set rsCity = Server.CreateObject("ADODB.Recordset")

rsCity.Open sql, connectionString, adOpenDynamic, adLockOptimistic

'Initalize Error

Error = False

'Loop through all the records in the recordset to check that the user id and the e-mail address are not already in the database

Do While NOT rsCity.EOF

'Check to see if the database already has this city

If cLng(Area) = cLng(rsCity("AreaID")) then

Error = True

Exit Do

End If

'Move to the next recordset

rsCity.MoveNext

Loop

If Error = False then

rsCity("AreaID") = Area (this is the line that the error message is on)

rsCity.update

Posted By: theSCIENTIST
Date Posted: 05 October 2004 at 4:23pm

The problem is because you are updating if AreaID doesn't exist, how can you then go on and update it if it doesn't exist?

I'm also a bit confused, what number is that you look for? A value in a field? Or is it the field itself?

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: Misty
Date Posted: 05 October 2004 at 4:49pm

If the error is true (if the certain AreaID already exists in the database), then it won't update the database. I want the AreaID for a certain city to be updated if the areaID is null.

CLng(Area) comes from Request.Form(selArea). It is a number (ex: 1, 24, 234). I am looking for a value in the field.

Let me show you my sql statement:

'Build the sql query used to fill the select element
sql = "select * from City"
sql = sql & " where City= '" & City & "'"
sql = sql & " and State= '" & State & "'"

The City database has a field called AreaID (int). The AreaID is null. I want to update the areaID for each City that has a null value.

Does this make sense about what I'm trying to do?

Posted By: Gullanian
Date Posted: 05 October 2004 at 7:43pm

You get the BOF error because you are selecting a record that doesn't exist.

Put this in your code after you open rsCities:

If rsCities.EOF then
    'Citiy doesn't exist in state
    Response.write(City & " in " & State & " does not exist in database")
Else
    'City does exist in DB
    Do your code if a city is found
End if
rsCities.close

Posted By: Misty
Date Posted: 06 October 2004 at 1:44am

I tried what you suggested. But I think that there's something else wrong.

I did a check on the sql statement.

Here's the result:

select * from City where City= 'Albany' and State= 'NY' which is right. I don't understand what may be wrong.

I also got an error message when I tried using cLNG. Here's the error message that I got: Invalid use of Null: 'CLng' . I really need to be able to compare both AreaID from the database and AreaID that comes from the form.

The AreaID field in the City database (SQL Server 2000) is int for the data type and 4 in length.

Posted By: Gullanian
Date Posted: 06 October 2004 at 7:27am

Try running the query in query manager. As for the clng error, it's because the variable you put inside the brackets in cLng() is mandatory, there has to be a variable in it, and if there is, the variable has not been set if you are getting a null error. Check the variable contains a value.

Posted By: ljamal
Date Posted: 06 October 2004 at 8:15am

Use the isNull statement for MSSQL to return the int value of your choice for areaID so that CLng always receives an int value. The syntax is:
ISNULL ( check_expression , replacement_value )
For example
select IsNull(AreaID, 0) from City where City = 'Albany' and state = 'NY'
If the value for AreaID was null than the value returned would be 0.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Misty
Date Posted: 06 October 2004 at 12:31pm

I am very frustrated because I still cannot get to work this. I tried doing what Ijamal suggested. I got the following error message:

Item cannot be found in the collection corresponding to the requested name or ordinal. I will bold the line that got this error message. AreaID definitely exists in the City database.

'Build the sql query used to fill the select element

sql = "select IsNull(AreaID, 0) from City"

sql = sql & " where City= '" & hidCity & "'"

sql = sql & " and State= '" & hidState & "'"

'Create a recordset

set rsCity = Server.CreateObject("ADODB.Recordset")

rsCity.Open sql, connectionString, adOpenDynamic, adLockOptimistic

'Initalize Error

Error = False

If rsCity.EOF then

'Area doesn't exist in state

Response.write(hidCity & " in " & hidState & " does not exist in database")

Else

'Area does exist in DB

'Check to see if the database already has this area

If CLng(Area) = CLng(rsCity("AreaID")) then (where the error is)

Error = True

'Move to the next recordset

rsCity.MoveNext

If Error = False then

Posted By: Gullanian
Date Posted: 06 October 2004 at 1:10pm

Get rid of the isnull in the query.

Change:
If CLng(Area) = CLng(rsCity("AreaID")) then (where the error is)

To

If isNull(Area) = False then
    If Clng(Area) = rsCity("AreaID") then
       'Do whatever, this point means that area var is equal to record in loop
    End if
End if

Posted By: Misty
Date Posted: 06 October 2004 at 1:45pm

Gullanian,

Thank you for the code! It almost worked. But there is still something wrong. I think that it has something to do with the line of code: If Clng(Area) = rsCity("AreaID") then. It is frustrating because it won't let me use CLng(rsCity("AreaID")).

I found out it would update the AreaID when I had the following code:
If rsCity.EOF then
'Area doesn't exist in state
Response.write(hidCity & " in " & hidState & " does not exist in database")
Else
'Move to the next recordset
rsCity.MoveFirst
rsCity("AreaID") = Area
rsCity.update
End If

But this is not acceptable for this case. I want the AreaID to be only updated if the AreaID for a certain city is null. Does anyone have any idea how I might be able to make sure that both Area (from the form) and AreaID (from the database) are the same.

Posted By: michael
Date Posted: 06 October 2004 at 3:04pm

Don't get rid of IsNull, ljamal was right, it being the easiest option, you just need to set the field name as it had been combined in a function so

'Build the sql query used to fill the select element
sql = "select IsNull(AreaID, 0) from City"

should be

'Build the sql query used to fill the select element
sql = "select IsNull(AreaID, 0) as AreaID from City"

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Misty
Date Posted: 06 October 2004 at 4:31pm

I still cannot get the AreaID to be updated. I am getting no error message. The sql statement that Michael gave me worked. Maybe there's something I don't notice. It should work, but I don't know what's wrong. I really need this code because I want the AreaID for a certain city to be only updated one time.

Here's my new code after I did what Michael suggested:

'Build the sql query used to fill the select element

sql = "select IsNull(AreaID, 0) as AreaID from City"

sql = sql & " where City= '" & hidCity & "'"

sql = sql & " and State= '" & hidState & "'"

'Create a recordset

set rsCity = Server.CreateObject("ADODB.Recordset")

rsCity.Open sql, connectionString, adOpenDynamic, adLockOptimistic

'Initalize Error

Error = False

If rsCity.EOF then

'Area doesn't exist in state

Response.write(hidCity & " in " & hidState & " does not exist in database")

Else

'Area does exist in DB

'Check to see if the database already has this area

If CLng(Area) = CLng(rsCity("AreaID")) then

Error = True

'Move to the next recordset

rsCity.MoveFirst

If Error = False then

rsCity("AreaID") = Area

rsCity.update

Posted By: michael
Date Posted: 06 October 2004 at 5:33pm

'Move to the next recordset

rsCity.MoveFirst

shouldn't that be MoveNext if you want to move to the next record set? This way it will go to the first one (maybe I am just missing something I am tired)

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Misty
Date Posted: 06 October 2004 at 6:50pm

I changed it to rsCity.MoveNext, but it didn't update the AreaID for a certain city.

I wonder if this is a problem with SQL Server 2000. I am so frustrated. The code should work, but I don't know why it won't work.

Posted By: Misty
Date Posted: 07 October 2004 at 1:24am

I decided to experiment with the code below. I did a check and both AreaID and Area have the right values. I think that it might have to do something with my if statements or SQL Server. But it didn't work because I got the following error message:

Microsoft OLE DB Provider for SQL Server error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Here's the code that I tried to use:

'Build the sql query used to fill the select element

sql = "select IsNull(AreaID, 0) as AreaID from City"

sql = sql & " where City= '" & hidCity & "'"

sql = sql & " and State= '" & hidState & "'"

'Create a recordset

set rsCity = Server.CreateObject("ADODB.Recordset")

rsCity.Open sql, connectionString, adOpenDynamic, adLockOptimistic

If rsCity.EOF then

'Area doesn't exist in state

Response.write(hidCity & " in " & hidState & " does not exist in database")

Else

'Area does exist in DB

'Check to see if the database already has this area

If CLng(Area) <> CLng(rsCity("AreaID")) then

'Move to the next recordset

rsCity.MoveFirst

rsCity("AreaID") = Area

rsCity.update

End If

Posted By: Misty
Date Posted: 11 October 2004 at 11:11pm

I still have not gotten this to work yet. I've performed other tests. I wish that I could send someone the 3 web pages with the code and the tables. I wonder if this would work on another SQL Server. It is very possible that there is a typo somewhere.

Would anyone here be willing to test this on another SQL Server?