Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

classic asp coding / sql server

Posted on 2011-09-10
5
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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 …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 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