Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Value was either too large or too small for an Int32

Posted on 2011-02-16
19
Medium Priority
?
1,468 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

783 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