I am trying to use the Scripting.Dictionary object to loop through form 

fields and then add the values to the Access database.  I'm having some 

problems and do not know if these can be solved or this is just a bad idea.



For example,

The following code will change the values that I change on the form, but 

if I delete the name (which the database permits) the old name will still 

register.  The form is a list of six names and only one is required.  The 

table consists of the six names plus the key field (a number) which is 

required.  The key field in the table is not one of the form fields.  The 

snippet is below.



'Capture the form values

For Each fld in Request.Form

   	fldNew(fld) = Request.Form(fld)

   	Next

   	

   'Then after I open the table I insert the updated values

   

    For each fld in objRS.Fields

   	If (fldNew(fld.Name) <> "") Then

   		fld.Value = fldNew(fld.Name)

   	End If

   	Next

   	

   	As I mentioned, if I delete a value in the form (so that there is 

no input/value in the form), it doesn't record.  The previous value in the 

database is retained.  (However, the changed values are properly 

recorded.)  The "If" statement checking for values may be the problem.  

However, one of the fields in the table is a key field which cannot be 

changed.  If I take out the "If" statement, I get the following 

error:  "The record cannot be deleted or changed because table 'Officers' 

includes related records. "  (Officers is another table using the same key 

field)  Therefore, if I delete the "If" statement, the database apparently 

reads that I want to change the key field and won't let me.

   	

   	I've tried adding a form field with the key field, but I get the 

same error message. 

   	

   	Sorry for the long message, but does anyone have ideas?

   	

   	Thanks.

   	

   	Steve

This really isn't the way I do it, but if you want to update a record this

way...



a) Use an SQL statement to open the recordset. Select only the 6 name fields

WHERE key_field = 1 (or whatever record you are editing):



<%

strSQL = _

    "SELECT field1, field2, field3, field4, field5, field6 " & _

    "FROM table1 " & _

    "WHERE keyfield = 1"

objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdText

%>



b) Amend you update code to read:



<%

For each fld in objRS.Fields



    If (fldNew(fld.Name) <> "") Then

        fld.Value = fldNew(fld.Name)

    Else

        ' This could be NULL

        '  I don't know whether you want ZLS or NULL

        fld.Value = ""

    End If



Next

%>



Since the key field is no longer in the recordset, you don't have to worry

about it being changed.



Cheers

Ken



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

From: "Stephen Proctor" <steveproctor@c...>

Subject: [access_asp] Scripting.Dictionary Object - Get form values to

update database





: I am trying to use the Scripting.Dictionary object to loop through form

: fields and then add the values to the Access database.  I'm having some

: problems and do not know if these can be solved or this is just a bad

idea.

:

: For example,

: The following code will change the values that I change on the form, but

: if I delete the name (which the database permits) the old name will still

: register.  The form is a list of six names and only one is required.  The

: table consists of the six names plus the key field (a number) which is

: required.  The key field in the table is not one of the form fields.  The

: snippet is below.

:

: 'Capture the form values

: For Each fld in Request.Form

:    fldNew(fld) = Request.Form(fld)

:    Next

:

:    'Then after I open the table I insert the updated values

:

:     For each fld in objRS.Fields

:    If (fldNew(fld.Name) <> "") Then

:    fld.Value = fldNew(fld.Name)

:    End If

:    Next

:

:    As I mentioned, if I delete a value in the form (so that there is

: no input/value in the form), it doesn't record.  The previous value in the

: database is retained.  (However, the changed values are properly

: recorded.)  The "If" statement checking for values may be the problem.

: However, one of the fields in the table is a key field which cannot be

: changed.  If I take out the "If" statement, I get the following

: error:  "The record cannot be deleted or changed because table 'Officers'

: includes related records. "  (Officers is another table using the same key

: field)  Therefore, if I delete the "If" statement, the database apparently

: reads that I want to change the key field and won't let me.

:

:    I've tried adding a form field with the key field, but I get the

: same error message.

:

:    Sorry for the long message, but does anyone have ideas?



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

Thanks Ken.



I appreciate your answer.  I've noticed and admired your helpful and to 

the point answers to my posts as well as those of others.  You said that 

you would not do it this way (i.e., using Scripting.Dictionary object).  

I'm curious how you would do it.



Until now, I've updated databases field by field.  This has worked.  In 

other words, take each form value and insert in the proper database field 

in the record.  



The method using Scripting.Dictionary came from MSPress Web Database 

Development Step by Step by Jim Buyens.  I was really impressed with it 

because it avoided the step of coding each form value in ASP and then 

tying it into the database.  It seemed like a real timesaver and also 

seemed to be a way to reuse code.



Without asking for all your methods, can you  briefly describe how you 

recommend updating a database using an HTML form - either field by field 

or using a looping method (like Scripting.Dictionary).  I know this is a 

very general question and a general answer is sufficient.



Thanks again for your help.



Steve





> This really isn't the way I do it, but if you want to update a record 

this

> way...

> 

> a) Use an SQL statement to open the recordset. Select only the 6 name 

fields

> WHERE key_field = 1 (or whatever record you are editing):

> 

> <%

> strSQL = _

>     "SELECT field1, field2, field3, field4, field5, field6 " & _

>     "FROM table1 " & _

>     "WHERE keyfield = 1"

> objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdText

> %>

> 

> b) Amend you update code to read:

> 

> <%

> For each fld in objRS.Fields

> 

>     If (fldNew(fld.Name) <> "") Then

>         fld.Value = fldNew(fld.Name)

>     Else

>         ' This could be NULL

>         '  I don't know whether you want ZLS or NULL

>         fld.Value = ""

>     End If

> 

> Next

> %>

> 

> Since the key field is no longer in the recordset, you don't have to 

worry

> about it being changed.

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Stephen Proctor" <steveproctor@c...>

> Subject: [access_asp] Scripting.Dictionary Object - Get form values to

> update database

> 

> 

> : I am trying to use the Scripting.Dictionary object to loop through form

> : fields and then add the values to the Access database.  I'm having some

> : problems and do not know if these can be solved or this is just a bad

> idea.

> :

> : For example,

> : The following code will change the values that I change on the form, 

but

> : if I delete the name (which the database permits) the old name will 

still

> : register.  The form is a list of six names and only one is required.  

The

> : table consists of the six names plus the key field (a number) which is

> : required.  The key field in the table is not one of the form fields.  

The

> : snippet is below.

> :

> : 'Capture the form values

> : For Each fld in Request.Form

> :    fldNew(fld) = Request.Form(fld)

> :    Next

> :

> :    'Then after I open the table I insert the updated values

> :

> :     For each fld in objRS.Fields

> :    If (fldNew(fld.Name) <> "") Then

> :    fld.Value = fldNew(fld.Name)

> :    End If

> :    Next

> :

> :    As I mentioned, if I delete a value in the form (so that there is

> : no input/value in the form), it doesn't record.  The previous value in 

the

> : database is retained.  (However, the changed values are properly

> : recorded.)  The "If" statement checking for values may be the problem.

> : However, one of the fields in the table is a key field which cannot be

> : changed.  If I take out the "If" statement, I get the following

> : error:  "The record cannot be deleted or changed because 

table 'Officers'

> : includes related records. "  (Officers is another table using the same 

key

> : field)  Therefore, if I delete the "If" statement, the database 

apparently

> : reads that I want to change the key field and won't let me.

> :

> :    I've tried adding a form field with the key field, but I get the

> : same error message.

> :

> :    Sorry for the long message, but does anyone have ideas?

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> 

Hi Stephen,



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

From: "Stephen Proctor" <steveproctor@c...>

Subject: [access_asp] Re: Scripting.Dictionary Object - Get form values to

update database





: You said that you would not do it this way

: (i.e., using Scripting.Dictionary object).

: I'm curious how you would do it.

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



Usually my code would look like this:



<%

' 1) Dim local variables



' 1) Allocate Request collection values to local variables

' I use routines here that can substitute a "default" value

' if the value passed is missing/invalid/not-acceptable



' 3) Validate all user input

' www.adopenstatic.com/resources/code/UIValidation.asp

' provides a intro to how you can do this



' 4) Normally I'd use SQL Server, so I used a stored procedure

' and Command/Parameter objects: this provides an additional

' level of protection, because Parameter objects are strongly typed

' But since we are talking about Access, we need to

' use an SQL statement. So, build an SQL statement

' and execute it.

' An SQL statement doesn't require a recordset object to be

' created. Recordset objects can be expensive, especially

' if you're using locking:



objConn.Execute strSQL,,adCmdText+adExecuteNoRecords



HTH



Cheers

Ken