can't find largest database record

D

Dave Lagergren

I am using the following code to find the largest record number in a
database. I have an include file that handles getting the database open.
The "ordernumber" column of the database is sorted in ascending order. This
only gets to 1.

<%
DIM mySQL, iRecordCount, iRC
iRC = 1

mySQL = "SELECT * FROM Results"
objRS.Open mySQL, objConn

DO WHILE NOT objRS.EOF

if cStr(iRC) = objRS("OrderNumber") then
iRC = iRC + 1
Response.Write ">> IRC= " & iRC & "<<"
objRS.MoveNext
else
Exit Do
end if
Loop

iRecordCount = cStr(iRC)

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
 
S

Stefan B Rusynko

Your logic will always stop at 1 because all other values will fail the IF and Exit the Do loop

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I am using the following code to find the largest record number in a
| database. I have an include file that handles getting the database open.
| The "ordernumber" column of the database is sorted in ascending order. This
| only gets to 1.
|
| <%
| DIM mySQL, iRecordCount, iRC
| iRC = 1
|
| mySQL = "SELECT * FROM Results"
| objRS.Open mySQL, objConn
|
| DO WHILE NOT objRS.EOF
|
| if cStr(iRC) = objRS("OrderNumber") then
| iRC = iRC + 1
| Response.Write ">> IRC= " & iRC & "<<"
| objRS.MoveNext
| else
| Exit Do
| end if
| Loop
|
| iRecordCount = cStr(iRC)
|
| objRS.Close
| Set objRS = Nothing
| objConn.Close
| Set objConn = Nothing
| %>
|
| --
| Dave Lagergren
| Manager - Data Applications
| Wireless Management, Inc
| Specializing in cellular wireless applications
 
D

Dave Lagergren

I just had one of those "ah-ha" moments. So I need to change it from:

if cStr(iRC) = objRS("OrderNumber") then

to

if cStr(iRC) <= objRS("OrderNumber") then

That works! Now, how can I make sure my database is always sorted by a
column named OrderNumber? I think I set it up right but if anyone is aware
of a good tutorial on sorting I would appreciate it.
 
S

Stefan B Rusynko

Use
mySQL = "SELECT * FROM Results ORDER BY OrderNumber ASC"

See http://www.w3schools.com/

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I just had one of those "ah-ha" moments. So I need to change it from:
|
| if cStr(iRC) = objRS("OrderNumber") then
|
| to
|
| if cStr(iRC) <= objRS("OrderNumber") then
|
| That works! Now, how can I make sure my database is always sorted by a
| column named OrderNumber? I think I set it up right but if anyone is aware
| of a good tutorial on sorting I would appreciate it.
|
| --
| Dave Lagergren
| Manager - Data Applications
| Wireless Management, Inc
| Specializing in cellular wireless applications
|
|
| "Stefan B Rusynko" wrote:
|
| > Your logic will always stop at 1 because all other values will fail the IF and Exit the Do loop
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > _____________________________________________
| >
| >
| > | > |I am using the following code to find the largest record number in a
| > | database. I have an include file that handles getting the database open.
| > | The "ordernumber" column of the database is sorted in ascending order. This
| > | only gets to 1.
| > |
| > | <%
| > | DIM mySQL, iRecordCount, iRC
| > | iRC = 1
| > |
| > | mySQL = "SELECT * FROM Results"
| > | objRS.Open mySQL, objConn
| > |
| > | DO WHILE NOT objRS.EOF
| > |
| > | if cStr(iRC) = objRS("OrderNumber") then
| > | iRC = iRC + 1
| > | Response.Write ">> IRC= " & iRC & "<<"
| > | objRS.MoveNext
| > | else
| > | Exit Do
| > | end if
| > | Loop
| > |
| > | iRecordCount = cStr(iRC)
| > |
| > | objRS.Close
| > | Set objRS = Nothing
| > | objConn.Close
| > | Set objConn = Nothing
| > | %>
| > |
| > | --
| > | Dave Lagergren
| > | Manager - Data Applications
| > | Wireless Management, Inc
| > | Specializing in cellular wireless applications
| >
| >
| >
 
R

Ronx

Try changing the SQL etc to

Dim sIRC, iRC
iRC = 0
mySQL = SELECT Max([OrderNumber]) AS lastOrder FROM [Results];
objRS.Open mySQL, objConn
if not objRS.EOF then
sIRC = objRS("lastOrder")
if isnumber(sIRC) then iRC = cINT(sIRC) end if
end if


The result will be the highest order number, or 0 if there are no valid
order numbers (valid is assumed to be numeric.)
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/




I just had one of those "ah-ha" moments. So I need to change it from:

if cStr(iRC) = objRS("OrderNumber") then

to

if cStr(iRC) <= objRS("OrderNumber") then

That works! Now, how can I make sure my database is always sorted by a
column named OrderNumber? I think I set it up right but if anyone is aware
of a good tutorial on sorting I would appreciate it.

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


