A problem displaying data in forms from database

[HarryGreiner]

I'm having a problem sorting out what I'm sure is a simple problem:

I have a small form which pulls a course title from a database of courses and puts it into a drop down box .... so far, so good.

<input name="stitle" type="hidden" id="stitle" value="title" onFocus="this.blur()"><SELECT name="entry">
<option value="">Select Course from here</option>
<%
Set oRs=Server.CreateObject("adodb.recordset")
strSQL = "SELECT DISTINCT title FROM Courselist ORDER BY title"
oRs.Open strSQL, conn

Do while not oRs.EOF
Response.Write "<OPTION VALUE = '" & oRs ("title") & "'>"
Response.Write oRs("title") & "</Option>"
oRs.MoveNext
loop
%>
</SELECT>

Okay. So far, so good. That all works a treat.

Now, when the user submits this little form it goes to an asp page which extrapolates the data for that course into another form to be amended and corrected - here's a chunk of it below:

<%

Dim SqlJunk


Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/courses.mdb")

SqlJunk = "SELECT * FROM courselist"

If Request.Form("stitle") = "title" Then
SqlJunk = SqlJunk & " WHERE title LIKE '%" & Request.Form("entry") & "%' ORDER BY Title"
End If

Set rsWebsite = Server.CreateObject("ADODB.Recordset")
rsWebsite.Open SqlJunk, dbGlobalWeb, 3
%>
<%
If rsWebsite.BOF and rsWebsite.EOF Then%>

<h2 align="center">We did not find a match!<br>Click <a href="coursesearch.asp">here</a> to try a new search</h2>
<%Else%>


<%If Not rsWebsite.BOF Then%>

<%
Do While not rsWebsite.EOF
'Write the HTML to display the current record in the recordset
Response.Write ("<div><span>Course Title</span><input name=Course Title type=text size=92 value=")
Response.Write (rsWebsite("title"))
Response.Write ("></div><div><span>School</span><input name=school type=text size=75 alt=length|2 value=")
Response.Write (rsWebsite("school"))
Response.Write ("></div><div><span>What level is this course</span><input name=Course Level type=text size=75 alt=length|2 value=")
Response.Write (rsWebsite("level"))
Response.Write ("></div><div><span>How long does the course last?</span><input name=Course lasts for type=text size=75 alt=length|2 value=")
Response.Write (rsWebsite("lastsfor"))
Response.Write ("></div>")
'Move to the next record in the recordset
rsWebsite.MoveNext

Loop
%>
<%End If%>
<%End If%>
<%
rsWebsite.Close
dbGlobalWeb.Close
%>

Again, all seems to work just dandy EXCEPT FOR ONE THING ... where the course or school or whatever is more than one word (and most of them are) it doesn't display the rest of the words in the text box - only the first word shows up. If you look in the code of the document the words are there - they just don't show up in the text box! If you send the data through and have it land in a div or whatever (as plain text) it all shows up fine.

Can anybody PLEASE enlighten me as to why it's doing this and I'd be extreely grateful if you could tell me how to fix it!

Many, many thanks - I'm not an asp man (cobbled the above together from a couple of different scripts and got it working ... well, most of it anyway).

[TheShaner]

This is because the way in which you're coding your Response.Write to output your input boxes. Like you said, you noticed that the words are there. However, your HTML is wrong.

Example:
<input type=text name=test value=Hello>
The above will have a value of Hello.
<input type=text name=test value=Hello World!>
The above will also have a value of Hello. The World! will not show due to the blank space in between.
<input type=text name=test value="Hello World!">
The above will have a value of Hello World!

See the difference? :) This is why I code all my HTML values to have quotes around it to be on the safe side, like the below.
<input type="text" name="test" value="Hello World!">
Now to do this in your Response.Write, you double up the quotes so that ASP knows you want the character " and not trying to close off your Response.Write statement. This is called "escaping". So must "escape" your quotes by double quoting. In many other languages, they use the backslash \ to escape characters, but we all know how MS likes to be different :p
So to finish the example:
Response.Write("<input type=""text"" name=""test"" value=""Hello World!"">")
EDIT: Here's an example from your code. It's the first 3 lines of your loop.
Response.Write ("<div><span>Course Title</span><input name=""Course Title"" type=""text"" size=""92"" value=""")
Response.Write (rsWebsite("title"))
Response.Write ("""></div><div><span>School</span><input name=""school"" type=""text"" size=""75"" alt=""length|2"" value=""")
The blue quotes are your beginning and ending quotes for the Response.Write statement and the red doubled-up quotes are the escaped quotes that will display one " to your page.

That line may look a bit confusing, but after a while, you get used to it, haha. Hope this helps you out!

-Shane

[HarryGreiner]

The Shaner - you are a gentleman and a scholar! Works (of course) and taught me why it works in the process. Exactly what I needed - you've made my day!!

:thumbsup:

[TheShaner]

Anytime! I edited my post to show you an example of how to do it with the code you have. Notice the highlighted quotes. I explain that underneath the code.

Also, I noticed that the name of one of your input boxes is Course Title. For the name and also id attributes, you cannot use a space in your value for them. It must be one word (if somehow I'm wrong, it's still good practice ;) haha). So change your input box's name to: CourseTitle.

-Shane

[HarryGreiner]

Thanks again Shane, the example was a nice touch.

I agree about the name of the input but the contents of this form get mailed to a human for checking, not directly back to the database and the mailer is quite happy with two words - and the humans are unhappy with one word (go figure).

However, thinking ahead to the part where the "checkers" have to upload to the database I'd better change it to one word all the way through it's going to cause problems then (I hadn't got to that point yet).

Thanks again - saved me a lost of wasted time I think.

[TheShaner]

Haha, I see what you mean. I just did a test and I was wrong in my last reply (although you already said it worked, I had to see for myself, haha). Yeah, it works fine using two words for a name. It's just generally good practice to keep them one, because it is much easier to work with.

As for the DB, they'll only be uploading the values into it, so the structure of the name should not matter.

Like I mentioned before, it's just generally easier to work with a one word name than two, but it's your preference (or rather your company's preference, lol).

-Shane

[HarryGreiner]

"As for the DB, they'll only be uploading the values into it, so the structure of the name should not matter"

I've been thinking about that while we've been talking and I think I'll try to keep everything matching to the database (single names). I'll probably try and make this form email and send the contents to a "staging" database. Then the "checkers" can check the amendments on the email - access another form like this to make any of their alterations and then upload the amended amends to the original databse ... should save them time, add to my security as I won't have to give them access to the database, and it feels like a nice, slick, logical solution to a long-running corporate communication problem (kudos for me, holiday in the Bahamas, marry the bosses daughter etc.).

Cheers Shane - looks like you've changed my life!

[TheShaner]

Sounds like you're good to go then!

And don't forget the big Christmas bonus too, haha. I sure will be looking forward to mine :thumbsup:

-Shane