I have two questions.

1) I set up an Access 2000 database and provided a number as a key in each 
table.  I then established a 1:1 relationship with the key field and asked 
to enforce referential integrity.  However, when I add data to the main 
table, I get an error message that I needed a comparable entry in another 
table.  When I deleted the relationship the program worked OK.  (The 
program adds values to the main table, then opens all the other tables one 
at a time to add only the key value to each other table.)  Is it possible 
to create the 1:1 relationship among the key values and add key data to 
the main table only?  Is there a way to add the key to all tables 
simultaneously?

2)  I am trying to use subroutines to add data to a table.  I am calling 
the subroutine as follows:

 objRS.Open "MainTable", strMFEMCorpString, adOpenStatic, 
adLockOptimistic, adCmdTable		
 
 objRS.AddNew
Call AddData
 
 
 objRS.Update

The subroutine is as follows:

Sub AddData
'f represents the field name, which is identical to the textbox name on 
the form
		Dim f
		For Each f in Request.Form
			objRS(f) = Trim(Request.Form(f))
		Next
End Sub
I am getting the following error:

ADODB.Recordset error '800a0cc1' 

Item cannot be found in the collection corresponding to the requested name 
or ordinal. 

Somehow the variable f is not correctly picking up the field name in objRS.

Much thanks for all your help.

Steve

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Stephen Proctor" <steveproctor@c...>
Subject: [access_asp] Two Questions - Update Subroutine and Adding Keys


: 1) I set up an Access 2000 database and provided a number as a key in each
: table.  I then established a 1:1 relationship with the key field and asked
: to enforce referential integrity.  However, when I add data to the main
: table,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Check which table you designated as the Parent table, and which table you
designated as the Child table.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: 2)  I am trying to use subroutines to add data to a table.  I am calling
: the subroutine as follows:
:
:  objRS.Open "MainTable", strMFEMCorpString, adOpenStatic,
: adLockOptimistic, adCmdTable
:
:  objRS.AddNew
: Call AddData
:  objRS.Update
:
: The subroutine is as follows:
:
: Sub AddData
: 'f represents the field name, which is identical to the textbox name on
: the form
: Dim f
: For Each f in Request.Form
: objRS(f) = Trim(Request.Form(f))
: Next
: End Sub
: I am getting the following error:
:
: ADODB.Recordset error '800a0cc1'
:
: Item cannot be found in the collection corresponding to the requested name
: or ordinal.
:
: Somehow the variable f is not correctly picking up the field name in
objRS.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Did you try doing something like:

For Each f in Request.Form
    Response.Write(f & " = " & Request.Form(f) & "<br>" & vbCrLf)
Next

Just to make sure that everything is as you expect it to be?

Secondly, I'd really counsel against using something like this - you make
one little change to your form (eg add a hidden field), and your routine
goes cactus. I'm not sure where you input validation code is, but the basic
principle is that you should hard code everything you can to lock down the
app.

Thirdly, NEVER pass a connection string to the Recordset's .Open method.
I've posted this advice MANY times on this list. When you do what you're
doing, ADO implicitly creates a connection object behind the scenes. However
because you have no reference to the object, you can't dispose of it (or
return it to the ODBC/OLEDB Connection Pool), leading to scalability
problems in your application.

Please read:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191572
INFO: Connection Pool Management By ADO Objects Called From ASP
(I urge all list members to read this KB article)

For more background on connection/resource pooling, see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
/pooling2.asp

Cheers
Ken

Ken,

Thank you very much for your response.  I will study it and the Knowledge 
Base article you recommended.  But first, an overall question.

First, you said: "the basic principle is that you should hard code 
everything you can to lock down the app."  

But so much of what I've read has recommended subroutines and reusable 
code.  (I made an earlier post involving a subroutine that you kindly 
answered.  But you were also critical of that subroutine.)  For inputting 
data in forms, I've been using a field.value = form.element method, 
requiring hard coding of each asp program.  This is doable for me, but 
also very time consuming.  Should I continue to do this, rather than look 
for a subroutine that will do that for multiple database tables?

Second, the connection string method is directly from "Beginning Active 
Server Pages 3.0" by Wrox.  So your criticism of this method surprised me.

Thanks again for your fast and helpful responses.

Steve

> I have two questions.