Stefan B Rusynko said:
Your logic will always stop at 1 because all other values will fail the IF and Exit the Do loop

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I am using the following code to find the largest record number in a
| database. I have an include file that handles getting the database open.
| The "ordernumber" column of the database is sorted in ascending order. This
| only gets to 1.
|
| <%
| DIM mySQL, iRecordCount, iRC
| iRC = 1
|
| mySQL = "SELECT * FROM Results"
| objRS.Open mySQL, objConn
|
| DO WHILE NOT objRS.EOF
|
| if cStr(iRC) = objRS("OrderNumber") then
| iRC = iRC + 1
| Response.Write ">> IRC= " & iRC & "<<"
| objRS.MoveNext
| else
| Exit Do
| end if
| Loop
|
| iRecordCount = cStr(iRC)
|
| objRS.Close
| Set objRS = Nothing
| objConn.Close
| Set objConn = Nothing
| %>
|
| --
| Dave Lagergren
| Manager - Data Applications
| Wireless Management, Inc
| Specializing in cellular wireless applications
Click to expand...
Click to expand...
 
D

Dave Lagergren

I found code that does exactly what i want:

<%
' Dim my query string and variable
DIM mySQL, iRecordCount

' set my query string to get the largest OrderNumber in the table
mySQL = "select max(OrderNumber)from Results"

' open the table
objRS.Open mySQL, objConn

' set iRecordCount to the returned variable
iRecordCount = objRS(0)

' close and clean up the database
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

increment the counter to the next OrderNumber
iRecordCount = iRecordCount +1

%>

This seems to work fine. Have I opened myself up to any potential problems?

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


Ronx said:
Try changing the SQL etc to

Dim sIRC, iRC
iRC = 0
mySQL = SELECT Max([OrderNumber]) AS lastOrder FROM [Results];
objRS.Open mySQL, objConn
if not objRS.EOF then
sIRC = objRS("lastOrder")
if isnumber(sIRC) then iRC = cINT(sIRC) end if
end if


The result will be the highest order number, or 0 if there are no valid
order numbers (valid is assumed to be numeric.)
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/




I just had one of those "ah-ha" moments. So I need to change it from:

if cStr(iRC) = objRS("OrderNumber") then

to

if cStr(iRC) <= objRS("OrderNumber") then

That works! Now, how can I make sure my database is always sorted by a
column named OrderNumber? I think I set it up right but if anyone is aware
of a good tutorial on sorting I would appreciate it.

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


Stefan B Rusynko said:
Your logic will always stop at 1 because all other values will fail the IF and Exit the Do loop

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I am using the following code to find the largest record number in a
| database. I have an include file that handles getting the database open.
| The "ordernumber" column of the database is sorted in ascending order. This
| only gets to 1.
|
| <%
| DIM mySQL, iRecordCount, iRC
| iRC = 1
|
| mySQL = "SELECT * FROM Results"
| objRS.Open mySQL, objConn
|
| DO WHILE NOT objRS.EOF
|
| if cStr(iRC) = objRS("OrderNumber") then
| iRC = iRC + 1
| Response.Write ">> IRC= " & iRC & "<<"
| objRS.MoveNext
| else
| Exit Do
| end if
| Loop
|
| iRecordCount = cStr(iRC)
|
| objRS.Close
| Set objRS = Nothing
| objConn.Close
| Set objConn = Nothing
| %>
|
| --
| Dave Lagergren
| Manager - Data Applications
| Wireless Management, Inc
| Specializing in cellular wireless applications
Click to expand...
Click to expand...
Click to expand...
 
R

Ronx

This is the same as the code I posted - except it assumes that there is
at least one record in the table, and all records in the table have
valid entries. If the entries are numeric (and the field is numeric)
then that will be fine - but your use of cInt in your original code
indicates a text field is used for the ordernumber; with a text field
you could have an ordernumber like ABC instead of 123 - incrementing ABC
might be difficult.
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/




I found code that does exactly what i want:

<%
' Dim my query string and variable
DIM mySQL, iRecordCount

' set my query string to get the largest OrderNumber in the table
mySQL = "select max(OrderNumber)from Results"

' open the table
objRS.Open mySQL, objConn

' set iRecordCount to the returned variable
iRecordCount = objRS(0)

' close and clean up the database
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

increment the counter to the next OrderNumber
iRecordCount = iRecordCount +1

%>

This seems to work fine. Have I opened myself up to any potential problems?

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


Ronx said:
Try changing the SQL etc to

Dim sIRC, iRC
iRC = 0
mySQL = SELECT Max([OrderNumber]) AS lastOrder FROM [Results];
objRS.Open mySQL, objConn
if not objRS.EOF then
sIRC = objRS("lastOrder")
if isnumber(sIRC) then iRC = cINT(sIRC) end if
end if


The result will be the highest order number, or 0 if there are no valid
order numbers (valid is assumed to be numeric.)
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/




