Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 762
  • Last Modified:

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

0
siblight
Asked:
siblight
  • 9
  • 6
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
Ashish PatelCommented:
Yes that is the last option and i guess it should work for you.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now