Solved

Adding record errors

Posted on 2003-12-03
14
513 Views
Last Modified: 2010-05-18
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...
0
Comment
Question by:JasLane
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 4

Assisted Solution

by:c_swanky
c_swanky earned 30 total points
ID: 9872535
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
 
LVL 4

Expert Comment

by:c_swanky
ID: 9872538
For Item (1) You can do ONE of the three things I have listed there.....
0
 
LVL 3

Expert Comment

by:rhoggren
ID: 9872603
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
 
LVL 2

Expert Comment

by:Sairam_S
ID: 9872712
Have you included adovbs.inc in your code..
0
 

Author Comment

by:JasLane
ID: 9895337
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
 
LVL 3

Assisted Solution

by:rhoggren
rhoggren earned 150 total points
ID: 9897821
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
 

Author Comment

by:JasLane
ID: 9900682
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:rhoggren
ID: 9900852
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
 

Author Comment

by:JasLane
ID: 9908843
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
 
LVL 3

Accepted Solution

by:
rhoggren earned 150 total points
ID: 9908887
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
 

Author Comment

by:JasLane
ID: 9909309
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
 
LVL 3

Expert Comment

by:rhoggren
ID: 9909869
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
 
LVL 3

Expert Comment

by:rhoggren
ID: 9909876
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
 

Author Comment

by:JasLane
ID: 9917595
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now