Lots of problems ASP – Access Insert Record code

Topic

New Reply

Been working on methods for building a SQL string and then send it to a dataconnection set up for an Access db. Lots of little problems have started to develop and now the road is become very murky…hope the Lounge can spread a little light!

1) Standard connection Code can’t find the db where it’s supposed to be. Here’s the code:

‘– Declare your variables
Dim DBFileName

‘ Change the db1.mdb to .mdb
DBFileName = “CRCGIntegratedStaffingForms.mdb”

‘– Create dataconnection and recordset object and open database

Set DataConnection = Server.CreateObject(“ADODB.Connection”)
Set myRecordSet = Server.CreateObject(“ADODB.Recordset”)
DataConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & Server.MapPath(“”) & “MDBCRCGIntegratedStaffingForms.mdb;”

I’ve used this sort of code for establishing a connect to an access db in my web server in a folder called ‘MDB’. I have other pages that open and read records and there’s no problem finding the db. However, now I get this strange message:

cannot find the file ‘C:WINNTsystem32CRCGIntegratedStaffingForms.mdb’.

I decided to move it there just to satisfy whatever demon is not looking in the right spot and get access denied errors such as

The Microsoft Jet database engine cannot open the file ‘C:WINNTsystem32CRCGIntegratedStaffingForms.mdb’. It is already opened exclusively by another user, or you need permission to view its data.

I also have a strange problem with some Select Case statements in asp that work perfectly well in one page but not on another, but I’d like to know first of all why asp can’t seem to look in the right folder. I *did* setup the folder in IIS to enable scripting and have the following in the header for the asp page:

Also, I don’t know if it is best to use INSERT or UPDATE for adding info to a file, as I’ve never done this before.

Any ideas?

TIA, as ever.

Reply | Quote

Replies

I haven’t used Server.MapPath much, but what if you use / instead of in there as in:

Server.MapPath(“/MDB/CRCGIntegratedStaffingForms.mdb”) & “;”

Reply | Quote

Hi Steve,

Sorry for the delayed response. Jefferson’s response is spot on. You’ll want to use forward slashes (/) in Server.MapPath rather than back slashes ().

You’ll want to include the entire virtual path inside the Server.MapPath function:

“…Data Source=” & Server.MapPath(“/MDB/CRCGIntegratedStaffingForms.mdb”) & “;”

Be sure that this path is relative to the ASP File calling this open statement.

Regarding the problem with the same code not working on two different pages, that could be caused by a number of issues. Double check to make sure all necessary variables are available on the problematic page. If not, you may need to pass one or more variables to the page with a form or in Session. It’s difficult to say more without seeing the code…

Finally, INSERT and UPDATE are two different processes. INSERT is used to append new records and UPDATE is used to update existing records. Note that you can also use ADO methods to accomplish the same thing (ADO.Recordset.AddNew and ADO.Recordset.Update). It depends on the situation as to whether I’ll use one or the other – mainly based on performance. When working with SQL Server, I always prefer to pass variables to a stored procedure with the Command and Parameter objects (which ultimately runs an INSERT or UPDATE behind the scenes). However, with Access I think either way would give about the same performance.

Hope this helps

Reply | Quote

The suggested change on the DataConnection string makes no difference. There seems to be something wrong with the .asp page itself. I have other pages that use the DataConnection string with the ‘bad’ slash usage and I am getting smooth connections and reads from .mdb files in the same folder. Stranger still, if i copy the code connection block from a working .asp page into the one I am trying to cure, I get the same error message looking for the .mdb file in the path C:WINNTsystem32 — so .asp connect works perfectly in one folder on the web server and gives me errors in another folder. both folders are set in IIS to allow script address and are assigned as application nodes.

What in the world is going on?? I checked page options and both Good .asp page and Bad .asp pages are exactly the same. .asp almost seems like it is trying to find a System DSN. Strange. I am stuck at this point and can’t test for successful INSERTS to a db that refuses to be found or, if you go ahead and put it where the system insists on looking for it, it can’t be opened.

sigh…

as to the other problem with some code working on one page but not on another: my bad! I forgot to embed the code in a loop so it wasn’t exactly the same and was working fine, in the sense of not doing anything at all. programming in the evening can be dangerous.

Reply | Quote

Steve,

A couple of thoughts. Are the two ASP pages in question located in the same folder? If so, we need to keep looking for the issue. If not, you’ll need to adjust your path to compensate for the different relative locations. Obviously, if the specified folder can’t be found IIS grabs C:WINNTSystem32 as a default path.

One thing I usually do is set a separate file with my connection string info. Something like you’ve posted:

I call my file DBCONN.ASP and use an Include statement in all pages that use that particular connection:
(Spaces added to prevent being hidden in the lounge)

The only catch is that I need a separate DBCONN.ASP file for each folder that I use. The Server.MapPath creates an ABSOLUTE reference to a specific path on your server’s hard drive. That is why you can not expect a virtual path to work from two different locations.

Hope this helps

Reply | Quote