> 1) I set up an Access 2000 database and provided a number as a key in 
each 
t> able.  I then established a 1:1 relationship with the key field and 
asked 
t> o enforce referential integrity.  However, when I add data to the main 
t> able, I get an error message that I needed a comparable entry in 
another 
t> able.  When I deleted the relationship the program worked OK.  (The 
p> rogram adds values to the main table, then opens all the other tables 
one 
a> t a time to add only the key value to each other table.)  Is it 
possible 
t> o create the 1:1 relationship among the key values and add key data to 
t> he main table only?  Is there a way to add the key to all tables 
s> imultaneously?

> 2)  I am trying to use subroutines to add data to a table.  I am calling 
t> he subroutine as follows:

>  objRS.Open "MainTable", strMFEMCorpString, adOpenStatic, 
a> dLockOptimistic, adCmdTable		
 > 
 > objRS.AddNew
C> all AddData
 > 
 > 
 > objRS.Update

> The subroutine is as follows:

> Sub AddData
'> f represents the field name, which is identical to the textbox name on 
t> he form
	> 	Dim f
	> 	For Each f in Request.Form
	> 		objRS(f) = Trim(Request.Form(f))
	> 	Next
E> nd Sub
I>  am getting the following error:

> ADODB.Recordset error '800a0cc1' 

> Item cannot be found in the collection corresponding to the requested 
name 
o> r ordinal. 

> Somehow the variable f is not correctly picking up the field name in 
objRS.

> Much thanks for all your help.

> Steve

When Ken referred to hardcoding I think he meant in relation to which 
fields in the database you are updating.

Your code:

		Dim f
		For Each f in Request.Form
			objRS(f) = Trim(Request.Form(f))
		Next

assumes every field on the ASP form is a database field, which is probably 
very rare and also assumes the form field name is the same as the database 
field name, which may not be the case.

The code should be something like:

            objRS("DBfieldname1") = Trim(Request.Form("formfieldname1")
            objRS("DBfieldname2") = Trim(Request.Form("formfieldname2")
                       ....  etc....
where the DBFieldname(i) are column names in database table
and   the formfieldname(i) are the field names in ASP form.

Regards

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Stephen Proctor" <steveproctor@c...>
Subject: [access_asp] Re: Two Questions - Update Subroutine and Adding Keys


: Thank you very much for your response.  I will study it and the Knowledge
: Base article you recommended.  But first, an overall question.
:
: First, you said: "the basic principle is that you should hard code
: everything you can to lock down the app."
:
: But so much of what I've read has recommended subroutines and reusable
: code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi Stephen,

I'm sorry to have confused you. Rereading my post makes it clear that my
choice of words wasn't optimal, and leads to confusion when trying to
intepret them.

When writing my reply I started from the position that when you develop a
web-based client-server system you essentially have two security "domains".
There is the stuff that you do on the server that you can trust, and then
there is the input coming from the user, which you can't trust, because you
have no fail-safe way of controlling what they send to your server.

http://www.adopenstatic.com/resources/code/UIValidation.asp explains this in
a little more detail, shows some of the possible ways that someone can break
your code, and also has some links to more in depth treatment of the issues.

Thus, everything that comes from the user must be validated. You take what
they give you, you move it to some local, "private" variables and then you
validate them. Only if they are all OK (or you substitute acceptable default
values) do you do some work with them.

So, the thrust of your routine is valid. If you really want you could create
an array of database fields and values and insert the values into the
database. However you need to first validate that all the field names are
acceptable! So, at some point in time you have to hard code the acceptable
field values into your code. This can be either in your SQL statement, in
your stored procedure, or if you use the type of looping construct you have
at the moment, before you start the loop.

Think about developing a component - you don't expose a whole lot of public
variables and just use them willy-nilly. You create private variables and
expose public accessor methods. External, untrusted, users can only use the
public accessor methods. You can then validate the input in the accessor
method, and if valid move the value verbatim (or with some adjustment) to
your private variable.

The archtypical example in the textbooks is the "gear" property in a car
object. The gear property indicates what gear the car is in. If you just
blindly accept what the user inputs, then you could have problems if they
enter "ABC".

Instead you take what they input, and you validate it:
If input is not numeric, then discard it
If input is >= 1 then increase gear by 1, but only if gear is < 5
If input <= 1 then decrease gear by 1, but only if gear is > -1 (-1 is
reverse)

Then you store the final value in the private variable _gear

HTH

Cheers
Ken