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

Bill HendersonWeb MarketingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 HendersonWeb MarketingAuthor Commented:
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.
Maybe something with character encoding? Perhaps there are invisible characters that show up in the formatting.

By hard coding, I meant have you tried just copy and pasting the query directly into the code and running that without parsing the file? If doesn't error, then you know that there is something extra coming in when you parse.

Also, can you get the length of the string you're inserting into the database? Presumably it would be 2 characters long. If it's any longer, it again points to something with invisible characters or character encoding.

Are you perhaps not parsing the CSV correctly? I noticed that the name is IGLEHART PT,TAMMY. Maybe that comma is throwing things off and you're not actually putting ND into the field? That could throw things off and have it be that you're trying to insert "GARRISON" as the billing_state. What happens if you increase billing_state to be 8 characters so that it could hold "GARRISON"?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill HendersonWeb MarketingAuthor Commented:
We got past the error by parameterizing the query. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.