Solved

Value was either too large or too small for an Int32

Posted on 2011-02-16
19
1,435 Views
Last Modified: 2012-05-11
Hi Experts,

I am trying to perform an update operation on a gridview when I receieved the above error.

the error code is:-

customer.Update(Convert.ToInt32(gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString()), txtA_PERCENTAGE.Text);

the datatype of A_PERCENTAGE is varchar2 (255 byte), oracle db

thanks
#region 'gvSubDetails_RowUpdating Code'
    protected void gvSubDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox txtA_PERCENTAGE = (TextBox)gvSubDetails.Rows[e.RowIndex].FindControl("txtA_PERCENTAGE");
        customer.Update(Convert.ToInt32(gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString()), txtA_PERCENTAGE.Text);
        gvSubDetails.EditIndex = -1;
        //FillCustomerInGrid();
    }
    #endregion

Open in new window

0
Comment
Question by:Sky4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
19 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 34904839
The maximum value for an int32 is: 2,147,483,647

What is the value in
gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString())
and
txtA_PERCENTAGE.Text
0
 

Author Comment

by:Sky4
ID: 34905059
the value in both is 0
0
 

Author Comment

by:Sky4
ID: 34905117
I have changed it to

 customer.Update(gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString(), txtA_PERCENTAGE.Text);

and this is the actual update function (see code) but when I click on the update link, the old value isn't updated. It simply reverts back to the old value

thanks


public void Update(string aid, string percentage)
    {
        string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE='" + percentage + "' Where A_ID=" + aid;
        OracleConnection conn = new OracleConnection(cnstr);
        conn.Open();
        OracleCommand cmd = new OracleCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
    }

Open in new window

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 53

Expert Comment

by:Dhaest
ID: 34905129
So you are updating the record wiht A_ID = 0 ?
0
 

Author Comment

by:Sky4
ID: 34905159
no only the a_percentage is being updated

a_id is a primary key, value is 15000019293416

sorry about the confusion
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 34905174
So gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString() contains
15.000.019.293.416
But int32 only can have:
         2,147,483,647

That's why you get the error whan trying to parse the value into an int32
And by the way, why are you parsing it when you pass this to the update-function. It expects a string

Try the following

customer.Update(gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString(), txtA_PERCENTAGE.Text);
0
 

Author Comment

by:Sky4
ID: 34905191
Hi

I have done as you posted but the data is not being updated
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34905249
When you add a breakpoint here: cmd.ExecuteNonQuery();

Is your sql-string correctly ? Does it work when you execute this string directly on your oracle ?
Did you check the value in the database after the update or only on the screen (that maybe is not refreshed correctly)
0
 

Author Comment

by:Sky4
ID: 34905288
IT works directly in the database (oracle)

UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE='100' Where A_ID='15000018488532'

it does not complain about the string in c#..............whatelse can i try
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34905297
how is A_ID in the database declared ? As a varchar, numeric, ... ?

Do you get any error when you add a try-catch ?
public void Update(string aid, string percentage)
    {
        try
        {
        string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE='" + percentage + "' Where A_ID=" + aid;
        OracleConnection conn = new OracleConnection(cnstr);
        conn.Open();
        OracleCommand cmd = new OracleCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
        }
        catch (Exception ex)
        {
          throw ex;
        }
    }

Open in new window

0
 

Author Comment

by:Sky4
ID: 34905307
i think oracle uses this a "colon" in front of the attribute name, if so I have changed the query string to:-

string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET :A_PERCENTAGE='" + percentage + "' Where :A_ID=" + aid;

but I now receive
+            $exception      {"ORA-01747: invalid user.table.column, table.column, or column specification"}      System.Exception {Oracle.DataAccess.Client.OracleException}
0
 

Author Comment

by:Sky4
ID: 34905413
A_ID is declared as a Number, I will change it to a varchar2
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34905437
>> A_ID is declared as a Number, I will change it to a varchar2

your sql must be
UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE='100' Where A_ID=15000018488532


instead of

UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE='100' Where A_ID='15000018488532'

Try:
string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE='" + percentage + "' Where A_ID=" + Convert.ToInt64(aid);
0
 

Author Comment

by:Sky4
ID: 34905542
I have tried with your last post, stuck a breakpoint

