Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    May 2011
    Location
    Philadelphia
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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:
    Code:
    <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
    Code:
    <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:
    Code:
    <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:
    Code:
    <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!
    Last edited by SofaKing; 05-01-2011 at 11:54 PM.

  2. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,698
    Thanks
    93
    Thanked 4,947 Times in 4,908 Posts
    How hard will you kick yourself??
    Code:
    <input name="noteSubject" size="50">
    ...
    <textarea name="note" cols="85" rows="15"></textarea>
    But then:
    Code:
    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.
    Be yourself. No one else is as qualified.

  3. Users who have thanked Old Pedant for this post:

    SofaKing (05-02-2011)

  4. #3
    New to the CF scene
    Join Date
    May 2011
    Location
    Philadelphia
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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.

  5. #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,659
    Thanks
    6
    Thanked 150 Times in 141 Posts
    You aren't using mysql at all or this is wrong in your code:
    Code:
    conn.Provider="Microsoft.Jet.OLEDB.4.0"

  6. #5
    New to the CF scene
    Join Date
    May 2011
    Location
    Philadelphia
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  7. #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,698
    Thanks
    93
    Thanked 4,947 Times in 4,908 Posts
    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:
    Code:
    <%
    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??
    Be yourself. No one else is as qualified.

  8. #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,698
    Thanks
    93
    Thanked 4,947 Times in 4,908 Posts
    Ahhh...just noticed something:
    Code:
    sql="INSERT INTO notes (userName,noteSub,"
    ...
    versus
    Code:
    rs.open "SELECT * FROM note WHERE ...
    Is it as simple as that??
    Be yourself. No one else is as qualified.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •