Solved

Value was either too large or too small for an Int32

Posted on 2011-02-16
19
1,407 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
  • 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 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