Adding record errors

I am having difficulty with some asp where I am trying to add a record to a database.

My errors come on two levels:

(1) If I use OPTION EXPLICIT I get "Variable is undefined: 'adModeReadWrite' ",
and
(2) If I don't use OPTION EXPLICIT I get "ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." from line ** instead.

Here's my problem code:

      Dim Conn, RS, DSNName, sql
      Set Conn = Server.CreateObject ("ADODB.Connection")
      Conn.Mode = adModeReadWrite
      DSNName = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
      DSNName = DSNName & Server.MapPath("/Dbimages.mdb")
      Conn.Open DSNName
      Set RS = Server.CreateObject("ADODB.Recordset")
      sql = "SELECT * from Images"
**      RS.Open sql, Conn, adOpenStatic, adLockPessimistic

      RS.AddNew
      RS.Fields ("Filename") = Request.Form("UpFile")
      RS.Fields ("Category") = Request.Form("UpCat") etc. adding more fields to a database from a form...
JasLaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

c_swankyCommented:
Leave <%OPTION EXPLICIT%> at the top of your page (good practice)

(1) adModeReadWrite is a constant used by .asp you can do 3 things to get around it
1) Put "3" where ever you have adModeReadWrite     (e.g.      Conn.Mode = 3)
2) Put this after you line with your Dims      Const adModeReadWrite = 3
3) Put the <!--#include file="adovbs.inc"-->  at the top you page (but not before OPTION EXPLICIT)

(2) Can you list all your fields?  
0
c_swankyCommented:
For Item (1) You can do ONE of the three things I have listed there.....
0
rhoggrenCommented:
Alternatively to including the adovbs file in every ASP page, you can edit your global.asa file and place the following:

<!--METADATA TYPE="typelib" FILE="C:\program files\common files\system\ado\msado15.dll" -->

This will automatically load all of the VBS constants for you when your application is loaded.

Hope this helps!
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Sairam_SCommented:
Have you included adovbs.inc in your code..
0
JasLaneAuthor Commented:
Sorry no reply for a while - modem probs!

To answer your Q Sairam, no I haven't included adovbs.inc yet because I'm a bit of a newbie and don't know what it is?? what is "adovbs.inc" and where can I find it to include it?

I'm in the process of trying Swanky's ideas so I'll get back to you all soon..
0
rhoggrenCommented:
Jas,

The reason you are receving this error message is because with OPTION EXPLICIT enabled, you need to declare each and every instance of every variable you use in your code.

You are receiving this error message :Variable is undefined: 'adModeReadWrite'  because the VB interpreter cannot find the declared variable for "adModeReadWrite".

That variable is one of the many "VBScript Contstants" which are held in a file called "adovbs.inc" which by default is stored on your Windows NT/2000 server in the following location:

c:\program files\common files\system\ado\

Within that file you will see a ton of lines of code that will look something like this:

'--------------------------------------------------------------------
' Microsoft ADO
'
' (c) 1996-1998 Microsoft Corporation.  All Rights Reserved.
'
'
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000
Const adFind = &H00080000
Const adSeek = &H00400000
Const adIndex = &H00800000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4


To use this file, either use a server side include in _EACH_ page you intend to use the constants, or you can declare ALL of the constants at the same time using the global.asa file stored at the root of your site/application.

If you open the global.asa file, you can paste this line in:

<!--METADATA TYPE="typelib" FILE="C:\program files\common files\system\ado\msado15.dll" -->

Pasting this line into your global.asa will alleviate the need to include the file in each of your ASP pages. Otherwise you will need to set an include file in every page.

Conversely, to get around using the adovbs.inc in all ways, simply paste the following into your code:

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

Those two chunks of code *should* get your database connectivity functioning properly.

R-
0
JasLaneAuthor Commented:
R,

thanks for the help. I'm past the earlier problems with your code snippet above but now I'm getting another error. "Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only." (The error is coming on the RS.Update line).

I'm using MS Access 2002. How do I change my db properties or is there another VB problem with this line in my asp page?

      Conn.Mode = adModeReadWrite

Appreciate your help and you were on the right track Swanky (with the const dim)!!

I haven't included the "adovbs.inc" in the code yet as I'd need to change the path for the hosting server (eg. server.MapPath & ..) and am trying to keep it simple as I'm still learning my way..
0
rhoggrenCommented:
Jas,

When I open my recordsets for writing, I generally do so as follows:

DIM RS
SET RS = server.createobject("ADODB.Recordset")

RS.open SQLStatement, ObjConnection, adOpenKeyset, adLockOptimistic

