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 10:54 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    SofaKing (05-02-2011)

  • #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.

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

  • #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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 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??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 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??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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