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

Last Reply 8 months ago By pandeyism

Posted 8 months 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 8 months ago Modified on 8 months 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 8 months 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)