can't find largest database record

microsoft.public.frontpage.programming

15 Sep 2007 4:10 AM

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
%>

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

15 Sep 2007 8:15 AM

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!" (-;
_____________________________________________

Show quote

15 Sep 2007 8:10 PM

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.

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

Show quote

16 Sep 2007 8:17 AM

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!" (-;
_____________________________________________

Show quote

"Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message
news:84AAAA91-59B6-47E4-95C8-6C3E6285A398@microsoft.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" 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!" (-;
| > _____________________________________________
| >
| >
| > "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message
| > news:EB937615-FB03-47CD-96C7-BE6C78976F8D@microsoft.com...
| > |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
| >
| >
| >

16 Sep 2007 8:26 AM

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/

Show quote

"Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in
message news:84AAAA91-59B6-47E4-95C8-6C3E6285A398@microsoft.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" 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!" (-;
> > _____________________________________________
> >
> >
> > "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message
> > news:EB937615-FB03-47CD-96C7-BE6C78976F8D@microsoft.com...
> > |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
> >
> >
> >

16 Sep 2007 6:24 PM

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

Show quote

"Ronx" wrote:

> 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/
>
>
>
>
> "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in
> message news:84AAAA91-59B6-47E4-95C8-6C3E6285A398@microsoft.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" 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!" (-;
> > > _____________________________________________
> > >
> > >
> > > "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message
> > > news:EB937615-FB03-47CD-96C7-BE6C78976F8D@microsoft.com...
> > > |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
> > >
> > >
> > >
>
>

16 Sep 2007 6:49 PM

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/

Show quote

"Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in
message news:F67C46AC-F469-46BA-9C47-F3FBF41C1B75@microsoft.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" wrote:
>
> > 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/
> >
> >
> >
> >
> > "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in
> > message news:84AAAA91-59B6-47E4-95C8-6C3E6285A398@microsoft.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" 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!" (-;
> > > > _____________________________________________
> > > >
> > > >
> > > > "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message
> > > > news:EB937615-FB03-47CD-96C7-BE6C78976F8D@microsoft.com...
> > > > |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
> > > >
> > > >
> > > >
> >
> >

16 Sep 2007 7:06 PM

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

Show quote

"Ronx" wrote:

> 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/
>
>
>
>
> "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in
> message news:F67C46AC-F469-46BA-9C47-F3FBF41C1B75@microsoft.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" wrote:
> >
> > > 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/
> > >
> > >
> > >
> > >
> > > "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in
> > > message news:84AAAA91-59B6-47E4-95C8-6C3E6285A398@microsoft.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" 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!" (-;
> > > > > _____________________________________________
> > > > >
> > > > >
> > > > > "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message
> > > > > news:EB937615-FB03-47CD-96C7-BE6C78976F8D@microsoft.com...
> > > > > |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
> > > > >
> > > > >
> > > > >
> > >
> > >
>
>