How to reduce login times when querying a huge MSSQL database for user name and password

I have an ASP page with the following appended code.

How can I rewrite this to run as a Stored Procedure to decrease the query time as the user database has over 1 million users and sometimes login alone times out on the server.
p_username = Replace(Request ("username"),"'","^")
	p_password = Replace((Request ("password")),"'","^")
	Session ("username") = p_username

	
	
	
	 Set MyConn=Server.CreateObject("ADODB.Connection")
		'Response.Write DB_Con
		'Response.End
		MyConn.Open DB_Con
				
		MySQL="Select  * from tbl_members Where username  = '" & p_username & "'"

		'Response.Write MySQL
		'Response.End ()
		Set MyRs=MyConn.Execute(MySQL)


	if MyRs.EOF then 
		'There is no such email and so the recordset is empty
		Response.Redirect "default.asp?retry=username"
		 
		
	 else
		'the username is valid, now check the password
		real_password	=	trim (MyRs("password"))
		
		'compare the recordset field for password with the database field for password
		if p_password	=	real_password then
		'the password is good
		dim dob
		'dob = MyRS("dob")
		'Response.Write dob
		'Response.End 
		Response.Cookies ("web2sms")("email")=MyRs("email")
		Response.Cookies ("web2sms")("isloggedin")=TRUE
		Response.Cookies ("web2sms")("username") = p_username
		Response.Cookies ("web2sms")("status") = MyRs("status")
		Response.Cookies ("web2sms")("firstname")	=	MyRs("firstname")
		Response.Cookies ("web2sms")("lastname")	= MyRs("lastname")
		Response.Cookies ("web2sms")("autoid") = MyRS("autoid")
		if ISNULL(MyRS("paypalauth")) then
		Response.Cookies ("web2sms")("paypal") ="dead"
		Else
		Response.Cookies ("web2sms")("paypal") = MyRS("paypalauth")
		End if
		Response.Cookies ("web2sms")("real_password")= MyRs("password")
		
		if  IsNull(MyRs("senderid"))  then
		
			Response.Cookies ("web2sms")("senderid")=  "Web2SmsPro"
			
		Else
		
			Response.Cookies ("web2sms")("senderid")= MyRs("senderid")
			
		End if
		
		
		'if p_save = "yes" then 
		'	Response.Cookies ("isloggedin").expires = #December 31, 2002 00:00:00#
		'end if
		
		'Report the IP address of login into the database.
			Dim objRecordset
			Set objRecordset = Server.CreateObject("ADODB.Recordset")
			
			objRecordset.Open "tbl_members", DB_CON, adOpenKeyset, adLockPessimistic, adCmdTable

	iRecordToUpdate = Clng(Request.Cookies("web2sms")("autoid"))
	
	If iRecordToUpdate <> 0 Then
		If Not objRecordset.EOF Then
			objRecordset.MoveFirst
			Do Until objRecordset.Fields("autoid") = iRecordToUpdate
				objRecordset.MoveNext
			Loop

			' String / Text Data Type
		ObjRecordset.Fields("lastloginIP") = Request.ServerVariables("REMOTE_ADDR")
		
				objRecordset.Update
		End If
	End If

	
		
		
		
		Response.Redirect "compose_message.asp"
		else
		'the username is valid but the password is incorrect
		Response.Redirect "default.asp?retry=password"
		
		
		end if
	
	end if
	
	'Good code practice. Clean up and destroy all objects  to free resources on server
	
	


MyRs.close
Set MyRs= Nothing
MyConn.Close
set MyConn=nothing

Open in new window

LVL 1
souldjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crisco96Commented:
Add an index on the username field and it will speed up your query significantly.

http://databases.about.com/od/sqlserver/a/storedprocedure.htm - writing a stored procedure

