Solved

classic asp coding / sql server

Posted on 2011-09-10
5
381 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 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create a Dropdown list with sub group 10 43
disadvantage of code behind appoarch in asp.net 3 48
Json and ajax 1 44
PHP $_POST vs asp request 4 57
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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