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

Discussion in 'ASP General' started by Dan Sikorsky, Jul 2, 2003.

  1. Dan Sikorsky

    Dan Sikorsky Guest

    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, Jul 2, 2003
    #1
    1. Advertising

  2. Dan Sikorsky

    Bob Barrows Guest

    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 ( );
     
    Bob Barrows, Jul 2, 2003
    #2
    1. Advertising

  3. Dan Sikorsky

    Dan Sikorsky Guest

    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" <> wrote in message
    news:%...
    > 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 ( );

    >
    >
    >
    >
     
    Dan Sikorsky, Jul 2, 2003
    #3
  4. Dan Sikorsky

    Dan Sikorsky Guest

    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" <> wrote in message
    news:%...
    > 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
    >
    > 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" <> wrote in message
    > > news:%...
    > >> 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 ( );

    >
    >
     
    Dan Sikorsky, Jul 3, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jon Maz
    Replies:
    4
    Views:
    1,319
    Jon Maz
    Sep 9, 2004
  2. Maxwell2006
    Replies:
    5
    Views:
    2,438
    Steven Cheng[MSFT]
    Mar 7, 2006
  3. Rolf Kemper
    Replies:
    1
    Views:
    753
    Dimitre Novatchev
    Oct 15, 2004
  4. Rolf Kemper
    Replies:
    0
    Views:
    395
    Rolf Kemper
    Oct 15, 2004
  5. John Bentley
    Replies:
    14
    Views:
    387
    Jim Ley
    Feb 5, 2004
Loading...

Share This Page