JScript Erroneously Says (oRecordSet (oItem).Attributes & 0x4) = 0

[Dan Sikorsky]

How can I get the recordset attributes for a table field in SQL Server 2000
to report the field updatable attribute correctly ... mine keeps saying the
fields are not updatable? That is, ( oRecordSet ( oItem ).Attributes & 0x4)
is always 0 for every field on SQL Server 2000

Example code:

var sSQL = 'SELECT TOP 1 * FROM [' + sTableName + '] WHERE ' + sUniqueField
+ '=' + sUniqueValue + ';';

oRecordSet.Open ( sSQL, oConnection, adOpenStatic, adLockOptimistic );

// get the data from the form, updating the recordset

var e = new Enumerator ( Request.Form );

while ( !e.atEnd ( ) )

{

var oItem = e.item();

if ( ( oRecordSet ( oItem ).Attributes & 0x4) != 0 ) // 4 ==
adFldUpdatable, 8 == adFldUnknownUpdatable

{

oRecordSet ( oItem ).Value = ( FixNull( Request.Form( oItem ) ) == '' ?
null : Request.Form ( oItem ));

// if the unique field was updated use the new answer

if ( oItem == sUniqueField )

{

// keep it wrapped in "" if it used to be

if ( sUniqueValue.charAt ( 0 ) == "'" )

sUniqueValue = "'" + Request.Form ( oItem ) + "'";

else

sUniqueValue = "" + Request.Form ( oItem );

}

}

}

e.moveNext ( );

}

oRecordSet.Update ( );

 

[Bob Barrows]

A couple possibilities:

a) The Top statement is resulting in a non-updatable resultset (I'm not sure
about this one). Why do you need Top 1 if you are filtering on a unique
column? it makes me suspect that the column is not unique which may lead to
the situation in option c.
b) The Provider you are using is misreporting the attribute and the column
actually is updatable
c) Your table has no primary key or unique index to allow ADO to identify
the record being updated

Bottom line: you should not use recordsets for data modification. Use SQL
DML (Insert, Update, and Delete queries) instead. Cursors are evil.

Bob Barrows

 

[Dan Sikorsky]

Thanks for the insight Bob ... I'll look into your analysis.

The only reason to use the SELECT TOP 1 * FROM ... is to get back the
attributes of each field in the table ... whether or not there are any
records. Not only do I use this technique here for an UPDATE action, but I
heavily use it for creating ASP form fields using a template. By passing in
the table name to a generic form routine, I can automatically show only the
table's form fields that should be shown for the SQL INSERT/UPDATE that is
executed when the form is submitted.

Admittedly, this technique works well for the LAN web forms that are used
internally via an Access2000 database, and I recently have be moving
internal Access2000 databases to SQL Server 2000 and have run across this
problem.

The connection string includes 'Provider=SQLOLEDB.1; ...'

There is a primary key declared for the table; the usual long integer that
is incremented by one to get the next key.

Do you of a way, in jscript, to determine if a table's field is updatable
(without maintaining a list of field names in a string variable), directly
by querying the SQL Server table, so as to use knowledge that is already
specified somewhere in SQL Server?

Thank you kindly,
Dan Sikorsky

A couple possibilities:

a) The Top statement is resulting in a non-updatable resultset (I'm not sure
about this one). Why do you need Top 1 if you are filtering on a unique
column? it makes me suspect that the column is not unique which may lead to
the situation in option c.
b) The Provider you are using is misreporting the attribute and the column
actually is updatable
c) Your table has no primary key or unique index to allow ADO to identify
the record being updated

Bottom line: you should not use recordsets for data modification. Use SQL
DML (Insert, Update, and Delete queries) instead. Cursors are evil.

Bob Barrows

How can I get the recordset attributes for a table field in SQL
Server 2000 to report the field updatable attribute correctly ...
mine keeps saying the fields are not updatable? That is, ( oRecordSet
( oItem ).Attributes & 0x4) is always 0 for every field on SQL Server
2000

Example code:

var sSQL = 'SELECT TOP 1 * FROM [' + sTableName + '] WHERE ' +
sUniqueField + '=' + sUniqueValue + ';';

oRecordSet.Open ( sSQL, oConnection, adOpenStatic, adLockOptimistic );

// get the data from the form, updating the recordset

var e = new Enumerator ( Request.Form );

while ( !e.atEnd ( ) )

{

var oItem = e.item();

if ( ( oRecordSet ( oItem ).Attributes & 0x4) != 0 ) // 4 ==
adFldUpdatable, 8 == adFldUnknownUpdatable

{

oRecordSet ( oItem ).Value = ( FixNull( Request.Form( oItem ) )
== '' ? null : Request.Form ( oItem ));

// if the unique field was updated use the new answer

if ( oItem == sUniqueField )

{

// keep it wrapped in "" if it used to be

if ( sUniqueValue.charAt ( 0 ) == "'" )

sUniqueValue = "'" + Request.Form ( oItem ) + "'";

else

sUniqueValue = "" + Request.Form ( oItem );

}

}

}

e.moveNext ( );

}

oRecordSet.Update ( );

 

[Dan Sikorsky]

Bob,

I'll use the select * from tbl where 1=2 idea.

