Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 10th, 2003, 11:25 AM
Registered User
 
Join Date: Nov 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Allow SQL to update an Access database

Hi,

I'm new at this so only laugh when I'm not listening ;)

I'm trying to update certain fields in certain records of an Access database sitting on the server.

I can obtain the data using a SELECT query but when I try to update the file, I can't. I sospect it is locked as readonly. The problem is I don't quite understand how to make it writable.

An abbreviated version of the code I'm using is below. I assume that I have to add some constant to one of the "open" lines to get it to work?

1. Is my theory correct?
2. What (and where) should I add?
3. If I am totally off base, any sugegstions on where to find more info?

Thanks for helping the newbie.

Howard

------------------------------------------------

<%@ Language=VBScript %>
<%

Dim Temp_Game, Temp_Score_V, Temp_Score_H

Temp_Game = Request("Game_Edited")
Temp_Score_V = Request("Score_V_New")
Temp_Score_H = Request("Score_H_New")

set cn = server.CreateObject("ADODB.Connection")
set objRS = server.CreateObject("ADODB.Recordset")
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("/refarea/fpdb/Current_Schedule.mdb")& ";"
cn.Open

sql = "UPDATE Schedule SET Score_V = " & Temp_Score_V & ", Score_H = "& Temp_Score_H & " WHERE ID = " & Temp_Game

objRS.Open sql, cn

objRS.Close
cn.Close
 
Old November 10th, 2003, 11:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

You don't say what error you're getting, but this article addresses how to ensure you can write to your access db from asp.
http://www.aspfaq.com/show.asp?id=2062

BTW, a recordset object is not needed for an update query. U can just use the Execute method of the connection object.
 
Old November 10th, 2003, 11:53 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Your query looks fine.

1. You should check that the MDB file is not set as readonly in the file system. That would most certainly prevent you from being able to update it.

2. You need to check that the application (web site or virtual directory) has write permissions. You can find this setting in the "Home Directory" tab of the website or application properties in IIS manager. Underneath the Local Path text box there is a checkbox for "Write" among other things. Make sure that is checked.

3. Another problem exists when the user account that IIS is running under (usually "IUSR_<computername>") doesn't have write access to the directory the application is running at. This you'll have to check in the securities properties for the web application folder.

Aren't permissions fun! ;)

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old November 10th, 2003, 12:09 PM
Registered User
 
Join Date: Nov 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the comments.

Here's the error I get:

There is a problem with the page you are trying to reach and it cannot be displayed.

--------------------------------------------------------------------------------

Please try the following:

Open the 204.2.108.185 home page, and then look for links to the information you want.
Click the Refresh button, or try again later.

Click Search to look for information on the Internet.
You can also see a list of related sites.

HTTP 500 - Internal server error
Internet Explorer


Does this mean my assumption about not being able to write is correct?

THanks.

HL


 
Old November 10th, 2003, 01:27 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Error 500 could mean anything! ;)

Can you browse that page locally to see what the error details are?
 
Old November 10th, 2003, 01:37 PM
Registered User
 
Join Date: Nov 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Couldn't see any other errors no matter how I looked.

It is failing on the following lines:

If I try to "execute" it

cn.Execute ("UPDATE Schedule SET Score_V = 1, Score_H = 2 WHERE ID = 180")

If I try to do it via a recordset:

objRS.Open sql, cn, 1, 3, 2

If I comment the line out, no problem (of course no data changed but no problems :D


 
Old November 11th, 2003, 06:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yes, but did you check the things mentioned in peter's post and the link I gave you? Just changing from a recodset to a connection.execute won't help if you have permissions problems.

Check the things shown in this article which was referneced in the original link which I gave you:
http://support.microsoft.com/?kbid=175168

BTW you need to change the "friendly" error messages in order to get any detailed error messages (in IE choose Tools > Internet Options > Advanced, and toggle the "Show friendly HTTP error messages").

rgds
Phil
 
Old November 11th, 2003, 08:32 AM
Registered User
 
Join Date: Nov 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

Execute now works. Appreciate the effort. Thanks for the help.

Howard







Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Access Database with datagrid jeff_M General .NET 2 May 19th, 2007 10:44 AM
!!HELP! trying to update an Access database brawny4 VB Databases Basics 4 April 24th, 2007 02:07 PM
Access database won't update? jula Access ASP 8 September 26th, 2004 05:46 PM
Database update access inershado Access ASP 1 September 23rd, 2003 11:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.