asked on

mysql returning Data too long for column error

Below is the query and the error returned

insert into accounts (account_number,contact_name,business_name ,business_address ,business_city ,business_state ,business_zipcode ,business_phone ,disclaimer_viewed) values('1424','IGLEHART PT,TAMMY','','1424 50TH AVE. NW','GARRISON','ND','58540','(949) 300-8680',1) 
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 5.3(a) Driver][mysqld-5.1.72-community]Data too long for column 'business_state'

Open in new window

The field is question here is business_state which is accepting the value 'ND' above was earlier a varchar(2) we later made it a varchar(4) , still it returns an error.

We are parsing a csv file but i am not too sure if the error has anything to do with that since the above query outputted by the code if copied and fired directly in the mysql query console runs okay and record gets inserted but the same query bumps into an error when run thru the code.

any advice or ideas would be just great

Thanks

quizwedge

I'll take two wild guesses...

1. When you say that the query is not working in code, are you using the query with the value 'ND' hard coded in? If not, it could be some kind of formatting from the CSV file.

2. Do you have more than one copy of the database? Perhaps you're pointing to a different version in your code that doesn't have the increased field size?

Bill Henderson

ASKER

Thanks Quizwedge - yeah that's the thing - the parsed values from the CSV, when displayed in the error, are showing 'ND' and the "too long" error, but we can literally paste the query included in the error in our console, and it inserts perfectly.

So I'm not sure what you mean by hard-coded, but the ND value appears as normal in the CSV, when opened in Excel or Notepad.

We don't have a second copy of the database with the field unchanged. Indeed, changing the length of the field from 2 to 4 characters did nothing. That was our wild guess!

Any other suggestions? I mean anything related to the parsing process itself to keep an eye out for?

Thanks again.

ASKER CERTIFIED SOLUTION

quizwedge

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Henderson

ASKER

We got past the error by parameterizing the query. Thanks