Getting A value out of a db 2

[mbutler55]

This has to be THE dumbest question ever asked, but I feel like I have tried everything and nothing seems to work! I have a list of numbers, sorted in ascending order in a table called Median - (yes I am trying to find the median value)

My code to peruse the list of numbers until finding the middle number is:
' First I find the total number of records. Is there an
easier way?
iNumRec = 1
Do while NOT objRS.EOF
iNumRec = iNumRec + 1
objRS.MoveNext
Loop
' Now to peruse to the middle record
for iCounter = 1 to iNumRec \ 2
next
' I am now at the middle record what comes next?
response.write _________

 

[jlsmithhartfiel]

objRS.RecordCount should give you the total records

The problem with just going to the count / 2 is how do you handle odd # record sets? There was a thread on calculating medians a while back. Someone had an example going. Search for median.
Jessica

 

[mbutler55]

I already did a keyword search for Median and couldn't find anything that would help. I do have other code with this that takes into account odd records / even records. My question is simply how to get a value to display from the database. What goes in the blank?

If iCounter is pointing at the value that I want to see on the screen, what do I response.write?
response.write _________

 

[BDC2]

iNumRec = 1
Do while NOT objRS.EOF
iNumRec = iNumRec + 1
objRS.MoveNext
Loop
objRS.MoveFirst
' Now to peruse to the middle record
for iCounter = 1 to iNumRec \ 2
objRS.MoveNext
next
' I am now at the middle record what comes next?
response.write objRS("DBaseFieldName&quot

Is this what you mean?

BDC.

 

[FesterSXS]

With the code you have there it you would have to re-enter the recordset to retrieve the value. Probably best to do as Jessica suggested and use RecordCount.

Make sure you open your recordset with an 'adOpenStatic' cursorType. This will allow you to use RecordCount. You will get a value of -1 if you open the recordset using the default cursorType.

Something like this might be OK for you.

<%
intCount = 0
intMedian = CInt(objRS.RecordCount / 2)

While NOT objRS.EOF
  intCount = intCount + 1

  If intCount = intMedian Then
    Response.Write objRS(&quot;YourFieldName&quot;)
    objRS.MoveLast
  End If

  objRS.MoveNext
Wend
%>

Tony

http://www.SpiraxSarco.com/cad

 

[FesterSXS]

You beat me to it BDC2 Tony

http://www.SpiraxSarco.com/cad

 

[mbutler55]

If this works, I will call you both the "Greatest Programmer that ever lived!!!!" But, how about one more. And let's see how can type the fastest:

If I am wanting the user to type in Username and Password on page1, click the submit button and then on page2 filter the data to see if the user exists. I know that I will perform a check for EOF, but how do I create the filter or whatever.

Dim strName, strPassword
strName = Request ("Username&quot
strPassword = Request ("Password&quot
Session ("Username&quot = strName
Session ("Password&quot = strPassword

Is it something like:
objRS.Filter = "Invest = " & strName & "&Password = " & strPassword"

and then check for EOF?

 

[BDC2]

Create the filter in the DB call

"SELECT * FROM Tablename WHERE name = "whatever" AND password = "whatever"

If the recordset contains no records (rs.EOF) then the user doesn't exist.

BDC.

 

[FesterSXS]

I would use the SQL to check the DB.

<%
strSQL = &quot;SELECT * FROM tblUsers WHERE Invest='&quot; & strName & &quot;' AND Password='&quot; & strPassword & &quot;';&quot;

Set objUsersRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)
objUsersRS.Open strSQL, strYourConnectionString

If objUsersRS.EOF Then
 'no match found
Else
 'user found and verified
End If

objUsersRS.Close
Set objUsersRS = Nothing
%>

Tony

http://www.SpiraxSarco.com/cad

 

[FesterSXS]

beaten again Tony

http://www.SpiraxSarco.com/cad

 

[BDC2]

That was a close one ;-)

 

[mbutler55]

I bow down to you both!!! That is why they call you "Programmer" and me "Instructor" - those who can do, those who can't pretend to know what they are talking about in a class where no one has a clue!

 

[mbutler55]

I really do hate this stuff! Give me back my Pascal from 1986! It was so easy to read!

What is causing the following error:
Invalid character
intCount = intCount + 1

What can possibly be wrong with x = x + 1?

 

[FesterSXS]

can you post the rest of the code - sometimes the error is actually on a different line to the line reported. Tony

http://www.SpiraxSarco.com/cad

 

[mbutler55]

First of all if this is not your code, please don't think I am showing preference. I did try them both this is just the last one that I have! This is not the entire page of code just the snippet where the problem seems to occur. If I take this code out all is well.

'Count the number of Age Median
Dim tempAge, x, iNumRec, Median, intMedian
Set objRS = Server.CreateObject ("ADODB.Recordset&quot
sql="SELECT PTAGE FROM Median"
objRS.Open sql, objConn

Response.Write ("Age Median: &quot
objRS.MoveFirst
x = 0
intMedian = CInt(objRS.RecordCount / 2)
While NOT objRS.EOF
  x = x + 1
  If x = intMedian Then
   Response.Write objRS("PTAGE&quot
   objRS.MoveLast
  End
If
   objRS.MoveNext
Wend

PS - I REALLY do appreciate you and yours taking the time to look over this with me. Both of you have been VERY easy to understand. You are not talking in code!

 

[FesterSXS]

try this...

objRS.Open sql, objConn, adOpenStatic

This will allow you to retrieve the RecordCount of the recordset Tony

http://www.SpiraxSarco.com/cad

 

[mbutler55]

Nope still doesn't like x = x + 1

 

[FesterSXS]

I should add that in order to use the ADO constant, adOpenStatic, you need to have included the following line at the top of your page (or the top of any of your other include files)

<!-- METADATA TYPE=&quot;typelib&quot; FILE=&quot;C:\Program Files\Common Files\System\ado\msado15.dll&quot; -->

This file contains all the the ADO constants that are used in ASP scripts. You can always use the value 3 instead of adOpenStatic if you havent included the above file.

Hope that made sense Tony

http://www.SpiraxSarco.com/cad

 

[FesterSXS]

can you put your End If on one line or did that just occur when you posted the code???
Tony

http://www.SpiraxSarco.com/cad

 

[BDC2]

Your moving to the last record in your loop then trying to move to the next record in the following statement, try this

While NOT objRS.EOF
x = x + 1
If x = intMedian Then
Response.Write objRS(&quot;PTAGE&quot
objRS.MoveLast
Else
objRS.MoveNext
End If
Wend

BDC.