Also you should parameterize your queries otherwise you're vulnerable to sql injection: http://unixwiz.net/techtips/sql-injection.html
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Indexing username column it's very good idea. Avoid also looping through many records. For example, how many rows "tbl_members" have?
0
crisco96Commented:
Just noticed another issue this code:
                'Report the IP address of login into the database.
                        Dim objRecordset
                        Set objRecordset = Server.CreateObject("ADODB.Recordset")
                       
                        objRecordset.Open "tbl_members", DB_CON, adOpenKeyset, adLockPessimistic, adCmdTable

        iRecordToUpdate = Clng(Request.Cookies("web2sms")("autoid"))
       
        If iRecordToUpdate <> 0 Then
                If Not objRecordset.EOF Then
                        objRecordset.MoveFirst
                        Do Until objRecordset.Fields("autoid") = iRecordToUpdate
                                objRecordset.MoveNext
                        Loop

                        ' String / Text Data Type
                ObjRecordset.Fields("lastloginIP") = Request.ServerVariables("REMOTE_ADDR")
               
                                objRecordset.Update
                End If
        End If

Needs to be turned into a sql update because what you're doing above is bring all million records in tbl_members then loop through each one and check to see if autoid matches. The code below will increase it, if autoid is not a primary key already make sure to index it.

Set Command = Server.CreateObject("ADODB.Command")

Command.ActiveConnection = MyConn
Command.Parameters.Append(Command.CreateParameter("@lastloginIP", 200, &H0001, 20, Request.ServerVariables("REMOTE_ADDR")))
Command.Parameters.Append(Command.CreateParameter("@autoid", 3, &H0001, , Clnt(Request.Cookies("web2sms")("autoid"))))

Command.CommandText = "Update tbl_members Set LastLoginIP = ? Where autoid = ?"
Command.ExecuteNonQuery()

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

souldjAuthor Commented:
Thanks Guys but i get this error message

Microsoft VBScript runtime  error '800a01b6'

Object doesn't support this property or method: 'Command.ExecuteNonQuery'

0
crisco96Commented:
Oh sorry have been writing a lot of .Net lately, try Command.Execute() instead
0
souldjAuthor Commented:
I have a DB Table storign records for ALL my clients in this same database and my clients can run reports on what they have completed on the site

The table looks like this

CREATE TABLE [dbo].[messagelog_archive](
      [autoid] [int] IDENTITY(1,1) NOT NULL,
      [SN] [int] NULL,
      [TransID] [nvarchar](50) NULL,
      [PhoneNumber] [nvarchar](50) NULL,
      [Message] [ntext] NULL,
      [MessageID] [nvarchar](50) NULL,
      [MessageStatus] [nvarchar](50) NULL,
      [Originator] [nvarchar](50) NULL,
      [UserName] [nvarchar](50) NULL,
      [Pass] [nvarchar](50) NULL,
      [Credit] [nvarchar](10) NULL,
      [DateSent] [smalldatetime] NULL,
      [TimeSent] [smalldatetime] NULL,
      [Status] [nvarchar](1) NULL,
      [PC2SMSID] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


When a report is run at the moment, There are over 3 million roows and growing. It continually times out with clients.

How do I more efficiently index this table to allow better filtering.

My search code follows


0
souldjAuthor Commented:
                     
<%


' Our own constants:
Const PAGE_SIZE = 75  ' The size of our pages.

' Declare our variables... always good practice!
Dim strURL     ' The URL of this page so the form will work
               ' no matter what this file is named.

Dim cnnSearch  ' ADO connection
Dim rstSearch  ' ADO recordset
Dim strDBPath  ' path to our Access database (*.mdb) file

Dim strSQL     ' The SQL Query we build on the fly
Dim strSearch  ' The text being looked for

Dim iPageCurrent ' The page we're currently on
Dim iPageCount   ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I            ' Standard looping variable

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("SCRIPT_NAME")

' Retreive the term being searched for.  I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request("what")

' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
      iPageCurrent = 1
Else
      iPageCurrent = CInt(Request.QueryString("page"))
End If

' Since I'm doing this all in one page I need to see if anyone
' has searched for something.  If they have we hit the DB.
' O/W I just show the search form and quit.
%>
      <TABLE WIDTH="100%" BORDER="0" CELLSPACING="2" CELLPADDING="2">
      <TR>
            <TD class=inputbox><h4>SMS Reporting </h4></TD>
      </TR>
</TABLE>


<FORM name="demoform" onsubmit="" action="<%= strURL %>" method="get">
<input type=hidden name=what value=reports ID="Hidden2">
                        
                          <table width=100% ID="Table2" cellpadding=2 cellspacing=2>
                            <tr>
                              <td><strong>Start Date:
                                <input class="formitem" name="startdate" value="<%=date()%>" size="12" onfocus="this.blur()"
                              readonly ID="Text2">
                                <a href="javascript:void(0)"
                              onclick="if(self.gfPop)gfPop.fStartPop(document.demoform.startdate,document.demoform.enddate);return false;"
                              HIDEFOCUS><img class="PopcalTrigger" align="absmiddle" src="calbtn.gif" width="34" height="22" border="0" alt=""></a>      
                                </strong></td>
                              <td><strong>End Date:
                                <input class="formitem" name="enddate" value="<%=date()%>" size="12" onfocus="this.blur()"
                              readonly ID="Text3">
                                <a href="javascript:void(0)"
                              onclick="if(self.gfPop)gfPop.fEndPop(document.demoform.startdate,document.demoform.enddate);return false;"
                              HIDEFOCUS><img class="PopcalTrigger" align="absmiddle" src="calbtn.gif" width="34" height="22" border="0" alt=""></a>      
                                </strong></td>
                            </tr>
                            <tr align="left" valign="top">
                              <td colspan=2><table width="100%" border="0" cellspacing="0" cellpadding="0">
                                  <tr>
                                    <td width="30%"><strong>Containing this number
                                      </strong></td>
                                    <td><strong>
                                      <input name="number" type="text" class="formitem" id="number" value="any" size="40">
                                      </strong></td>
                                  </tr>
                                </table></td>
                            </tr>
                            <!--    <tr align="left" valign="top">
                              <td colspan=2><table width="100%" border="0" cellspacing="0" cellpadding="0">
                                  <tr>
                                    <td width="30%"><strong>Status of Message</strong></td>
                                    <td><strong>
                                      <select name="status" class="formitem">
                                        <option value="%" selected>Any</option>
                                        <option value="0">Delivered to Handset</option>
                                        <option value="?">Delivered to SMS Gateway</option>
                                        <option value="-1">Queued for Submission</option>
                                        <option value="Awaiting delivery receipt">Pending</option>
                                        <option value="error">Error</option>
                                      </select>
                                      </strong></td>
                                  </tr>
                                </table></td>
                            </tr>-->
                            <tr align="left" valign="top">
                              <td colspan=2><strong>With this status&nbsp;</strong>
                                <select name="statusreport" size="1" class="formitem">
                                <option value="all">All reports</option>
                                <option value="No Report">No reports</option>
                                <%
                               
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON


MySQL="Select errormessage, errorcode "&_
                  "from tbl_errormessages "&_
                  "ORDER BY errorcode;"

Set ObjRs=objDB.Execute(MySQL)

if objrs.eof then
%>
<option value="">No Status Data</option>
<%
else
Do While not objrs.Eof

%>
<option value="<%=objrs("errorcode")%>"><%=objrs("errormessage")%></option>
<%
Objrs.MoveNext
Loop
%>
</select>

<%
end if


objRS.close()
Set objRS = Nothing
                               
                                %>
                                </td>
                            </tr>
                            <tr align="left" valign="top">
                              <td> <strong>
                                Export Results</td>
                                <td> <input type=radio name=export value="yes" />Yes <br />
                               
                                <input type=radio name=export value="no" checked />No
                                </strong></td>
                            </tr>
                          <tr align="right" valign="top">
                              <td colspan=2> <strong>
                                <input type=submit value="Search" class="formitem">
                                </strong></td>
                            </tr>
                          </table>
</FORM>
                  
<p><br>

<%
'If strSearch <> "" Then


  'Display all the details passed

                  '      for i=1 to request.querystring.count
                  '      response.write Trim(request.querystring.key(i))&"; "& Trim(request.querystring.item(i)) &"<br>"
                  '      Next

            'Response.End
      
      Set cnnSearch = Server.CreateObject("ADODB.Connection")

      ' This line is for the Access sample database:
      cnnSearch.Open DB_CON

      ' Build our query based on the input.
      
      
      startdate = Request("startdate")
      enddate = request("enddate")
      
            if what = "reports" then
                                          startdate = date()
                                          enddate = date()
            
                                          else
      
                                          startdate = Request("startdate")
                                    enddate = request("enddate")
      
                              End if
      
                              if startdate = "" then
                                    startdate = date()
                              end if
      
                              if enddate = "" then
                                    enddate = date()
                              end if

number = request.querystring ("number")
      if number = "any" then
            number2find = "%"      
      else
            number2find = trim(cstr(number))
      end if
      
      messagestatus = request.QueryString ("statusreport")
            if messagestatus = "No Report" then
                  messagestatus = NULL
            else
                  messagestatus =request.QueryString ("statusreport")
            end if


orderby = Request.QueryString ("orderby")

if orderby = "" then
      orderby = "DateSent Desc"
      neworder= "DateSent"
end if

if Request.QueryString ("orderby") = "DateSent" then
      orderby = "DateSent"
      neworder= "DateSent Desc"
end if


if Request.QueryString ("orderby") =  "DateSent Desc" then
      orderby =  "DateSent Desc"
      neworder= "DateSent"
end if


'Response.Write orderby

      'Build the SQL Statement
      

if request.querystring ("what") = "" then
'Search for all messages sent today

select Case number2find
            
            
            
            Case Else
            
            strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
                                    "FROM  messagelog "&_
                                    "WHERE  DateSent  >= '"& startdate & "' "&_
                                    "AND DateSent  <='"& enddate &"' "&_
                                    "AND MessageStatus LIKE '%"& messagestatus &"%' "&_
                                    "AND UserName = '"&  Request.Cookies  ("web2sms")("username")  &"' "&_
                                    "ORDER BY " & orderby &" ;"
                                    
            
      End Select
      
      
      '-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      else
      
      '-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      
            select Case number2find
            
            Case "%"
            'Find any number
            if ISNULL (messagestatus) then
                              
            strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
                                    "FROM  messagelog "&_
                                    "WHERE  DateSent  >= '"& startdate & "' "&_
                                    "AND DateSent  <='"& enddate &"' "&_
                                    "AND UserName = '"&  Request.Cookies  ("web2sms")("username")  &"' "&_
                                    "AND MessageStatus IS NULL "&_
                                    " ORDER BY " & orderby &" ;"
                                    
            Else if messagestatus = "all" then
            
                  strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
                                    "FROM  messagelog "&_
                                    "WHERE  DateSent  >= '"& startdate & "' "&_
                                    "AND DateSent  <='"& enddate &"' "&_
                                    "AND UserName = '"&  Request.Cookies  ("web2sms")("username")  &"' "&_
                                    " ORDER BY " & orderby &" ;"
                                    
                        else
                                    strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
                                    "FROM  messagelog "&_
                                    "WHERE  DateSent  >= '"& startdate & "' "&_
                                    "AND DateSent  <='"& enddate &"' "&_
                                    "AND UserName = '"&  Request.Cookies  ("web2sms")("username")  &"' "&_
                                    "AND MessageStatus LIKE '%"& messagestatus &"%'  "&_
                                    " ORDER BY " & orderby &" ;"
                        End if
                  end if
                                    
                              
            Case Else
            'Find a specific number
            
            strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
                                    "FROM  messagelog "&_
                                    "WHERE  DateSent  >= '"& startdate & "' "&_
                                    "AND DateSent  <='"& enddate &"' "&_
                                    "AND Phonenumber  LIKE '%"& number2find &"%' "&_
                                    "AND UserName = '"&  Request.Cookies  ("web2sms")("username")  &"' "&_
                                    "ORDER BY " & orderby &" ;"
                                    
            
      End Select
      
      end if
      '-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ' Execute our query using the connection object.  It automatically
      ' creates and returns a recordset which we store in our variable.
      Set rstSearch = Server.CreateObject("ADODB.Recordset")
      rstSearch.PageSize  = PAGE_SIZE
      rstSearch.CacheSize = PAGE_SIZE

      ' Open our recordset
      'Response.Write "<br>"& StrSQL & "<br>"
      'Response.End
      
      
      rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText

      ' Get a count of the number of records and pages
      ' for use in building the header and footer text.
      iRecordCount = rstSearch.RecordCount
      iPageCount   = rstSearch.PageCount
%>


<%
if request.querystring ("what") = "" then
'Do Nothing
%>

<%else%>

<h4>A search for messages sent between <%=MyDateFormat(startdate)%> and <%=MyDateFormat(enddate)%> has the following results.</h4>
<%
      If iRecordCount = 0 Then
            ' Display no records error.
            %>
            <p>
            <font color="red" size=3>No records found.  Please try again.</font>
            </p>
            <%
      Else
              if Request.QueryString ("export")="yes" then
                      
Function dbDate(dt)
        dbDate = year(dt) & left("00",2-len(month(dt))) &_
        month(dt) & left("00",2-len(day(dt))) & day(dt) '&_
        '" " & formatdatetime(dt,4)
End Function
                ' Name for the ouput document
                filename  =  "ReportResults-" &  dbDate(now) & Left(Request.Cookies("web2sms")("lastname"),4)
                file_being_created="./export/" & filename & ".xls"

                ' create a file system object
                set fso = createobject("scripting.filesystemobject")

                ' create the text file - true will overwrite any previous files
                ' Writes the db output to a .xls file in the same directory
                Set act = fso.CreateTextFile(server.mappath(file_being_created), true)


                    ' All non repetitive html on top goes here
                        act.WriteLine("<html><body>")
                        act.WriteLine("<table border=""1"">")
                        act.WriteLine("<tr>")
                        act.WriteLine("<th nowrap>Originating Time</th>")
                        act.WriteLine("<th nowrap>Destination Number</th>")
                        act.WriteLine("<th nowrap>Status</th>")
                        act.WriteLine("<th nowrap>SMS Code</th>")
                        act.WriteLine("<th nowrap>Message</th>")
                        act.WriteLine("</tr>")
              'Create the data and then write it to an xls file and display the link for the file.
              Do While Not rstSearch.EOF
              
                                  Set objDB = Server.CreateObject("ADODB.Connection")
                                    objDB.Open DB_CON


                                MySQL="Select errormessage,errorcode from tbl_errormessages "&_
                                              "where errorcode ='"&  rstSearch.Fields("MessageStatus").Value &"' ;"

                             
                          Set ObjRs=objDB.Execute(MySQL)


                                            if objrs.eof then
                                              
                          
                                                    if ISNULL(rstSearch.Fields("MessageStatus").Value) then
                                                            reporttrans = "  Awaiting Report "
                                                   
                                                    else

                                                         reporttrans = "Error"
                                                   end if
                                                   
                                            else
                                                if ISNULL(rstSearch.Fields("MessageStatus").Value) then
                                                        reporttrans = " Awaiting Report "
                                                  else
                                                          reporttrans =  objrs("errormessage")
               
                                                  End if

                                        end if

                                objRS.close()
                            Set objRS = Nothing
                          
                        Act.WriteLine("<tr>")
                        act.WriteLine("<td align=""right"">" &  MyDateFormat(rstSearch.Fields("DateSent").Value) & "&nbsp;<br>"&_
                                        "@<br>&nbsp;" & Right(rstSearch.Fields("timesent").Value,10) & "</td>" )
                        act.WriteLine("<td align=""right"">'" & rstSearch.Fields("phonenumber").Value  & "'</td>" )
                        act.WriteLine("<td align=""right"">"  & reporttrans & "</td>")
                        act.WriteLine("<td align=""right""> "  &  rstSearch.Fields("MessageStatus").Value   & "</td>")
                        act.WriteLine("<td align=""right"">"& rstSearch.Fields("Message").Value  & "</td>")
                        act.WriteLine("</tr>")

            ' move to the next record
                rstSearch.movenext

' return to the top of the for - next loop
' change this to "loop" to output all the records
' and the corresponding for statement above should be changed also
loop

' All non repetitive html on top goes here
act.WriteLine("</table></body></html>")

' close the object (excel)
act.close
            
              
' Writes a link to the newly created excel in the browser
resultcode = "<hr width=50% color=black><span align=center><h4>Excel Document Created Successfully</h4> The file "& "<a target='_blank' href='" & file_being_created &"'>"& filename &"</a> has been created <br><br>Click the file name to download</span><hr width=50% color=black>"

response.Write resultcode
'End of Export!
              else
              
            ' Move to the page we need to show.      
            rstSearch.AbsolutePage = iPageCurrent

            ' Show a quick status line letting people know where they are:
            %>
            <hr>
            <center>
            <p><b><u>
            <%= iRecordCount %> Records Found.
            Displaying page <%= iPageCurrent %>
            of <%= iPageCount %>:
            </u></b></p></center>
            <%
            ' Display a table of the data in the recordset.  We loop through the
            ' recordset displaying the fields from the table and using MoveNext
            ' to increment to the next record.  We stop when we reach EOF.
            ' For fun I'm combining some fields and showwing you can do more then
            ' just spit out the data in the form it is in in the table.
            
            %>
            
            <table border="0" align=left width=100% ID="Table1" cellpadding=2 cellspacing=2>
                          <tr>
                            <td align=center class="tdheading"><a href="<%= strURL %>?what=<%=Request("what")%>&orderby=<%=neworder%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&status=<%=messagestatus%>">Originating Time</a></td>
                            <td align=center class="tdheading"> Destination Number</td>
                            <td align=center class="tdheading">Status</td>
                            <td align=center class="tdheading">Message</td>
                          </tr>
                          <%
            Recordcount = 0
            Do While Not rstSearch.EOF And recordcount < PAGE_SIZE
                  
            'this code alternates the color of the table rows...
      iCount = iCount + 1
      If iCount Mod 2 = 0 Then
            sRowColor = "#8F9AF7"
      Else
            sRowColor = "#FFFFFF"
      End If

                  %>
                  <tr align=center valign=top bgcolor="<%=sRowColor%>" >
                            <td align=center valign=top class=inputbox><%= MyDateFormat(rstSearch.Fields("DateSent").Value) %>&nbsp;<br>
                              @<br>
                              &nbsp;<%= Right(rstSearch.Fields("timesent").Value,10) %></td>
                            <td align=center valign=top class=inputbox><%= rstSearch.Fields("phonenumber").Value %></td>
                            <td align=center valign=top class=inputbox><!--#include file="report_status.asp"--></td>
                            <td align=left valign=top class=inputbox width=30%><%=rstSearch.Fields("Message").Value%></td>
                          </tr>
                          <%
                  recordcount = recordcount + 1
                  rstSearch.MoveNext
            Loop
            %>
                          <tr>
                            <td colspan=4 align=center valign=top> <TABLE WIDTH="100%" BORDER="0" CELLSPACING="2" CELLPADDING="2" ID="Table3" align=center>
                                <TR>
                                  <TD align=center valign=middle> <%
            ' Now we need to show our navigation links:
            
            ' Show "previous" and "next" page links which pass the page to
            ' view our search parameter.  You could also use form buttons
            ' but I find this looks better.
            If iPageCurrent > 1 Then
                  %> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= iPageCurrent - 1 %>">[<<
                                    Prev]</a> <%
            End If

            ' You can also show page numbers:
            For I = 1 To iPageCount
                  If I = iPageCurrent Then
                        %> <%= I %> <%
                  Else
                        %> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= I %>"><%= I %></a> <%
                  End If
            Next 'I

            If iPageCurrent < iPageCount Then
                  %> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= iPageCurrent + 1 %>">[Next
                                    >>]</a> <%
            End If
            %> </p>
            
            
            
            
            <%
            End if
      End If

      ' Close our recordset and connection and dispose of the objects
      rstSearch.Close
      Set rstSearch = Nothing
      cnnSearch.Close
      Set cnnSearch = Nothing
'End If
%>
<%end if%>

</TD>
                                </TR>
                              </TABLE></td>
                          </tr>
                        </table>

0
crisco96Commented:
souldj your last comments are a completely different question than what you originally posted please ask another question.
0
souldjAuthor Commented:
ok.
0
souldjAuthor Commented:
Excellent Help Guys

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.