Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

classic asp coding / sql server

Posted on 2011-09-10
5
Medium Priority
?
412 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 31

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 31

Accepted Solution

by:
Wayne Barron earned 2000 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 31

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

971 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