Results 1 to 10 of 10

Thread: ASP : adding to database

  1. #1

    ASP : adding to database

    OK, i have a really simple and quick question... well actaully 2

    q #1

    i have one main file called function.asp that deals will all the database related things. EX. adding a news update... hte from action would be function.asp?method=addnews.

    is this a bad thing to do, or is it ok?


    Q. #2
    Im making a news updating script, and im running into troubles.

    this is what i have....
    Code:
     <%
    Conn = Server.CreateObject("ADODB.Connection")
    lpath =  "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("main.mdb")
    Conn.Open(lpath)
    set RS = Server.CreateObject("ADODB.Recordset")
    RS.Open "updates", Conn
    RS.AddNew
    RS("title") = Request.Form("title")
    RS("body") = Request.Form("body")
    RS("when") = Now()
    RS("day") = DatePart("d", Date())
    RS("month") = DatePart("m", Date())
    RS("year") = DatePart("yyyy", Date())
    RS.Update
    RS.Close
    Set RS = Nothing
    Conn.close
    set Conn = nothing
    Response.Write "sdfgfdsg" 'to see if it works
    %>
    do you see any problems with it? all im getting is 500 errors.

    thanks for any help
    -Naaman

  2. #2
    10
    posts
    Registered User
    Having all database related functions in one file is a good idea, but personally I wouldn't post a form directly to that file (i.e. have the name of that file as the value of the action attribute). What you could do instead is include your database file whenever you need to perform database operations. For example, if you have a newsupdate.asp file that has the form for adding news, use the #include statement to include your functions.asp into that file. Then you can post the newsupdate.asp to itself, use the Request object to see what operations needs to be performed, and call the appropriate function in your functions.asp. Apart from variable and constant declarations, all the code in functions.asp needs to be inside functions, otherwise it will be executed every time the file is included into another file.

    As for your Access problem, haven't used Access in years, so can't really tell right away where the problem might be. What's the first error message?

    Juha

  3. #3
    1,806
    posts
    My Pimp Hand is Strong
    You're like a program written entirely in c. No Class

  4. #4
    Ok, i looked at that page, and i have never seen anything like that before.

    i looked back over the coding i had, and i now have this....

    Code:
    <%
    Conn = Server.CreateObject("ADODB.Connection")
    lpath =  "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("main.mdb")
    Conn.Open(lpath)
    strSQL = "SELECT * from updates"
    set RS = Server.CreateObject("ADODB.Recordset")
    RS.Open strSQL, Conn
    RS.AddNew
    RS("title") = Request.Form("title")
    RS("body") = Request.Form("body")
    RS("when") = Now()
    RS("day") = DatePart("d", Date())
    RS("month") = DatePart("m", Date())
    RS("year") = DatePart("yyyy", Date())
    RS.Update
    RS.Close
    Set RS = Nothing
    Conn.close
    set Conn = nothing
    Response.Write "STEP1"
    %>
    If, anyone could take a look at it, and see if theres anything wrong, that would be great.

    thanks
    -Naaman

  5. #5
    10
    posts
    Registered User
    Well, the Conn variable is an object, so to set a value for it (or because it's an object a reference to a value) you need to use the Set keyword:

    Code:
     Set Conn = Server.CreateObject("ADODB.Connection")
    I would strongly suggest that you use the Access OLEDB provider instead of the native Access driver. It doesn't make things any more complicated, it's probably a better choice performance-wise too (although if you're using Access you can't really expect much in that respect) and if you ever need to port your code to use a proper database system, you won't have to make any changes to your code (other than changing the OLEDB provider declaration).

    Use the INSERT SQL statement (as in norie's answer) instead of creating a recordset. It's faster and makes more sense. The downside is that it's also vulnerable to SQL injections, so make sure the form fields do not contain any SQL commands, if the form is accessible to all.

    Don't use reserved words (in this case day, month, year) as table field names. Your queries will not work, if you do.

    When you load the page from the web server, the web server has to be able to access the physical file and have the right to update it. So make sure the user account that the web server is using for anonymous access (unless your site uses some sort of an identification method) has write permission to your main.mdb file.

    One more thing. Using dates with the INSERT statement can be a pain, because you always have to use the right date format. In your case, since you're inserting the current date/time, let Access deal with it. Change the design of your updates table so that the when field has a Default Value of Now(). You could do a similar operation to your day, month, and year fields (or item_day, item_month, and item_year as they are in my example).

    So here's the actual code that I got to work. It has a addNewsItem function with the news title and body as it's parameters. It also has an error message parameter that you can display to the user, if the function returns false (i.e. fails to update your database). Ideally you'd have the function in one file that you include to the file where your form is, but for simplicity I've got the function and it's caller in the same file.

    Code:
    Option Explicit 
     
    Dim strErrorMessage
     
    If (addNewsItem("My headline", "And the news body", strErrorMessage) = True) Then
       Response.Write "Hey it worked!"
    Else
       Response.Write strErrorMessage
    End If
     
    Function addNewsItem(strTitle, strBody, strErrorMessage)
       On Error Resume Next
       Dim objConn
       Dim strSQL
       Dim objRS
     
       Set objConn = Server.CreateObject("ADODB.Connection")
       objConn.Provider="Microsoft.Jet.OLEDB.4.0"
       objConn.Mode = 3
     
       strSQL = "INSERT INTO updates (title, body, item_day, item_month, item_year)"
       strSQL = strSQL & " VALUES ("
       strSQL = strSQL & "'" & strTitle & "', "
       strSQL = strSQL & "'" & strBody & "', "
       strSQL = strSQL & DatePart("d", Date()) & ", " 
       strSQL = strSQL & DatePart("m", Date()) & ", " 
       strSQL = strSQL & DatePart("yyyy", Date())
       strSQL = strSQL & ")"
     
       objConn.Open Server.MapPath("main.mdb")
       objConn.Execute(strSQL)
       objConn.Close
       If (Err.number <> 0) Then
    	  strErrorMessage = Err.Description
    	  addNewsItem = False
       Else
    	  addNewsItem = True
       End If
     
       Set objConn = Nothing
     
       On Error GoTo 0
    End Function
    Last edited by juhap; March 13th, 2005 at 05:45 AM.

  6. #6
    thanks alot man.

    i tried the following code in the addnews.asp page

    Code:
     <%
    <%
    Option Explicit 
     
    Dim strErrorMessage
     
    If (addNewsItem("My headline", "And the news body", strErrorMessage) = True) Then
       Response.Write "Hey it worked!"
    Else
       Response.Write strErrorMessage
    End If
     
    Function addNewsItem(strErrorMessage)
       On Error Resume Next
       Dim objConn
       Dim strSQL
       Dim objRS
     
       Set objConn = Server.CreateObject("ADODB.Connection")
       objConn.Provider="Microsoft.Jet.OLEDB.4.0"
       objConn.Mode = 3
     
       strSQL = "INSERT INTO updates (title, body, user, item_day, item_month, item_year)"
       strSQL = strSQL & " VALUES ("
       strSQL = strSQL & "'" & Request.Form("title") & "', "
       strSQL = strSQL & "'" & Request.Form("body") & "', "
       strSQL = strSQL & "'" & Session("username") & "', "
       strSQL = strSQL & DatePart("d", Date()) & ", " 
       strSQL = strSQL & DatePart("m", Date()) & ", " 
       strSQL = strSQL & DatePart("yyyy", Date())
       strSQL = strSQL & ")"
     
       objConn.Open Server.MapPath("main.mdb")
       objConn.Execute(strSQL)
       objConn.Close
       If (Err.number <> 0) Then
       strErrorMessage = Err.Description
       addNewsItem = False
       Else
       addNewsItem = True
       End If
     
       Set objConn = Nothing
     
       On Error GoTo 0
    End Function
    %>
    %>
    i tried putting that code in both the head and body of the page, and it didnt make a difference

    All i get when i go tothe page is a 500 error.

    im still fairly new to asp, so i might be making a very basic mistake.

    thanks for all the heelp
    -Naaman

  7. #7
    10
    posts
    Registered User
    Your function declaration addNewsItem has only one parameter, but you're sending it three parameters when you're calling it. So your function call should look like this:
    Code:
    If (addNewsItem(strErrorMessage) = True) Then
    Also, if you're using IE, turn off the friendly error messages, so you should see a more detailed version of the error message (Tools / Internet Options / Advanced, uncheck Show friendly HTTP error messages).

  8. #8
    OK!

    thanks so much for your help so far

    It works now.... exept.....

    i get this error a the to of the page

    "Syntax error in INSERT INTO statement. "

    i dont see anyhting wrong with it.... here it is
    Code:
    ....
     strSQL = "INSERT INTO updates (title, body, user, item_day, item_month, item_year)"
    ...

    and one more thing. should i put this code in the head of the page? or the body(where it is now)

    thanks soooo much
    -Naaman

  9. #9
    Bump

  10. #10
    10
    posts
    Registered User
    It's server-side code, so it's not that critical where the code is. It will always be executed before the page is returned to the browser, so the browser never sees it. I would place it in a separate file, but if you don't want to do that, place it before your HTML code. Mixing HTML and ASP code doesn't make the code very readable.

    Did you rename the fields in your database table, so that they match the field names in your INSERT statement? Does the field name count in your INSERT statement match the count of the corresponding values (the VALUES part or your INSERT statement)?

    I can only guess without seeing the value of your strSQL variable (Response.Write strSQL) and your code so far.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012