Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > JScript Erroneously Says (oRecordSet (oItem).Attributes & 0x4) = 0

Reply
Thread Tools

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

 
 
Dan Sikorsky
Guest
Posts: n/a
 
      07-01-2003
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 ( );


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      07-02-2003
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 ( );





 
Reply With Quote
 
 
 
 
Dan Sikorsky
Guest
Posts: n/a
 
      07-02-2003
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


"Bob Barrows" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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 ( );

>
>
>
>



 
Reply With Quote
 
Dan Sikorsky
Guest
Posts: n/a
 
      07-03-2003
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



"Bob Barrows" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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/te...kbench_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
>
> Dan Sikorsky wrote:
> > 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
> >
> >
> > "Bob Barrows" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> >> 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 ( );

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
What is Server-Side Jscript (not Jscript.NET)? Maxwell2006 ASP .Net 5 03-07-2006 05:28 AM
Re: ICS erroneously indicated Steve Winograd [MVP] Wireless Networking 0 04-16-2005 06:10 PM
Add hex number strings given by variables in XSLT (was erroneously reported to comp.text group before) Rolf Kemper XML 1 10-15-2004 08:44 AM
To style or not to style ? (was erroneously reported to comp.text before, no answer yet) Rolf Kemper XML 0 10-15-2004 04:53 AM
Porting from JScript to JScript.Net - compiler error Jon Maz ASP .Net 4 09-09-2004 10:24 AM



Advertisments