I just had one of those "ah-ha" moments. So I need to change it from:

if cStr(iRC) = objRS("OrderNumber") then

to

if cStr(iRC) <= objRS("OrderNumber") then

That works! Now, how can I make sure my database is always sorted by a
column named OrderNumber? I think I set it up right but if anyone is aware
of a good tutorial on sorting I would appreciate it.

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


:

Your logic will always stop at 1 because all other values will fail the IF and Exit the Do loop

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I am using the following code to find the largest record number in a
| database. I have an include file that handles getting the database open.
| The "ordernumber" column of the database is sorted in ascending order. This
| only gets to 1.
|
| <%
| DIM mySQL, iRecordCount, iRC
| iRC = 1
|
| mySQL = "SELECT * FROM Results"
| objRS.Open mySQL, objConn
|
| DO WHILE NOT objRS.EOF
|
| if cStr(iRC) = objRS("OrderNumber") then
| iRC = iRC + 1
| Response.Write ">> IRC= " & iRC & "<<"
| objRS.MoveNext
| else
| Exit Do
| end if
| Loop
|
| iRecordCount = cStr(iRC)
|
| objRS.Close
| Set objRS = Nothing
| objConn.Close
| Set objConn = Nothing
| %>
|
| --
| Dave Lagergren
| Manager - Data Applications
| Wireless Management, Inc
| Specializing in cellular wireless applications
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Lagergren

Yes, it is is virtually the same code. Fortunately the order numbers are
assigned by the system and simply increment with each new order - that is why
I needed the routine. When a new order is entered I find the last order
number and increment it and assign it to the new order.

I used cInt because I was not sure if the result was text or numeric. I
tend to get confused by that for some reason.

Your suggestion clarified everything for me and I really appreciate it. I
have to make asp sites for our company out of necessity, not because I am a
coder. If it wasn't for the help of people like you I would never get any
sleep!

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


Ronx said:
This is the same as the code I posted - except it assumes that there is
at least one record in the table, and all records in the table have
valid entries. If the entries are numeric (and the field is numeric)
then that will be fine - but your use of cInt in your original code
indicates a text field is used for the ordernumber; with a text field
you could have an ordernumber like ABC instead of 123 - incrementing ABC
might be difficult.
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/




I found code that does exactly what i want:

<%
' Dim my query string and variable
DIM mySQL, iRecordCount

' set my query string to get the largest OrderNumber in the table
mySQL = "select max(OrderNumber)from Results"

' open the table
objRS.Open mySQL, objConn

' set iRecordCount to the returned variable
iRecordCount = objRS(0)

' close and clean up the database
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

increment the counter to the next OrderNumber
iRecordCount = iRecordCount +1

%>

This seems to work fine. Have I opened myself up to any potential problems?

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


Ronx said:
Try changing the SQL etc to

Dim sIRC, iRC
iRC = 0
mySQL = SELECT Max([OrderNumber]) AS lastOrder FROM [Results];
objRS.Open mySQL, objConn
if not objRS.EOF then
sIRC = objRS("lastOrder")
if isnumber(sIRC) then iRC = cINT(sIRC) end if
end if


The result will be the highest order number, or 0 if there are no valid
order numbers (valid is assumed to be numeric.)
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/




message
I just had one of those "ah-ha" moments. So I need to change it from:

if cStr(iRC) = objRS("OrderNumber") then

to

if cStr(iRC) <= objRS("OrderNumber") then

That works! Now, how can I make sure my database is always sorted by a
column named OrderNumber? I think I set it up right but if anyone is aware
of a good tutorial on sorting I would appreciate it.

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


:

Your logic will always stop at 1 because all other values will fail the IF and Exit the Do loop

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I am using the following code to find the largest record number in a
| database. I have an include file that handles getting the database open.
| The "ordernumber" column of the database is sorted in ascending order. This
| only gets to 1.
|
| <%
| DIM mySQL, iRecordCount, iRC
| iRC = 1
|
| mySQL = "SELECT * FROM Results"
| objRS.Open mySQL, objConn
|
| DO WHILE NOT objRS.EOF
|
| if cStr(iRC) = objRS("OrderNumber") then
| iRC = iRC + 1
| Response.Write ">> IRC= " & iRC & "<<"
| objRS.MoveNext
| else
| Exit Do
| end if
| Loop
|
| iRecordCount = cStr(iRC)
|
| objRS.Close
| Set objRS = Nothing
| objConn.Close
| Set objConn = Nothing
| %>
|
| --
| Dave Lagergren
| Manager - Data Applications
| Wireless Management, Inc
| Specializing in cellular wireless applications
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with data type mismatch - error 80040e07 1
Data type mismatch in criteria expression. 3
Delete record in a table 1
Connecting guestbook to MySQL 0
where to buy asp page 0
User defined datatype? 2
access error please help 2
Microsoft JET Database Engine error '80040e07' 4

Top