OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Page 1 of 1

7 Replies - 16458 Views - Last Post: 29 June 2011 - 12:22 PM

#1 JamesConley

• D.I.C Regular

Reputation: 19
• Posts: 341
• Joined: 09-March 09

OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 05 June 2010 - 03:34 PM

Hey everyone!!

So I have recently started working with SQL and occasionally get some errors that I need explained to me as if I were an eight year old.

Error: OUT OF RANGE VAULE FOR COLUMN ' ' AT ROW ' '

I have done some researching (google, MySQL forums, etc..) and have found a solution ( I think) to the problem. The solution that I found was this SET GLOBAL SQL_MODE=”;

Two things:

One: I could not get the CMD to accept this, IDK why but it wouldn't.

Two: Can someone explain what exactly this command is doing if this is the command that I need in order to fix my problem. I am all about getting a quick answer or solution but I would really like to understand why I am using whatever it is that I am. I feel it helps me understand further problems and the overall process of what I'm doing, in this case MySQL.

Extra Info: Windows XP and My SQL 5.1.47

Thanks to everyone who stops by and takes a look!!

Is This A Good Question/Topic? 0

Replies To: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

#2 macosxnerd101

• Games, Graphs, and Auctions

Reputation: 12077
• Posts: 44,969
• Joined: 27-December 08

Re: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 05 June 2010 - 06:51 PM

What's the value you are trying to insert and what is the type/limitations of that column? This should be an easy fix with an ALTER col MODIFY type query.

#3 no2pencil

• Professor Snuggly Pants

Reputation: 6449
• Posts: 30,306
• Joined: 10-May 07

Re: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 05 June 2010 - 07:03 PM

Without knowing what the column data type is set to & what you were trying to insert, then it's impossible to know how this failed.

My guess would be most likely you tried to insert a number with quotes to a column that is set to int, &/or you tried to insert a string that was longer than the predefined length of the column value.

#4 JamesConley

• D.I.C Regular

Reputation: 19
• Posts: 341
• Joined: 09-March 09

Re: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 05 June 2010 - 07:13 PM

The exact line was:

INSERT INTO CUSTOMER_TBL VALUES
('12', 'MARYS GIFT SHOP', '435 MAIN ST', 'DANVILLE', 'IL', '47978', '3178567221', '3178523434');

I think @no2pencil is right. I did a DESCRIBE CUSTOMER_TBL; and I did set the fax number type to INT(10). Should I change this? Can I use an ALTER command for this?

Thanks guys!!! I appreciate it!!

#5 macosxnerd101

• Games, Graphs, and Auctions

Reputation: 12077
• Posts: 44,969
• Joined: 27-December 08

Re: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 05 June 2010 - 07:14 PM

macosxnerd101, on 05 June 2010 - 09:51 PM, said:

What's the value you are trying to insert and what is the type/limitations of that column? This should be an easy fix with an ALTER col MODIFY type query.

Yes- you should be able to use an ALTER statement with a MODIFY clause.

#6 no2pencil

• Professor Snuggly Pants

Reputation: 6449
• Posts: 30,306
• Joined: 10-May 07

Re: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 05 June 2010 - 07:19 PM

jpconleyiv, on 05 June 2010 - 08:13 PM, said:

Should I change this?

I would just remove the quotes in your insert statement (specifically for that column).

Remember, programming (& generally all computing) is "Do as I say", not "Do as I want".

#7 JamesConley

• D.I.C Regular

Reputation: 19
• Posts: 341
• Joined: 09-March 09

Re: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 05 June 2010 - 07:21 PM

macosxnerd101, on 06 June 2010 - 01:14 AM, said:

macosxnerd101, on 05 June 2010 - 09:51 PM, said:

What's the value you are trying to insert and what is the type/limitations of that column? This should be an easy fix with an ALTER col MODIFY type query.

Yes- you should be able to use an ALTER statement with a MODIFY clause.

lol!! Ok thanks bro!!

Should I just replace it with VARCHAR(10)?

#8 anuin

Reputation: 0
• Posts: 1
• Joined: 29-June 11

Re: OUT OF RANGE VALUE FOR COLUMN ' ' AT ROW ' '

Posted 29 June 2011 - 12:22 PM

jpconleyiv, on 05 June 2010 - 07:21 PM, said:

macosxnerd101, on 06 June 2010 - 01:14 AM, said:

macosxnerd101, on 05 June 2010 - 09:51 PM, said:

What's the value you are trying to insert and what is the type/limitations of that column? This should be an easy fix with an ALTER col MODIFY type query.

Yes- you should be able to use an ALTER statement with a MODIFY clause.

lol!! Ok thanks bro!!

Should I just replace it with VARCHAR(10)?

Jp I had the exact same problem with the exact same exercise. What I beleive the problem is that in sql the integer type doesn't hold that big of a number. It apparently only goes up to 2147483647. So what you have to do is change the type from integer(10) to bigint(10) by putting this in the command line.

alter table customer_tbl modify column cust_fax bigint(10);

then when I went back to input the data agian it worked perfectly.