Solved

classic asp coding / sql server

Posted on 2011-09-10
5
361 Views
Last Modified: 2012-05-12
The following codes in classic asp is working. however,
when I click on the paging like the one with 1.2.3.4
I have to click twice in order to make the part no. showing on url querystring.
When I click once only, it doesn't do anything ....

Need some expects in asp/asp.net find out for me. 500 points rewards.
<%@LANGUAGE="Vbscript" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
<% 
Category="COOLANT"
Function TotalRecordCount(Category)
SQL=" Select Top 1 PartNo, "
SQL=SQL & " Row_Number() Over(Order By PartNo) rownumber, "
SQL=SQL & " Count(*) Over() as TotalRecordCount "
SQL=SQL & " From Products "
SQL=SQL & " where Category = '" & Category & "' "
SQL=SQL & " Order By partno asc "
set dbsecurecon  = Server.CreateObject("ADODB.Connection")
dbsecurecon = "PROVIDER=SQLOLEDB;DATA SOURCE=xxx;DATABASE=aviation;User ID=sa;Password=xxx"
Set RS = Server.CreateObject("ADODB.recordset")
RS.open SQL, dbsecurecon,3,3
If Not RS.EOF Then
TotalRecordCount=Trim(RS("TotalRecordCount"))
Else
TotalRecordCount=0
End If
End Function

Function CategorySingleListInDetail(RowNumber,Category)
'SQL=" Select Top 1 PartNo, "
'SQL=SQL & " Row_Number() Over(Order By PartNo) rownumber, "
'SQL=SQL & " Count(*) Over() as TotalRecordCount "
'SQL=SQL & " From Products "
'SQL=SQL & " where rownumber = '" & RowNumber & "' "
'SQL=SQL & " Order By partno asc "
SQL=" SELECT * FROM ( "
SQL=SQL & " Select Description, PartNo, "
SQL=SQL & " Row_Number() Over(Order By PartNo) as rownumber, "
SQL=SQL & " Count(*) Over() as TotalRecordCount "
SQL=SQL & " From Products "
SQL=SQL & " where Category = '" & Category & "' ) T1 "
SQL=SQL & " WHERE rownumber = '" & RowNumber & "' "
SQL=SQL & " Order By partno asc "

