Sign in to follow this  
blackbirdblackbird1

[web] ASP Updating Access Database

Recommended Posts

I am attempting to make my own simple CMS out of ASP and a MSAccess database and so far the biggest problem i have had is for some reason i can't update a row in a table when running on a local server (localhost). I haven't been able to test it on an outside server. Here is my code:
42 <%on error resume next
43 
44 dim no, sql
45
46 no = Request("id")
47
48 set conn=Server.CreateObject("ADODB.Connection")
49 conn.provider="Microsoft.Jet.OLEDB.4.0"
50 conn.Open "C:\Inetpub\AUSDpublic\ahs\sports.mdb"
51
52 if Request.form("action")="Save" then
53       sql="UPDATE " & score & " SET "
54       sql=sql & "date='" & Request.Form("calinput") & "',"
55       sql=sql & "score='" & Request.Form("score") & "',"
56       sql=sql & "opp='" & Request.Form("opp") & "',"
57       sql=sql & "email='" & Request.Form("email") & "',"
58       sql=sql & "opp_score='" & Request.Form("opp_score") & "',"
59       sql=sql & "dateadded='" & date() & "' WHERE " & score & ".[no]=" & no
60   conn.Execute sql, Recordsaffected
61   if err <> 0 then
62   Response.Write("You do not have permission to update this database!")
63   else
64   Response.Write("Record number " & no & " was updated.")
65   end if
66 end if
67 if Request.Form("action")="Delete" then
68       no=Request.Form("no")
69   conn.Execute "DELETE FROM " & score & " WHERE " & score & ".[no]=" & no, Recordsaffected
70   if err <> 0 then
71   Response.Write("You do not have permission to delete a record from this database!")
72   else
73   Response.Write("Record number " & no & " was deleted.")
74   end if
75 end if
76 
77 conn.close%>

I keep getting the "You do not have permission to update this database!" message on line 62. Its not much of an error message but it does tell me something isnt working. P.S. The reason I am using ASP (not ASP.NET) and MSAccess (not MySQL) is becuase I a building a website for my high school and all my school district has is Windows 2003 servers and they wont install anything for me so MSAccess whas/is the only database i could think of that didn't need anything installed on the server.

Share this post


Link to post
Share on other sites
You have so, so, many things wrong I don't know where to begin.

1. Don't use "ON ERROR RESUME NEXT". If an error occurs, you want to know about it. seriously.

2. Don't use absolute paths for files on the server - otherwise you will have to change them all between your development and production servers and it will fail if you get it wrong

3. Don't use MSAccess as the back end for a web application, it is not robust enough, not performant enough and not scalable enough.

4. Your script looks like it's probably vulnerable to SQL injection too

5. What someone will or will not install is no basis to choose a technology for a web application. You're building it, so they have to host it. You're providing a service, don't make things needlessly difficult for yourself.

Choose a technology that your can use to develop and the operations staff can support. Don't choose one otherwise. MSAccess as web backend probably fails on both counts.

Mark

Share this post


Link to post
Share on other sites
Quote:
Original post by markr
You have so, so, many things wrong I don't know where to begin.

1. Don't use "ON ERROR RESUME NEXT". If an error occurs, you want to know about it. seriously.

2. Don't use absolute paths for files on the server - otherwise you will have to change them all between your development and production servers and it will fail if you get it wrong

3. Don't use MSAccess as the back end for a web application, it is not robust enough, not performant enough and not scalable enough.

4. Your script looks like it's probably vulnerable to SQL injection too

5. What someone will or will not install is no basis to choose a technology for a web application. You're building it, so they have to host it. You're providing a service, don't make things needlessly difficult for yourself.

Choose a technology that your can use to develop and the operations staff can support. Don't choose one otherwise. MSAccess as web backend probably fails on both counts.

Mark


Now it is giving me this error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/custom_teacher_pages/news/score_submit.asp, line 60



I basically got the main part of the script from w3schools.com and then modified it.


OK what you dont understand is that I am a STUDENT building this website for my HIGH SCHOOL for a WEB CLASS. I am not getting paid for this in any way, shape or form. I would be very lucky if they even installed the .dll so that i could use MySQL.

Share this post


Link to post
Share on other sites
I'm not sure where the real problem is.
The problem however seems to be from your Query, it is malformed.

My guess would be that something doesn't look right in this line:

sql=sql & "dateadded='" & date() & "' WHERE " & score & ".[no]=" & no


Make sure the name of the fields are correct, these errors happen all the time.

Another possible problem is the date() function.


Regardless of the problem you are having, some of the points raised by markr should be taken into consideration...

For instance, imagine someone using your webform fills an entry that has ' in it and that splits your query into 2. With a little bit of work he could very easily #%^@$%-up your database.
The bottomline, NEVER use Request.Form("calinput") in a query, it must be first filtered of certain characters to make SQL injection impossible.

Otherwise good luck with your project

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this