Alter column conversion problem

Discussion in 'ASP General' started by Frank frank@null.null, Aug 21, 2007.

  1. Frank

    Frank Guest

    Hello everybody,
    I have an Access table with some fields set as text and I would like
    to convert them to integer.

    For some reason some values have wrongly been entered with chars, thus
    ALTER TABLE my_table ALTER COLUMN my_field Integer
    gives me a nice
    Microsoft JET Database Engine error '80040e07'
    Data type mismatch in criteria expression.

    If I manually convert the field using Access, I get warned of possible
    data loss, but the conversion takes place anyway.

    How can I force the conversion using just SQL? Is there something like
    an option "override errors" / "force conversion"?

    Thanks
    Frank
    Frank , Aug 21, 2007
    #1
    1. Advertising

  2. Frank wrote:
    > Hello everybody,
    > I have an Access table with some fields set as text and I would like
    > to convert them to integer.
    >
    > For some reason some values have wrongly been entered with chars, thus
    > ALTER TABLE my_table ALTER COLUMN my_field Integer
    > gives me a nice
    > Microsoft JET Database Engine error '80040e07'
    > Data type mismatch in criteria expression.


    This error message does not seem to have anything to do with your data
    in the table. However, having never worked with JetSQL DDL, I may be
    wrong.

    A quick look at the documentation seems to indicate that you are out of
    luck. My suggestion would be to

    add a new column with the correct datatype
    alter table my_table add column tmpcol integer NULL

    use a SQL update statement to explicitly convert the values and put them
    into the new column:
    update my_table set tmpcol=clng(my_field) where isnumeric(my_field)

    if that statement raises an error (isnumeric is not perfect), you will
    need to open a recordset and loop through it, setting the new column's
    value one record at a time and catching any errors that occur

    drop the existing column
    ALTER TABLE my_table DROP COLUMN my_field

    Add it back in with the correct datatype
    alter table my_table add column my_field integer NULL

    use a SQL update statement to set its value:
    update my_table set tmpcol=clng(my_field)

    Drop the temp column
    ALTER TABLE my_table DROP COLUMN tmpcol


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Aug 21, 2007
    #2
    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. Kersh

    Alter Rendered HTML for page

    Kersh, Jul 24, 2003, in forum: ASP .Net
    Replies:
    6
    Views:
    503
    John Saunders
    Aug 6, 2003
  2. eRic
    Replies:
    2
    Views:
    387
  3. Wee Bubba
    Replies:
    2
    Views:
    392
    Wee Bubba
    May 17, 2004
  4. Shanli RICHEZ
    Replies:
    7
    Views:
    471
    Scott Allen
    Nov 28, 2004
  5. -berlin.de
    Replies:
    0
    Views:
    143
    -berlin.de
    Jun 30, 2007
Loading...

Share This Page