ASP : adding to database

[dinkumrocks]

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

<%
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

[juhap]

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

[norie]

http://www.w3schools.com/ado/ado_add.asp

[dinkumrocks]

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


<%
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

[juhap]

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:


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.


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

[dinkumrocks]

thanks alot man.

i tried the following code in the addnews.asp page


<%
<%
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

[juhap]

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:

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).

[dinkumrocks]

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

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

[dinkumrocks]

Bump :)

[juhap]

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.