Out of Range Error When Updating A Decimal Value

I am trying to update a database that has decimal(4,1) in a field. It can hold 100, but when I change a value in a gridview to 100 and update it, I get the error below. Any reason wny?

Parameter value '100.0' is out of range.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.ArgumentException: Parameter value '100.0' is out of range.

Source Error:

Line 1841: int returnValue;
Line 1842: try {
Line 1843: returnValue = command.ExecuteNonQuery();
Line 1844: }
Line 1845: finally {

Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs Line: 1843

Stack Trace:

[ArgumentException: Parameter value '100.0' is out of range.]
 System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) +4085
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +11

5
 StatisticsDALTableAdapters.StatisticsTableAdapter.UpdateStatistics(Nullable`1 ID, Nullable`1 PlayerID, Nullable`1 FG, Nullable`1 TTP, Nullable`1 FT, Nullable`1 RPG, Nullable`1 DEFR, Nullable`1 OFFR, Nullable`1 APG, Nullable`1 SPG, Nullable`1 BPG, Nullable`1 Turnovers, Nullable`1 PPG, String SYear, String EYear, String StatisticsType) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs:1843
 YourHoopSpace.BLL.StatisticsBLL.UpdateStatistic(Int32 ID, Int32 playerID, Nullable`1 fg, Nullable`1 ttp, Nullable`1 ft, Nullable`1 rpg, Nullable`1 defr, Nullable`1 offr, Nullable`1 apg, Nullable`1 spg, Nullable`1 bpg, Nullable`1 turnovers, Nullable`1 ppg, String sYear, String eYear, String statisticsType) in f:\MyProfile\App_Code\BLL\StatisticsBLL.cs:128

Is this the problem?

int returnValue;

Try decimal in your code. 

|||

HI~

How do you pass the parameters?

Here is sample code:

string str_test = TextBox1.Text; SqlConnection conn =new SqlConnection("Server=T-YOUNGF\\SQLEXPRESS;Database=pubs;uid=sa;pwd=pwd"); SqlCommand cmd =new SqlCommand("update decimal_test3 set column1=@.column1", conn); cmd.Parameters.AddWithValue("@.column1", 100.0); conn.Open();int i = cmd.ExecuteNonQuery(); Response.Write(i.ToString() +" row(s) has updated"); conn.Dispose();

And table:

create table decimal_test3 (column1decimal (4,1));

|||The problem was on my update I needed a decimal of (4,1) instead of (3,1) which I changed in the table not the stored procedure. Why does it need 4 instead of 3 to store 100?|||

decimal of (4,1) can hold upto 999.9

decimal of (3,1) can hold upto 99.9

So 100 is invalid.See decimal and numeric for details.

|||Thank you. I looked at that page, but really couldnt figure it out but now I know.sql

Out of range datetime value error when inserting using select...union

Hi all,
I am getting this error when insert values from one table to another in
the first table the values are varchar (10). In the second they are
datetime. The format of the data is mm/dd/yyyy to be easily converted
to dates. The conversion in this case is implicit as indicated in SQL
Server documentation. Here is my query:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description
UNION
SELECT program_id + 100000, program_name, start_date, end_date,
campaign_id AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.programs
UNION
SELECT execution_id + 200000, execution_name, start_date,
end_date, program_id + 100000 AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.executions
UNION
SELECT wave_id + 300000, wave_name, start_date, end_date,
mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL AS
ListID
FROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executions
WHERE mysqlfactiva.dbo.waves.execution_id =
mysqlfactiva.dbo.executions.execution_id

I am referencing programs table two times. If I just select this all I
get all data I need. When doing insert I get a message:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. The statement has been terminated.

If I execute just first part of the query before first union, I insert
data fine:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description

As soon as I use union I get the above error. This is very strange
since even when I execute the query using first union where the dates
come from the same table 'programs' I get the error. Why I can insert
from programs first time and can's second time?

Any help will be appreciated.

Thanks,
Stan(suslikovich@.yahoo.com) writes:
> I am getting this error when insert values from one table to another in
> the first table the values are varchar (10). In the second they are
> datetime. The format of the data is mm/dd/yyyy to be easily converted
> to dates. The conversion in this case is implicit as indicated in SQL
> Server documentation. Here is my query:

Note that the interpretation is dependent on the settings for DATEFORMAT
and LANGUAGE.

> INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
> ListID)
> SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
> mysqlfactiva.dbo.campaigns.campaign_name AS Name,
> MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,
> MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
> NULL AS ParentID,
> NULL AS ListID
> FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs

So the data type of mysqlfactiva.dbo.programs.start_date is varchar(10)?

Excuse me, if I am nosy, but a MIN on that columns appears somewhat
funny to me. If there is a date in early January you will use that
date, no matter which year it is in?

Better would be

MIN (CASE WHEN isdate(start_date) = 1 THEN start_date END

> As soon as I use union I get the above error. This is very strange
> since even when I execute the query using first union where the dates
> come from the same table 'programs' I get the error. Why I can insert
> from programs first time and can's second time?

In the first query, you are only taking the first value, in the
second query you are taking all the values. This query should give
you the bad data:

SELECT * FROM mysqlfactiva.dbo.programs.start_date
WHERE isdate(start_date) = 0 OR isdate(end_date) = 0

By the way, I seem recall that MySQL has a very liberal view on what
is a good date...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx