ASP/ADO - Hyperlink - Question

[Gilmar]

Hello,

I am currently building a library page for a company’s intranet. I am not too familiar with ASP; so I basically used w3schools.com to learn.

I mostly used the ADO tutorials to learn how to display values from my library database and I was also able to use their demo to create a library admin panel in order to facilitate updates.

I have 4 main columns from my database that are currently displaying on my asp page:

Title – Author - Subject, - Status


I was wondering:

Could the values from the “Title” column, each carry a different hyperlink?

I would like a new resized window to appear when a user clicks on a book title.


If any other information is needed, I will gladly provide and thanks.

[Kuriyama]

I was wondering:

Could the values from the “Title” column, each carry a different hyperlink?

I would like a new resized window to appear when a user clicks on a book title.


If any other information is needed, I will gladly provide and thanks.

So you have some sort of page that lists the data you have you in DB correct?

This will get sort of complicated for someone that is new to development, but should be able to do it.

1.) Like you said, create your links and append data in the querystring.

Example


<a href="/book_editor.asp?title=[title]&Author=[Author]&Subject=[Subject]


The blue text is your query string, and the red text is a request handling page that you'll need to create.

2.) Create your handling page. You'll need to look at the incoming request and pull data out of the query string. Here is a pseudo code examples. Make sure that you escape the ' character out of the incoming request!

title = Replace(Request("title"), "'", "''")
author = Replace(Request("author "), "'", "''")
subject= Replace(Request("subject"), "'", "''")

sqlText = "SELECT * FROM table WHERE title = '" & title & "' AND author = '" & author & "' AND subject = '" & subject & "'"

Set objRS = Server.CreateObject("ADODB.RecordSet")
set objConn = Server.CreateObject("ADODB.Connection")

'Set up your connection string stuff here

objRS.open objConn, sqlText, 0, 1

'Spit up your results.


3.) Create a form and populate the data from SQL.

4.) Create update functionality. you can do this with your handler page, or create another handler page that will do updating/inserting.

To pop up the page in a new window look at the using Javascript. window.open(. . .)

I hope this helps.

[Gilmar]

Hi Kuriyama, I’d like to thank you for taking the time to reply to my post. Before I proceed with your instructions, I would just like to add more details to my previous post. It might help clarify the situation.

Yes I have a page that is displaying the 4 columns that I had mentioned previously from my db on a page.

I also created another page and used this demo: http://www.w3schools.com/ado/demo_db_list.asp

The demo helped me realize that I can add, edit, delete book values once. I got tired of editing 4 separate html pages.

If you notice on the demo, when you click the “no”, a window appears allowing users to edit the fields that was entered into the db.

I tried adding a simple tag <a href=”url”>Book Title<a> within the input box, saved it and it worked.

The problem is, I realized that by entering <a href...>, it disrupted the ascending order of the values. That's why I asked if there was any way of adding a hyperlink differently.

I apologize for the long essay and hope that I was able to clarify my situation. Any other information needed, please let me know. I will proceed now and follow your instructions.

Thanks

[Kuriyama]

The problem is, I realized that by entering <a href...>, it disrupted the ascending order of the values. That's why I asked if there was any way of adding a hyperlink differently.

I don't understand what you mean by it's no longer ordering the values correctly.

Actually, that w3schools demo is exactly what I'm talking about. If you follow it, you will be able to do what you need to do.

One thing I noticed about that demo is that w3schools most likely has a primary key on their table the "No" column. Since your table doesn't have a primary key, you can't follow their example down to the letter. You'll have to make some adjustments to your SQL queries.

Would you be able to copy and paste your code for me so I can take a look at it?

[Gilmar]

Hey,

I had already succeeded in creating the demo.

My DB is:

[no], Title, Author, Subject, Status, dateadded


Back to the demo, by clicking no, it opens a window with displayed values from the db within input fields.

I would like to add a hyperlink to the "title" box. The problem is... by adding an html code like <a href> to the Title, it interferes with the ascending order of my values.

It would be easier to post a screenshot of my problem.

I added 2 screenshots as an attachment. I don't know if it will work.

[Kuriyama]

This doesn't really help, but it looks like you are having encoding problems. Send me the code please for both the list page and the detail page. It will be much faster that way.

[Gilmar]

Display Page:

<%option explicit%>
<html>
<head>
<body>
<%
dim conn,rs,x
set conn=Server.CreateObject("ADODB.Connection")
conn.provider="SQLOLEDB"
conn.open(server.mappath("DRIVER={SQL SERVER};SERVER=mtl-hq-s01;UID=ChangeManagement;" & _
"PWD=S18a$50;DATABASE=dblvl027"))
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT no,title,author,subject,status FROM it_library ORDER BY title",conn
%>
<h2>IT Library - Book List</h2>
<p style="font-size:10pt; font-weight:bold;"><a class="menu" href="javascript:this.location.reload();">Refresh to see changes </a> &nbsp;|&nbsp; <a class="menu" href="javascript:void(0)" onClick="MM_openBrWindow('add.asp','addbook','width=318,height=255')">Add a book</a></p>

<table border="0" width="100%" cellpadding="5">
<tr bgcolor="#b0c4de">
<%
for each x in rs.Fields
response.write("<th>" & ucase(x.name) & "</th>")
next
%>
</tr>
<%do until rs.EOF%>
<tr bgcolor="#f0f0f0">
<form action="db_edit.asp" method="post" target="formWin"
onsubmit="formpopup();">
<%
for each x in rs.Fields
if x.name="no" then%>
<td><input type="submit" name="no" value="<%=x.value%>"></td>
<%else%>
<td><%Response.Write(x.value)%> </td>
<%end if
next
%>
</form>
<%rs.MoveNext%>
</tr>
<%
loop
rs.close
set rs=nothing
conn.close
set conn=nothing
%>
</table>


</body>
</html>



Edit Page:


<%
no=Request.Form("no")
if no="" then response.end
set conn=Server.CreateObject("ADODB.Connection")
conn.provider="SQLOLEDB"
conn.open(server.mappath("DRIVER={SQL SERVER};SERVER=mtl-hq-s01;UID=ChangeManagement;" & _
"PWD=S18a$50;DATABASE=dblvl027"))
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Select * from it_library where it_library.[no]=" & no , conn
%>
<html>
<head>
<head>
<body style="text-align:">

<h2>Edit Book</h2>
<form method="post" action="db_submit.asp" target="_self">
<input name="no" type="hidden" value=<%=no%>>
<table width="200" border="0" cellpadding="0" cellspacing="0">
<!--DWLayoutTable-->
<tr>
<td width="200" height="28" valign="top" background="images/top_editbook.gif"></td>
</tr>
<tr>
<td height="114" valign="top"><table bgcolor="#f0f0f0">
<%
for each x in rs.Fields
if x.name <> "no" and x.name <> "dateadded" then%>
<tr>
<td><%=x.name%> </td>
<td><input name="<%=x.name%>" value="<%=x.value%>" size="20"></td>
<%end if
next

rs.close
conn.close
%>
</tr>
</table></td>
</tr>
</table>
<br />
<input type="submit" name="action" value="Save">
<input type="submit" name="action" value="Delete">
</form>


</body>
</html>

[Gilmar]

And I have another page which will be the page that will show the library to employees:

<html>

<head>

</head>

<body>



<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="SQLOLEDB"
conn.Open(Server.Mappath("DRIVER={SQL SERVER};SERVER=mtl-hq-s01;UID=ChangeManagement;" & _
"PWD=S18a$50;DATABASE=dblvl027"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Title, Author, Subject, Status FROM it_library ORDER BY Title"
rs.Open sql, conn
%>


<table width=775 border=0 cellpadding=0 cellspacing=0 background="images/bg.gif" style='width:581.25pt;
border-collapse:collapse;mso-padding-alt:0in 0in 0in 0in'>

<tr>
<td valign=top ><table border="0" width="85%" cellpadding="10" style="text-align:left">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
</tr>
<%loop
rs.close
conn.close
%>
</table>



body>

</html>

[Kuriyama]

Gilmar,

I haven't forgotten about you. I'll have a solution later today for you.

[Gilmar]

I appreciate your help. The worst part, I'm sure it's a simple solution. Just having problems explain it.

[Kuriyama]

I appreciate your help. The worst part, I'm sure it's a simple solution. Just having problems explain it.

Ok, I ported your code into my development server and everything is working correctly.

Coding wise, things are good. There are no errors. This leads me to believe that this is a data problem. Is the data in that table being used to render html else where? I think someone has wrote some HTML into the database, and I think I understand your problem now.

It's not good practice to write HTML into a unless you really need to. What you can do to correct your issue is to HTML encode data coming out of your database. In your editing file replace

<input name="<%=x.name%>" value="<%=x.value%>" size="20">

with this

<input name="<%=x.name%>" value="<%=Server.HTMLEncode(x.value)%>" size="20">

see if that works.

[Gilmar]

Wow, this code (value="<%=Server.HTMLEncode(x.value)%>")

fixed that screenshot problem that I had sent you.

Thank you so much.


I'm glad to hear that my coding is working correctly. lol

Like I was saying:

Since I can now properly add <a href> tags within the input boxes, it seems to me that the data still re-arranges itself differently. Here's a ss

I was wondering, is there a way to avoid this?

[Gilmar]

To answer your question "Is the data in that table being used to render html else where?"

Yes, I am using that table to render to another page.

[Kuriyama]

Wow, this code (value="<%=Server.HTMLEncode(x.value)%>")

fixed that screenshot problem that I had sent you.

Thank you so much.


I'm glad to hear that my coding is working correctly. lol

Like I was saying:

Since I can now properly add <a href> tags within the input boxes, it seems to me that the data still re-arranges itself differently. Here's a ss

I was wondering, is there a way to avoid this?

There is no way that you can get around the ordering. Unfortunately, you are writing html into the database which probably isn't a good idea in this case. SQL is ordering the data correctly.

<a href=".. . .
comes before
letters and numbers

[Gilmar]

That's what I figured.

It's ok, the code you sent me really helped solved the issue for me. I found a way to work around the ordering.

Thanks again for your time Kuriyama.