CANT UPDATE Record Using ASP

Hi

I am trying to update a record in a databse but it is not working.

I know its not a permissions problem because i can save / update records in other tables, which only have one record.  When i try and update a specific record i get no update permissions.

Can anyone help?
<%
no=request.querystring("PAGEID")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "g:/inetpub/wwwroot/intranet/shared/setup/site.mdb"
set rs = Server.CreateObject("ADODB.recordset")
 
 
 sql="UPDATE customers SET "
  sql=sql & "content='" & Request.Form("content") & "'"
 
  sql=sql & " WHERE ID='" & no & "'"
  on error resume next
  conn.Execute sql
 
  if err<>0 then
    response.write("No update permissions!")
  else 
    response.write("Student Page Saved!<BR>")
  end if 
 
 
conn.close
%>

Open in new window

siblightAsked:
Who is Participating?
 
Ashish PatelConnect With a Mentor Commented:
Yes that is the last option and i guess it should work for you.
0
 
Ashish PatelCommented:
Try this and if you get any errors please paste what you are getting now.


<%
no=request.querystring("PAGEID")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "g:/inetpub/wwwroot/intranet/shared/setup/site.mdb"
set rs = Server.CreateObject("ADODB.recordset")
 
 If no <> "" Then
 
 	sql="UPDATE customers SET "
  	sql=sql & "content='" & Request.Form("content") & "'"
  	sql=sql & " WHERE ID= " & no & " "
 
  	on error resume next
  	conn.Execute sql
 
  	if err<>0 then
    		response.write("Error:" & err.Description)
  	else 
    		response.write("Student Page Saved!<BR>")
  	end if 
 End If 
 
conn.close
%>

Open in new window

0
 
siblightAuthor Commented:
I when i submit my form i only get a blank page
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ashish PatelCommented:
Now use this and say me what you are getting.

<%
no=request.querystring("PAGEID")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "g:/inetpub/wwwroot/intranet/shared/setup/site.mdb"
set rs = Server.CreateObject("ADODB.recordset")
 Response.write "PageID:<" & no & ">"
 If no <> "" Then
 
       sql="UPDATE customers SET "
        sql=sql & "content='" & Request.Form("content") & "'"
        sql=sql & " WHERE ID= " & no & " "
 
        on error resume next
        conn.Execute sql
 
        if err<>0 then
                response.write("Error:" & err.Description)
        else
                response.write("Student Page Saved!<BR>")
        end if
 End If
 
conn.close
%>
0
 
siblightAuthor Commented:
sorry i actually get this message:

Error:Syntax error (missing operator) in query expression 'ID=
0
 
Ashish PatelCommented:
Okay now use this one and say me the ouput
<%
no=request("PAGEID")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "g:/inetpub/wwwroot/intranet/shared/setup/site.mdb"
set rs = Server.CreateObject("ADODB.recordset")
 
 Response.write "PageID:>>" & no & "<< <br>" 
 If no <> "" Then
 
       sql="UPDATE customers SET "
        sql=sql & "content='" & Request.Form("content") & "'"
        sql=sql & " WHERE ID= '" & no & "' "
 
        on error resume next
        conn.Execute sql
 
        if err<>0 then
                response.write("Error:" & err.Description)
        else 
                response.write("Student Page Saved!<BR>")
        end if 
 End If 
 
conn.close
%>

Open in new window

0
 
siblightAuthor Commented:
PageID:>><<

0
 
Ashish PatelCommented:
Okay so it means that the no variable which you are creating from the below line
no=request.querystring("PAGEID") is blank
so better change it to below line and try
no=request.Form("PAGEID")
0
 
Ashish PatelCommented:
Or Else just use this and let me know. Final stuff
<%
no=Request("PAGEID")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "g:/inetpub/wwwroot/intranet/shared/setup/site.mdb"
set rs = Server.CreateObject("ADODB.recordset")
 
       sql="UPDATE customers SET "
        sql=sql & "content='" & Request.Form("content") & "'"
        sql=sql & " WHERE ID= '" & no & "' "
 
        on error resume next
        conn.Execute sql
 
        if err<>0 then
                response.write("Error:" & err.Description)
        else 
                response.write("Student Page Saved!<BR>")
        end if 
conn.close
%>

Open in new window

0
 
siblightAuthor Commented:
With that last portion of code i got this message:
Error:Data type mismatch in criteria expression.

Open in new window

0
 
Ashish PatelCommented:
So, if your ID column in table is numeric then use the below line and remove single quotes from it.
Change
sql=sql & " WHERE ID= '" & no & "' "
with
sql=sql & " WHERE ID= " & no & " "
0
 
siblightAuthor Commented:
The final message i get is:

I think this is a permissions error, but what i dont understand is i can write to other tables in this mdb with no problems
Error:Operation must use an updateable query.

Open in new window

0
 
Ashish PatelCommented:
its not a permission error. Use this code below and you problem should be solved.

<%
no=Request("PAGEID")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
Conn.Mode = 3 
conn.Open "g:/inetpub/wwwroot/intranet/shared/setup/site.mdb"
set rs = Server.CreateObject("ADODB.recordset")
 
       sql="UPDATE customers SET "
        sql=sql & "content='" & Request.Form("content") & "'"
        sql=sql & " WHERE ID= " & no & " "
 
        on error resume next
        conn.Execute sql
 
        if err<>0 then
                response.write("Error:" & err.Description)
        else 
                response.write("Student Page Saved!<BR>")
        end if 
conn.close
%>

Open in new window

0
 
Ashish PatelCommented:
And finally still if you are getting the same thing then do this settings. Running ASP scripts that does insert/update query's on an Access DB.

- Go to your Control Panel
- Administrative Tools > IIS
- Expand the folder of your site/application
- Right-click the DB folder (just the folder where your .mdb is in)
- Go to "All-tasks" and click "Permissions Wizard"
- Click "Next" for 4 times, without changing anything and finish
0
 
siblightAuthor Commented:
Thanks! That code didn't help i still get:

Error:Operation must use an updateable query.

I will get my network manager to try and follow the steps in IIS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.