Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Value was either too large or too small for an Int32

Posted on 2011-02-16
19
Medium Priority
?
1,446 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

721 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