My primary usage for determining the 'updatableness' of a field is for a
generic asp page to prompt for only fields that are insertable or updatable,
stripping out such fields as the primary key, all foreign keys, and fields
declared in SQL Server to not have permission for specific users.

In my JScript, I do use a 'select max(id)+1 as ID from tbl ' to generate the
next primary key, followed by an insert statement. This is a carryover from
the Access2000 design, and I should use an IDENTITY field instead.

Dan

I still don't see why TOP 1 is necessary. If you just want a list of fields,
you can do:
select * from table where 1=2

As to whether or not the field is updatable, if the provider is not
supplying the necessary attribute information, you can open a recordset,
disconnect it by setting its activeconnection property to nothing, and
attempt to update the fields, catching the error if the field is not
updatable. That way, you won't effect the source data in the database.

In my experience, however, all columns except the IDENTITY column are
updatable. When you say " ... incremented by one to get the next key.", do
you mean that you are using the builtin IDENTITY column? Or have you created
your own process to get the next key. If the column is not an IDENTITY, it
should be updatable. You can determine whether a column is an IDENTITY
column by running the sp_columns procedure against the table and checking to
see if the TYPE_NAME column contains the word "identity". I would do this in
a stored procedure to avoid bringing all the column rows back to your asp
page. You could have the stored procedure return only the name of the
IDENTITY.

Another situation that may effect updatability is when the column is
involved in a foreign key relationship (or has some other constraint defined
that may prevent any updates). This is another case where you would want to
wrap the code that checks for this (you can use the INFORMATION_SCHEMA views
or the relevant system stored procedures - see BOL) in a stored procedure.

I never do generic stuff like this: it's much too detrimental to performance
and scalability. Using Select * and cursors for data modifications pretty
much guarantees that you are sending too much data across the wire and
maintaining table locks for too long. In addition, failure to utilize stored
procedures can have a severe impact on performance and scalability:

http://msdn.microsoft.com/library/techart/Docu2KBench.htm#docu2kbench_topic15
(see the Dynamic SQL vs. Stored Procedures test)

I'm going to have to stop here. I think my boss is looking for me ;-)

HTH,
Bob Barrows

Thanks for the insight Bob ... I'll look into your analysis.

The only reason to use the SELECT TOP 1 * FROM ... is to get back the
attributes of each field in the table ... whether or not there are any
records. Not only do I use this technique here for an UPDATE action,
but I heavily use it for creating ASP form fields using a template.
By passing in the table name to a generic form routine, I can
automatically show only the table's form fields that should be shown
for the SQL INSERT/UPDATE that is executed when the form is submitted.

Admittedly, this technique works well for the LAN web forms that are
used internally via an Access2000 database, and I recently have be
moving internal Access2000 databases to SQL Server 2000 and have run
across this problem.

The connection string includes 'Provider=SQLOLEDB.1; ...'

There is a primary key declared for the table; the usual long integer
that is incremented by one to get the next key.

Do you of a way, in jscript, to determine if a table's field is
updatable (without maintaining a list of field names in a string
variable), directly by querying the SQL Server table, so as to use
knowledge that is already specified somewhere in SQL Server?

Thank you kindly,
Dan Sikorsky

A couple possibilities:

a) The Top statement is resulting in a non-updatable resultset (I'm
not sure about this one). Why do you need Top 1 if you are filtering
on a unique column? it makes me suspect that the column is not
unique which may lead to the situation in option c.
b) The Provider you are using is misreporting the attribute and the
column actually is updatable
c) Your table has no primary key or unique index to allow ADO to
identify the record being updated

Bottom line: you should not use recordsets for data modification.
Use SQL DML (Insert, Update, and Delete queries) instead. Cursors
are evil.

Bob Barrows

Dan Sikorsky wrote:
How can I get the recordset attributes for a table field in SQL
Server 2000 to report the field updatable attribute correctly ...
mine keeps saying the fields are not updatable? That is, (
oRecordSet ( oItem ).Attributes & 0x4) is always 0 for every field
on SQL Server 2000

Example code:

var sSQL = 'SELECT TOP 1 * FROM [' + sTableName + '] WHERE ' +
sUniqueField + '=' + sUniqueValue + ';';

oRecordSet.Open ( sSQL, oConnection, adOpenStatic, adLockOptimistic
);

// get the data from the form, updating the recordset

var e = new Enumerator ( Request.Form );

while ( !e.atEnd ( ) )

{

var oItem = e.item();

if ( ( oRecordSet ( oItem ).Attributes & 0x4) != 0 ) // 4 ==
adFldUpdatable, 8 == adFldUnknownUpdatable

{

oRecordSet ( oItem ).Value = ( FixNull( Request.Form( oItem ) )
== '' ? null : Request.Form ( oItem ));

// if the unique field was updated use the new answer

if ( oItem == sUniqueField )

{

// keep it wrapped in "" if it used to be

if ( sUniqueValue.charAt ( 0 ) == "'" )

sUniqueValue = "'" + Request.Form ( oItem ) + "'";

else

sUniqueValue = "" + Request.Form ( oItem );

}

}

}

e.moveNext ( );

}

oRecordSet.Update ( );