Link to home
Start Free TrialLog in
Avatar of Sky4
Sky4

asked on

Value was either too large or too small for an Int32

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

Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

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
Avatar of Sky4
Sky4

ASKER

the value in both is 0
Avatar of Sky4

ASKER

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

So you are updating the record wiht A_ID = 0 ?
Avatar of Sky4

ASKER

no only the a_percentage is being updated

a_id is a primary key, value is 15000019293416

sorry about the confusion
ASKER CERTIFIED SOLUTION
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sky4

ASKER

Hi

I have done as you posted but the data is not being updated
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)
Avatar of Sky4

ASKER

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

Avatar of Sky4

ASKER

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}
Avatar of Sky4

ASKER

A_ID is declared as a Number, I will change it to a varchar2
>> 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);
Avatar of Sky4

ASKER

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
Avatar of Sky4

ASKER


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

Avatar of Sky4

ASKER

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

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;
Avatar of Sky4

ASKER

@ 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