I use the Keyset option because I have had consistent success with adding a record, calling the update method, and then being able to reference the ID of the new record (Great for multi-tabled apps)

If this does not work, check the permissions on the database file itself. Remember that the IUSR Win2K/NT account needs to have modify access to the database for it to function properly.

Let me know if this helps or if you run into any other problems!

R-
0
JasLaneAuthor Commented:
R-,

I've changed my RS.Open line to read like yours but I'm getting the same error as before (Object or database is read only). I went in to the ODBC sources and checked that my dbImages database (listed as a System DSN) did not have "Read Only" ticked - all OK there.  

I'll check Windows help to see if I've missed something unless you know what I've done wrong here?

Maybe I've got a conflict between DSN-less connection and having it listed in my data sources on my local computer?

Jas
0
rhoggrenCommented:
Jas,

Ok, if you have all of your ADO options set correctly, then the next step is to check Windows Permissions on the database file itself.

On your webserver, find where the database file is located. Follow these steps:

1. Right click on the file
2. Click Properties
3. The properties tab should load, you'll want to look in the "Security" tab
4. Make sure that the IUSR_MACHINENAME (MACHINENAME being the actual name of your server) has Modify access to the database file.

If the IUSR_ account does not have modify permissions, set those permissions and click apply until you are back out to the Windows Explorer.

Then try your script again. If everything is working correctly you should see a record in your database!

Let me know if you run into any problems!

R-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JasLaneAuthor Commented:
R-,

You are a legend! The IUSR_MACHINENAME was added and now it all works fine. Thanks for the permission and adovbs tips along the way.

Only other problem is now I'm getting blank records in the database. It would appear the form "post" is not working. I have all text fields and one file field so I'm using "multipart/form-data" in the form tag. I'm sending from add_record.asp to add_record_update.asp but on the new page the Response.Form("NFile, etc") are all blank.

My code in "add_record.asp" page:

<Form action="Add_record_update.asp" method="post" enctype="multipart/form-data">
<div align="center"><br>Add record to Images database</div>
<br>
<p>File:<br><input name="NFile" type="file"></p>
<p>Volume:<br><input name="NCat" type="text"></p>
<p>Film:<br><input name="NSub" type="text"></p> .. etc ..
</Form>

Then in "add_record_upload.asp" we have:

      Set RS = Server.CreateObject("ADODB.Recordset")
      sql = "SELECT * from Images"
      RS.Open sql, Conn, adOpenKeyset, adLockOptimistic

      RS.AddNew
      RS.Fields ("Filename") = Request.Form("NFile")
      RS.Fields ("Category") = Request.Form("NCat")
      RS.Fields ("Subcat") = Request.Form("NSub")
      RS.Fields ("Title") = Request.Form("NTit")
      RS.Fields ("Caption") = Request.Form("NCap")
      RS.Update
      RS.Close

Any advice you can give on this?
0
rhoggrenCommented:
Yes, but before I can answer I need to ask you a question. Are you intending to save the file to disk or are you just looking to save the information stored in the NFile field?

From your statement about using multipart "enctype="multipart/form-data"" it sounds like that is your intention.

How are you accomplishing your upload and saving the file to disk? I personally use an upload class that I found (I can't even remember where anymore and it wasn't commented at all) so for me, using your code, I would call my upload class as follows:

SET objFile = NEW Uploader
'some additional code here for the uploader functions

 Set RS = Server.CreateObject("ADODB.Recordset")
     sql = "SELECT * from Images"
     RS.Open sql, Conn, adOpenKeyset, adLockOptimistic

     RS.AddNew
     RS.Fields ("Filename") = objFile .Form("NFile")
     RS.Fields ("Category") = objFile .Form("NCat")
     RS.Fields ("Subcat") = objFile .Form("NSub")
     RS.Fields ("Title") = objFile .Form("NTit")
     RS.Fields ("Caption") = objFile .Form("NCap")
     RS.Update
     RS.Close

Does this help at all? If not give me some more info about the intentions of the form and whether or not you are saving the file to disk, and if so, how you are accomplishing that. From there I can get a better idea of where to steer you :-)

R-
0
rhoggrenCommented:
Another thing I wanted to mention about that adovbs.inc file. If you do choose to use it, copy it from it's default location and place it in a directory somewhere on your site.

Also, rename the extension .asp so the user will not get a download dialog should someone happen upon your file.

R-
0
JasLaneAuthor Commented:
Yes R-,

thanks for the tips on the upload class (and changing the .ext on adovbs).

With your help, things are set up correctly now (pretty much as you've posted above). The images are being uploaded and the records updated!

Jas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

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.