I don’t use an include in this case as the db is specific to the task at hand, but I don’t think that’s much of an issue. I think the .asp page must be corrupted in some way. Here’s what I’ve done since last posting:

Created a new page with just the connection information to open the target db, read and print a single field. It works. The original, bad, page, bombs using good connection strings. both the new good .asp and the old bad .asp pages are in the same folder. perhaps something in the rest of the coding is causing a problem, but i don’t see how as the .asp should process in order of the file itself.

Well, now that I have a .asp page that actually finds, opens and reads from the db in the MDB folder, I will start porting in the rest of the code and keep testing.

new information

I found the offending code: DataConnection.execute(mySQL)

I can open, read and print data from the mdb but when .asp tries to run the above code it fails with the usual errors. Obviously this isn’t working but not sure what to use to run the INSERT SQL string. any suggestions?

PROGRESS

‘peers that I needed to set read/write permission to the Access file to get it to update. I am now inserting willy-nilly. On to fine-tuning an attempt to dynamically create valid SQL strings… The Dataconnection.execute is not offensive and, from another web page I got this:

DataConnection.execute mySQL, , &H00000080 which works — the bit of coding at the end is a way to specify a record insert without having to declare DataConnection.AddNew & is supposed to be faster than relying on Command coding or the ADOVBS.INC file.

Reply | Quote

Regarding this point: [indent]

---

DataConnection.execute mySQL, , &H00000080 which works — the bit of coding at the end is a way to specify a record insert without having to declare DataConnection.AddNew & is supposed to be faster than relying on Command coding or the ADOVBS.INC file.

---

[/indent]Call me clueless, but why would one need the ADOVBS.INC file? Isn’t everything I need in the .dll file? Maybe it’s just a difference in style for how you incorporate the constants into the page. I use this inside the and tags (comment tags broken for Lounge posting):

I looked in the ADOVBS.INC file and for everyone’s reference, here’s the meaning of the code:

‘—- ExecuteOptionEnum Values —-
Const adAsyncExecute = &H00000010
Const adAsyncFetch = &H00000020
Const adAsyncFetchNonBlocking = &H00000040
Const adExecuteNoRecords = &H00000080
Const adExecuteStream = &H00000400

I’ve used adExecuteNoRecords when executing Make Table queries in an MDB from an ASP page. It’s more efficient because only a null recordset object is returned. But I don’t think it specifies that you want to do an INSERT; that would need to be in your query, I’m pretty sure.

Also, I think it’s not such a big deal for the server to read the INC or DLL file, and it makes the code easier to maintain if you can use more readily understandable constants in the code. Just my .

Reply | Quote

Hi Jefferson,

Regarding using the ADOVBS.INC file, I ALWAYS use this with any ASP page that connects to a database. My reason is that most often the web hosts I’m working with are outside my control. Therefore the path of the msado15.dll file is not certain.

Honestly, processing a number of constant declarations is not very processor-intensive and doesn’t seem to have a noticable affect on performance. I think the convenience of using text constants far outweighs any extra overhead.

On the other hand, if you manage your own server and can easily reference the msado15.dll file then why would you need to bother with including the ADOVBS.INC file…

Just my

Reply | Quote

I got this hint about using the code &H00000080 instead of a ADOVBS.INC derived named constant from this excellent article from an excellent resource:

http://www.aspfaq.com/show.asp?id=2191%5B/url%5D

Using the constant rather than rhe named argument adExecuteNoRecords means you don’t need to append the include file and that reduces overhead by some small amount. In my case, I am implementing a series of web-based forms and may have to do several reads/writes to a (yecch) ACCESS database, so I am concerned with making the code as lean as possible and keep overhead in page processing low. I don’t know if there’s much of a problem with the include file but, all things considered, if you don’t need a lot of named constants to maintain a page, I don’t see why you shouldn’t use the direct constant. You can always put in a comment line defining what the constant does. It’s a programming style issue, ultimately.

I tend to agree with the preference to use the ADOVBS file rather than msoado15.dll in terms of how much control you have over the public webserver. in my case, I have no control over it and the people that do took 3 weeks to reinstall and configure CDONTS on the box after a serious crash. don’t want to rely on them to set up anything customizeable for my needs. So, DNS-less connections certainly, and ADOVBS.INC if necessary.

Oh, and also: I was very happy to fun into the http://www.aspfaq.com[/url%5D site as the person running it does a great job in discussing ASP problems and solutions and, better still, hasn’t cluttered things up with buzzwordy ASP.NET articles that are useless for my needs. check it out!

Reply | Quote

Hi Steve,

Great site!

Regarding whether or not to use ADOVBS.INC, I must respectfully disagree.

I try to write code that is very readable for myself and others. I agree that you could certianly include a comment line listing the value of the arguments but that can be tedious and inconsistent. The server time to process the ADOVBS constants is very minimal – especially with today’s hardware capabilities. At one point I would have agreed with your thoughts. But after writing several hundred-thousand lines of ASP code, I have come to appreciate the benefits of the ADOVBS file. My code is much more readable and much easier to troubleshoot or recycle.

Hope this helps

Reply | Quote