Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CANT UPDATE Record Using ASP

Posted on 2007-12-06
15
Medium Priority
?
760 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:siblight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418801
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
 

Author Comment

by:siblight
ID: 20418808
I when i submit my form i only get a blank page
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418813
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:siblight
ID: 20418816
sorry i actually get this message:

Error:Syntax error (missing operator) in query expression 'ID=
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418820
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
 

Author Comment

by:siblight
ID: 20418836
PageID:>><<

0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418839
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418842
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
 

Author Comment

by:siblight
ID: 20418872
With that last portion of code i got this message:
Error:Data type mismatch in criteria expression.

Open in new window

0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418882
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
 

Author Comment

by:siblight
ID: 20418913
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418960
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20418967
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
 

Author Comment

by:siblight
ID: 20419063
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
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 480 total points
ID: 20419074
Yes that is the last option and i guess it should work for you.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question