HELP! ASP form input pass to ADO SQL clause to query DB

[SofaKing]

:confused: I am a university student trying to complete my independent study which was to build a simple dynamic web page. I am using ASP, ADO, and SQL to do this. There are some bugs that I just can't figure and their driving me crazy. Basically, the form (.html)page takes 3 input values: userName, noteSub, and myNote. These are then passed to the (.asp)page to insert into the DB. My first problem is that only the userName is being inserted into the DB; noteSub and myNote are not. Below is the code for the .html form page and .asp page that inserts the values. I also want to be able to allow users to display their specific notes via query.

*addnote.html:

<form action="addnote.asp" method="post" name="noteIN" id="noteIN">
<table align="left">
<tr>
<td>Username:</td>
<td><input name="userName"></td>
</tr><tr>
<td>Subject :</td>
<td><input name="noteSubject" size="50"></td>
</tr><tr>
<td>Note :</td>
<td><textarea name="note" cols="85" rows="15"></textarea></td>
</tr>
</table>


<center>
<input name="addNote" type="submit" id="addNote" value="Add Note">
<input name="cancelREG" type="reset" id="cancelREG" value="Cancel">
</center>
</form>
</div>

*addnote.asp
<successtemplate>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/version2/note.mdb"

sql="INSERT INTO notes (userName,noteSub,"
sql=sql & "myNote)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("userName") & "',"
sql=sql & "'" & Request.Form("noteSub") & "',"
sql=sql & "'" & Request.Form("myNote") & "')"

on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>
</successtemplate>

When submitted, it displays that 1 record has been added, but noteSub and myNote are not!?

Then i want to be able to have users input their username and display their specific notes. This is where my biggest headache is. I have a form page

*shownoteID.html:
<form action="display.asp" method="post" id="retrieve">
<table align="center">
<tr>
<td>Username :</td>
<td><input name="userName"></td>
</tr>
</table>

<br /><br />
<center>
<input name="getNote" type="submit" id="getNote" value="Get Notes!">
<input name="cancelQry" type="reset" id="cancelQry" value="Cancel">
</center>
</form>

Then, the query is really where im lost. I have an .asp page:

*display.asp:
<successtemplate>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/version2/note.mdb"


set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM note WHERE userName='" & Request.Form("userName") & "'",conn
do until rs.EOF
for each x in rs.Fields
Response.Write(x.name)
Response.Write(" = ")
Response.Write(x.value & "<br />")
next
Response.Write("<br />")
rs.MoveNext
loop

rs.close
conn.close
%>

This is supposed to display the specific users notes. Any suggestions on what I am doing wrong?!! I cant seem to figure this out!!

I want to pass the userName value to the ADO SQL clause so that it will only display the users notes.
HELP!

[Old Pedant]

How hard will you kick yourself??

<input name="noteSubject" size="50">
...
<textarea name="note" cols="85" rows="15"></textarea>


But then:

sql=sql & "'" & Request.Form("noteSub") & "',"
sql=sql & "'" & Request.Form("myNote") & "')"


This has nothing to do with MySQL, by the by. It's strictly an ASP question.

[SofaKing]

Wow. I really am kicking myself about those ASP errors! Thanks a million Old Pedant. The MySQL part of my question was specifically centered around the last two code segments where I am trying to take a form input value and use it to form; basically pass it from the ASP to SQL to form a dynamic query. I believe it should be a statement something like:

rs.open "SELECT noteSub, note
FROM note WHERE userName='" & Request.Form("value") & "'",conn


Again, thanks for the help, if a different forum category would better suit my questions please advise.

[guelphdad]

You aren't using mysql at all or this is wrong in your code:
conn.Provider="Microsoft.Jet.OLEDB.4.0"

[SofaKing]

I guess it falls more under ADO and ASP. Aren't there SQL statements within the ASP and ADO script? I feel like there are no real good sources of info on the net about this specific topic. That's why I'm here. :confused:

[Old Pedant]

MySQL is a *specific* database. Started as an Open Source project, was eventually bought by Sun, and of course is now part of Oracle. There is still a 'Community" edition that is free.

You are using an Access database. Which is a Microsoft product.

Most any database uses the SQL LANGUAGE to communicate with it, but there are many many differences between the various implementations of SQL. A lot of stuff is generally the same in all, but especially DateTime and String manipulations are vastly different between them.

ANYWAY...

I don't see anything obviously wrong with this code of yours:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/version2/note.mdb"


set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM note WHERE userName='" & Request.Form("userName") & "'",conn
do until rs.EOF
for each x in rs.Fields
Response.Write(x.name)
Response.Write(" = ")
Response.Write(x.value & "<br />")
next
Response.Write("<br />")
rs.MoveNext
loop

rs.close
conn.close
%>

What *specifically* is the problem??

[Old Pedant]

Ahhh...just noticed something:

sql="INSERT INTO notes (userName,noteSub,"
...

versus

rs.open "SELECT * FROM note WHERE ...

Is it as simple as that??