I guess this is what is written back to oracle--            sql      "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE='64' Where A_ID=15000029470939"      string

but no data is updated
0
 

Author Comment

by:Sky4
ID: 34905735

this seems to have solved part of the problem..........
string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE = :percentage Where A_ID = :aid "

now get

PL/SQL ORA-01008 : Not all variables bound
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34905763
Probably you now need to add commandParameters ....
public void Update(string aid, string percentage)
    {
        try
        {
        string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE = :percentage Where A_ID = :aid "
        OracleConnection conn = new OracleConnection(cnstr);
        conn.Open();
        OracleCommand cmd = new OracleCommand(sql, conn);

cmd.Parameters.Add(new OracleParameter(":percentage ", OracleType.Varchar));
cmd.Parameters[":percentage "].Value = percentage;

cmd.Parameters.Add(new OracleParameter(":aid", OracleType.Number));
cmd.Parameters[":aid"].Value = aid;

       cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
        }
        catch (Exception ex)
        {
          throw ex;
        }
    }

Open in new window

0
 

Author Comment

by:Sky4
ID: 34905828
Now it no longer errors but still not updates. I have attached the full code


===========update method================ 
public void Update(string aid, string percentage)
    {
        try
        {
            string sql = "UPDATE HE_STUDENT_GRADES_II_TEMP SET A_PERCENTAGE = :percentage Where A_ID = :aid ";
            OracleConnection conn = new OracleConnection(cnstr);
            conn.Open();
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.Parameters.Add(new OracleParameter(":percentage ", OracleDbType.NVarchar2));
            cmd.Parameters[":percentage "].Value = percentage;
            cmd.Parameters.Add(new OracleParameter(":aid", OracleDbType.NVarchar2));
            cmd.Parameters[":aid"].Value = aid;
            cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

asp.net page
==================
 <asp:GridView ID="gvSubDetails" AutoGenerateColumns="False" runat="server" Width="735px"
            OnRowCancelingEdit="gvSubDetails_RowCancelingEdit" OnRowEditing="gvSubDetails_RowEditing" DataKeyNames="A_ID"
            OnRowUpdating="gvSubDetails_RowUpdating">
            <AlternatingRowStyle CssClass="altrowstyle" />
            <HeaderStyle CssClass="headerstyle" />
            <RowStyle CssClass="rowstyle" />
            <Columns>                
                   <asp:TemplateField HeaderText="%" SortExpression="A_PERCENTAGE">
                    <EditItemTemplate>
                            <asp:TextBox ID="txtA_PERCENTAGE" runat="server" Text='<%# Bind("A_PERCENTAGE") %>'></asp:TextBox>
                    </EditItemTemplate>             
                    <ItemTemplate>
                        <asp:Label ID="LblA_PERCEnt" runat="server" Text='<%# Bind("A_PERCENTAGE") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField> 
                <asp:TemplateField HeaderText="Edit" ShowHeader="False">
                    <EditItemTemplate>
                        <asp:LinkButton ID="lbnUpdate" runat="server" CausesValidation="True" CommandName="Update"
                            Text="Update"></asp:LinkButton>
                        <asp:LinkButton ID="lbnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
                            Text="Cancel"></asp:LinkButton>
                    </EditItemTemplate>
                      <ItemTemplate>
                        <asp:LinkButton ID="lbnEdit" runat="server" CausesValidation="False" CommandName="Edit"
                            Text="Edit"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>           
            </Columns>
            <EmptyDataTemplate>
                No Data is available
            </EmptyDataTemplate>
        </asp:GridView>    




code behind
============================
 customer.Update(gvSubDetails.DataKeys[e.RowIndex].Values[0].ToString(), txtA_PERCENTAGE.Text);

Open in new window

0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 34906246
have you tried

cmd.Parameters.Add(new OracleParameter("percentage ", OracleDbType.NVarchar2));
cmd.Parameters["percentage "].Value = percentage;
cmd.Parameters.Add(new OracleParameter("aid", OracleDbType.NVarchar2));
cmd.Parameters["aid"].Value = aid;
0
 

Author Comment

by:Sky4
ID: 34906322
@ EDDYKT:
i have tried it, although I am no longer receiving the errors, everytime I try to update a record it reverts back to the original
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question