Set dbsecurecon  = Server.CreateObject("ADODB.Connection")
dbsecurecon = "PROVIDER=SQLOLEDB;DATA SOURCE=xxx;DATABASE=aviation;User ID=sa;Password=xxx"
Set RS = Server.CreateObject("ADODB.recordset")
RS.open SQL, dbsecurecon,3,3
If Not RS.EOF Then
PartNo = RS("PartNo")
Description = RS("Description")
p="<table>"
p=p & "<tr><Td colspan=""3"" bgcolor=black><font color=white>"
p=p & "Category: " & Category & "</td></tr>"
p=p & "<tr><td colspan=""3"">Request a Quote:</td></tr>"
p=p & "<tr>"
p=p & "<td>" & PartNo & "</td>"
p=p & "</tr>" 
p=p & "<tr>"
p=p & "<td>" & Description 
p=p & "</td>"
p=p & "<td><br></td>"
p=p & "</tr>"
p=p & "</table>"
TotalRecordCountClient=Trim(TotalRecordCount(Category))
For i = 1  to TotalRecordCountClient
p=p & "<a href=""testplan1.asp?RowNumber=" & i  & "&Category=" & Category & "&PartNo=" & PartNo & """>" & i & "</a>" & " " 
Next
Else
p=""
End If
CategorySingleListInDetail=p
End Function
TotalCount=TotalRecordCount(Category)
RowNumber=request.QueryString("RowNumber")
if RowNumber = "" Then RowNumber = 1 End If
Response.Write CategorySingleListInDetail(RowNumber,Category)
%>
</body>
</html>

Open in new window

0
Comment
Question by:Webboy2008
  • 3
  • 2
5 Comments
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36518325
#1: This is not .net

If you want to make your code better editable and better at troubleshooting.
Then I strongly suggest that you rethink the way that you are coding it.

This is just a mark up of how it should be done, and make you can find the issue better like this, and it will hopefully inspire you to doing it this way in the future.

This also makes it easier for page layout, as the way you have it done now, you have no control over page layout what so ever.

Carrzkiss

<%@LANGUAGE="Vbscript" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
<% 
Category="COOLANT"
Function TotalRecordCount(Category)
SQL=" Select Top 1 PartNo, "
SQL=SQL & " Row_Number() Over(Order By PartNo) rownumber, "
SQL=SQL & " Count(*) Over() as TotalRecordCount "
SQL=SQL & " From Products "
SQL=SQL & " where Category = '" & Category & "' "
SQL=SQL & " Order By partno asc "
set dbsecurecon  = Server.CreateObject("ADODB.Connection")
dbsecurecon = "PROVIDER=SQLOLEDB;DATA SOURCE=xxx;DATABASE=aviation;User ID=sa;Password=xxx"
Set RS = Server.CreateObject("ADODB.recordset")
RS.open SQL, dbsecurecon,3,3
If Not RS.EOF Then
TotalRecordCount=Trim(RS("TotalRecordCount"))
Else
TotalRecordCount=0
End If
End Function

Function CategorySingleListInDetail(RowNumber,Category)
'SQL=" Select Top 1 PartNo, "
'SQL=SQL & " Row_Number() Over(Order By PartNo) rownumber, "
'SQL=SQL & " Count(*) Over() as TotalRecordCount "
'SQL=SQL & " From Products "
'SQL=SQL & " where rownumber = '" & RowNumber & "' "
'SQL=SQL & " Order By partno asc "
SQL=" SELECT * FROM ( "
SQL=SQL & " Select Description, PartNo, "
SQL=SQL & " Row_Number() Over(Order By PartNo) as rownumber, "
SQL=SQL & " Count(*) Over() as TotalRecordCount "
SQL=SQL & " From Products "
SQL=SQL & " where Category = '" & Category & "' ) T1 "
SQL=SQL & " WHERE rownumber = '" & RowNumber & "' "
SQL=SQL & " Order By partno asc "

Set dbsecurecon  = Server.CreateObject("ADODB.Connection")
dbsecurecon = "PROVIDER=SQLOLEDB;DATA SOURCE=xxx;DATABASE=aviation;User ID=sa;Password=xxx"
Set RS = Server.CreateObject("ADODB.recordset")
RS.open SQL, dbsecurecon,3,3
If Not RS.EOF Then
PartNo = RS("PartNo")
Description = RS("Description")%>
<table>
<tr><Td colspan="3" bgcolor=black><font color=white>Category: <%=Category%></font></td></tr>
<tr><td colspan="3">Request a Quote:</td></tr>
<tr>
<td><%=PartNo%></td><td></td>
</tr>
<tr>
<td><%=Description%> 
</td>
<td><br /></td>
</tr>
</table>
<%
TotalRecordCountClient=Trim(TotalRecordCount(Category))
For i = 1  to TotalRecordCountClient%>
<a href="testplan1.asp?RowNumber=<%=i%>&mp;Category=<%=Category%>&mp;PartNo=<%=PartNo%>"><%=i%></a>
<%Next
Else
p=""
End If
CategorySingleListInDetail=p
End Function
TotalCount=TotalRecordCount(Category)
RowNumber=request.QueryString("RowNumber")
if RowNumber = "" Then RowNumber = 1 End If
Response.Write CategorySingleListInDetail(RowNumber,Category)
%>
</body>
</html>

Open in new window

0
 

Author Comment

by:Webboy2008
ID: 36519271
carrzkiss:I don't need to re-arrange the codes. The codes are working.
I need someone to resolve my issues.
0
 
LVL 30

Accepted Solution

by:
Wayne Barron earned 500 total points
ID: 36519294
Then explain the issue better and I will check in on it.
And with the way that you was designing you code, makes it hard to troubleshot
Which is the reason why I provided what I did. To help troubleshot the issue.
0
 

Author Comment

by:Webboy2008
ID: 36519302
That is ok. I already found out the issue. and I just post the asp codes that I hope to write in asp.net/vb.net
can you help? Thanks
Function SingleItem(Category,RowNumber)

SQL=" SELECT * FROM ( "
SQL=SQL & " Select Description, PartNo, "
SQL=SQL & " Row_Number() Over(Order By PartNo) as rownumber, "
SQL=SQL & " Count(*) Over() as TotalRecordCount "
SQL=SQL & " From Products "
SQL=SQL & " where Category = '" & Category & "' ) T1 "
SQL=SQL & " WHERE rownumber = '" & RowNumber & "' "
SQL=SQL & " Order By partno asc "

Set dbsecurecon  = Server.CreateObject("ADODB.Connection")
dbsecurecon = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;User ID=sa;Password=xxx"
Set RS = Server.CreateObject("ADODB.recordset")
RS.open SQL, dbsecurecon,3,3
If Not RS.EOF Then
PartNo = RS("PartNo")
End If
 SingleItem=PartNo
End Function

Open in new window

0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36519780
Where are you get .net from? I do not see no .net here, but Classic ASP.
Please explain why you keep on referring to asp.net

Also, you said that you fixed the issue, what was it that you fixed?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now