Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Alter column conversion problem

Reply
Thread Tools

Alter column conversion problem

 
 
Frank frank@null.null
Guest
Posts: n/a
 
      08-21-2007
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
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-21-2007
Frank http://www.velocityreviews.com/forums/(E-Mail Removed) 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.


 
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
Alter - Perl extension for Alter Ego Objects anno4000@radom.zrz.tu-berlin.de Perl Misc 0 06-30-2007 04:13 PM
Microsoft may have to alter Vista Silverstrand Front Page News 0 07-25-2005 11:04 PM
best way to dynamically alter page contents and speed Wee Bubba ASP .Net 2 05-17-2004 10:00 AM
Alter Rendered HTML for page Kersh ASP .Net 6 08-06-2003 03:29 PM
alter web.config from asp.net web form? eRic ASP .Net 2 07-31-2003 05:33 PM



Advertisments