SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP/Access - Insert new row

    I am working on a quick and dirty cms .. I am currently able to output all my data from the db, but what I want to do is take 4 fields from a form and insert those into a new row into my db.

    I am currently using access w/ asp ...

  2. #2
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    INSERT INTO tablename(fieldname1,fieldname2) values('fieldata1','fieldata2')

    This is the syntax. Let's assume you want someone's name and age. The name is text in the db, and the age is numeric:
    Code:
    name=request.form("name")
    age=request.form("age")
    if not isempty(name) then
    	if not isempty(age) then
    		con.execute("INSERT INTO userinfo(name,age) values('" & name & "'," & age & ")")
    		response.write "process complete"
    	end if
    end if
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  3. #3
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jeremy,

    Thanks for the speedy reply....All of that makes sense...but, just a couple of ?'s....

    request.form("name")
    -I understand that's grabbing the value of the field "name" but where does this code go?

    My form is
    <form action="">
    <input>....etc....
    <input type="submit">
    </form>

    I understand the syntax..but not the placement???

  4. #4
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Okay, on one page have your form with, say:
    Code:
    <form action="userprocess.asp" method="post">
    <input type="text" name="name"><br>
    <input type="text" name="age"><br>
    <input type="submit">
    </form>
    Then, create the page userprocess.asp with (I've changed the code a bit):
    Code:
    <%
    name=request.form("name")
    age=CInt(request.form("age"))
    if not isempty(name) then
    	if not isempty(age) then
    		con.execute("INSERT INTO userinfo(name,age) values('" & name & "'," & age & ")")
    		response.write "process complete"
    	end if
    end if
    %>
    userprocess.asp would also need to have connection info:
    Code:
    	DBPath =Server.Mappath("db.mdb")
    	Set con = Server.CreateObject( "ADODB.Connection" )
    	con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPath
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  5. #5
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is more effectient:
    Code:
    <%
    name=request.form("name")
    age=CInt(request.form("age"))
    if not isempty(name) AND not isempty(age) then
       con.execute("INSERT INTO userinfo(name,age) values('" & name & "'," & age & ")")
       response.write "process complete"
    end if
    %>

  6. #6
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)


    I wasn't sure how to do double if not's, thanx mate
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  7. #7
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All right below is my code...i've created from your help .. this is my add.asp page from form action="add.asp"

    Im getting some weird errors: "Illegal assignment: 'date'"

    Any ideas? Or anything wrong w/ my code?

    --------

    <%
    Dim oConn
    Dim oRs
    Dim filePath
    Dim Index

    ' Map authors database to physical path
    filePath = Server.MapPath("db2.mdb")

    ' Create ADO Connection Component to connect
    ' with sample database

    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

    ' Execute a SQL query and store the results
    ' within recordset

    'Set oRs = oConn.Execute("SELECT * From press")
    %>

    <%

    date = request.form("date")
    author = request.form("author")
    title = request.form("title")
    content = request.form("content")

    oRs.execute("INSERT INTO press(date,author,title,content) values(" & date & "," & author & "," & title & "," & content & ")")
    response.write "Complete"

    %>

  8. #8
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    date is a vbs reserved word. Try strDate or something
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  9. #9
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well...I though I got it..but this doesn't seem to be doing the trick...

    Now I am being told: "Syntax error in INSERT INTO statement."

    My Code:------------

    <%
    Dim oConn
    Dim oRs
    Dim filePath
    Dim Index

    ' Map authors database to physical path
    filePath = Server.MapPath("db2.mdb")

    ' Create ADO Connection Component to connect
    ' with sample database

    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

    ' Execute a SQL query and store the results
    ' within recordset

    'Set oRs = oConn.Execute("SELECT * From press")

    strdate = request.form("date")
    author = request.form("author")
    title = request.form("title")
    content = request.form("content")

    oConn.execute("INSERT press(strdate,author,title,content) values('" & strdate & "','" & author & "','" & title & "','" & content & "')")

    response.write "Complete"

    %>

  10. #10
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <% 
    Dim oConn 
    Dim oRs 
    Dim filePath 
    Dim Index 
    
    ' Map authors database to physical path 
    filePath = Server.MapPath("db2.mdb") 
    
    ' Create ADO Connection Component to connect 
    ' with sample database 
    
    Set oConn = Server.CreateObject("ADODB.Connection") 
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath 
    
    ' Execute a SQL query and store the results 
    ' within recordset 
    
    'Set oRs = oConn.Execute("SELECT * From press") 
    
    strdate = request.form("date") 
    author = request.form("author") 
    title = request.form("title") 
    content = request.form("content") 
    
    oConn.execute("INSERT INTO press(strdate,author,title,content) values('" & strdate & "','" & author & "','" & title & "','" & content & "')") 
    
    response.write "Complete" 
    
    %>
    Stuff in bold is what you forgot.

  11. #11
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Man...im loving your help....I wish this would all come together..but here's my newest error:

    "Operation must use an updateable query."

  12. #12
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had this error before with my host, one of them anyway.

    The problem was that they didn't allow DSNless connections, which is what jerry has given you. You are going to have to do is create a DSN connection.

    This is caused by the internet Guest account has not been given write permission.

    I would either create a DSN connection or fone your host and ask then to give the Guest account write permission to .mdb files


  13. #13
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im just doing this all locally...

    So how would I create a "DSN Connection"??

  14. #14
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What version of windows are you using?

  15. #15
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Win 2000

  16. #16
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Enable read/write permissions for IUSR_<<MachineName>> for the database and the directory it is stored in....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  17. #17
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Beat me to it Dave

  18. #18
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You guys have been most helpful today...Awesome!!

    Well I have it up and running ... insert into the database and deleting... Now I am trying to update current articles in the db.

    I am able to output the article in form mode ready for resubmit....

    Here's My Code:
    oConn.execute("INSERT INTO press WHERE ID=" & Request("ID")"(strdate,author,title,content) values('" & strdate & "','" & author & "','" & title & "','" & content & "')")

    --Now im passing the id from the submit...but im getting the error "Syntax error in Insert Into Statement"
    Any ideas?

  19. #19
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can not us a WHERE clause in an insert statment, you can use it with SELECT and UPDATE.

    From the sound of things, you are trying to update the record, try this:
    Code:
    oConn.execute("UPDATE press SET strdate = '" & strDate & "', author = '" & author & ",title = '" & title & "',content ='" & content "' WHERE ID=" & Request("ID"))

  20. #20
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers...for that....Im sorry i've fallen into the trap of getting things done too quickly without fully understanding all the sql

    Select/Insert/Update/Input/ etc...

    Thanks for your help.

  21. #21
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem, thats whay I'm here for. If you have any more problems, just ask.


Bookmarks

Posting Permissions

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