ASP.Net GridView RowUpdating Error converting data type varchar to numeric

Last Reply one year ago By pandeyism

Posted one year ago

Hi to all,

Error converting data type varchar to numeric.

I get an exception from my application. It is thrown on cmd.ExecuteNonQuery(). I tried to update a row in database. In db the type is decimal(10,2), I convert it to decimal when it updated but does not work. How to resolve this convertion?

    protected void GridViewIncomes_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        SqlConnection sqlCon = new SqlConnection(CS);
        TextBox tbDenumire = (TextBox)GridViewIncomes.Rows[e.RowIndex].FindControl("tbDenumire");
        TextBox tbSuma = (TextBox)GridViewIncomes.Rows[e.RowIndex].FindControl("tbSuma");
        TextBox tbData = (TextBox)GridViewIncomes.Rows[e.RowIndex].FindControl("tbData");
        
        TextBox tbDescriere = (TextBox)GridViewIncomes.Rows[e.RowIndex].FindControl("tbDescriere");
        int VenitId = Convert.ToInt32(GridViewIncomes.DataKeys[e.RowIndex].Values[0]);
        string sql = "UPDATE Venituri SET Denumire='" + tbDenumire.Text + "',Data='" + tbData.Text + "',Descriere='" + tbDescriere.Text + "',Suma='" + Convert.ToDecimal(tbSuma.Text) + "' WHERE VenitId=" + VenitId + "";

        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        sqlCon.Open();
        * cmd.ExecuteNonQuery();
        sqlCon.Close();
        if (cmd.ExecuteNonQuery() == 1)
        {

            lblSuccessMessage.Text = "Actualizat cu succes!";
            GridViewIncomes.EditIndex = -1;
            GridViewIncomes.DataBind();
        }
       
        lblSuccessMessage.Text = "";
    }

 

<asp:TemplateField HeaderText="Suma">
    <EditItemTemplate>
        <asp:TextBox ID="tbSuma" runat="server" Text='<%# Bind("Suma") %>'></asp:TextBox>
    </EditItemTemplate>
    <ItemTemplate>
        <asp:Label ID="lbSuma" runat="server" Text='<%# Bind("Suma") %>'></asp:Label>
    </ItemTemplate>
</asp:TemplateField>

This looks the database:

[VenitId]   INT             IDENTITY (1, 1) NOT NULL,
[Denumire]  NVARCHAR (20)   NOT NULL,
[Data]      NVARCHAR (50)   NOT NULL,
[Descriere] NVARCHAR (MAX)  NULL,
[Suma]      DECIMAL (10, 2) NULL,

 

Posted one year ago Modified on one year ago

Hi ada20,

Refer below sample.

HTML

Numeric Value :
<asp:TextBox ID="txtNumber" runat="server"></asp:TextBox>
<br />
<asp:Button Text="Insert" runat="server" OnClick="Save" />
<br />
<asp:GridView ID="gvDecimalValues" runat="server" AutoGenerateColumns="false" DataKeyNames="Id"
    OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit" OnRowUpdating="OnRowUpdating">
    <Columns>
        <asp:TemplateField HeaderText="Value">
            <ItemTemplate>
                <asp:Label ID="lblValue" runat="server" Text='<%# Eval("Value") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtValue" runat="server" Text='<%# Eval("Value") %>'></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;
using System.Data;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        BindGrid();
    }
}

protected void Save(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    SqlCommand cmd = new SqlCommand("INSERT INTO Testdt VALUES(@Value)", con);
    cmd.Parameters.AddWithValue("@Value", Convert.ToDecimal(txtNumber.Text));
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    BindGrid();
}

public void BindGrid()
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    SqlCommand cmd = new SqlCommand("SELECT * FROM Testdt", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    gvDecimalValues.DataSource = dt;
    gvDecimalValues.DataBind();
}

protected void OnRowEditing(object sender, GridViewEditEventArgs e)
{
    gvDecimalValues.EditIndex = e.NewEditIndex;
    this.BindGrid();
}

protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow row = gvDecimalValues.Rows[e.RowIndex];
    int id = Convert.ToInt32(gvDecimalValues.DataKeys[e.RowIndex].Values[0]);
    string value = (row.FindControl("txtValue") as TextBox).Text;
    string query = "UPDATE Testdt SET Value=@Value WHERE Id=@Id";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@Value", value);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    gvDecimalValues.EditIndex = -1;
    this.BindGrid();
}

protected void OnRowCancelingEdit(object sender, EventArgs e)
{
    gvDecimalValues.EditIndex = -1;
    this.BindGrid();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        BindGrid()
    End If
End Sub

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
    Dim cmd As SqlCommand = New SqlCommand("INSERT INTO Testdt VALUES(@Value)", con)
    cmd.Parameters.AddWithValue("@Value", Convert.ToDecimal(txtNumber.Text))
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
    BindGrid()
End Sub

Public Sub BindGrid()
    Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
    Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Testdt", con)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)
    gvDecimalValues.DataSource = dt
    gvDecimalValues.DataBind()
End Sub

Protected Sub OnRowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    gvDecimalValues.EditIndex = e.NewEditIndex
    Me.BindGrid()
End Sub

Protected Sub OnRowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    Dim row As GridViewRow = gvDecimalValues.Rows(e.RowIndex)
    Dim id As Integer = Convert.ToInt32(gvDecimalValues.DataKeys(e.RowIndex).Values(0))
    Dim value As String = (TryCast(row.FindControl("txtValue"), TextBox)).Text
    Dim query As String = "UPDATE Testdt SET Value=@Value WHERE Id=@Id"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Parameters.AddWithValue("@Id", id)
            cmd.Parameters.AddWithValue("@Value", value)
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    gvDecimalValues.EditIndex = -1
    Me.BindGrid()
End Sub

Protected Sub OnRowCancelingEdit(ByVal sender As Object, ByVal e As EventArgs)
    gvDecimalValues.EditIndex = -1
    Me.BindGrid()
End Sub

Screenshot


Posted one year ago

Hi @pandeyism,

I don't need an insert into table. I need an update on the table and I have already tried this: 

Convert.ToDecimal(tbSuma.Text)

 


Posted one year ago