[Solved] Update query not working with Arabic languages from Windows Form DataGridView

Hi smile,

You need to prefix N before the ItemCode value in the query.

smile says:

con.SqlQuery("Update tblStores set Qty = Qty - " + Convert.ToInt32(row.Cells["Quantity"].Value) + " where ItemCode = '" + Convert.ToString(row.Cells["ItemCode"].Value.ToString()) + "'");

Change with below.

con.SqlQuery("Update tblStores set Qty = Qty - " + Convert.ToInt32(row.Cells["Quantity"].Value) + " where ItemCode = N'" + Convert.ToString(row.Cells["ItemCode"].Value.ToString()) + "'");

Check this example. Now please take its reference and correct your code.

SQL

CREATE TABLE tblStore(Price INT,Qty INT,ItemName NVARCHAR(50))
INSERT INTO tblStore VALUES(500,100,N'قبعات نتوء-1')
INSERT INTO tblStore VALUES(600,100,N'حراس-1')
INSERT INTO tblStore VALUES(700,100,N'مكملات-1')

Namespaces

C#

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

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        BindGrid();
    }

    private void btnUpdate_Click(object sender, EventArgs e)
    {
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            string constring = @"Data Source=.;Initial Catalog=master;User id = sa;password=pass@123";
            using (SqlConnection con = new SqlConnection(constring))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    string itemName = Convert.ToString(row.Cells["ItemCode"].Value);
                    int quantity = Convert.ToInt32(row.Cells["Quantity"].Value);
                    string query = string.Format("Update tblStore set Qty = Qty - {0} where ItemName = N'{1}'", quantity, itemName);
                    cmd.CommandText = query;
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        dataGridView1.DataSource = null;
        BindGrid();
    }

    private void BindGrid()
    {
        string constring = @"Data Source=.;Initial Catalog=master;User id = sa;password=pass@123";
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM tblStore", con))
            {
                cmd.CommandType = CommandType.Text;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        dataGridView1.AutoGenerateColumns = false;
                        dataGridView1.ColumnCount = 3;

                        dataGridView1.Columns[0].Name = "ItemCode";
                        dataGridView1.Columns[0].HeaderText = "ItemCode";
                        dataGridView1.Columns[0].DataPropertyName = "ItemName";

                        dataGridView1.Columns[1].HeaderText = "Price";
                        dataGridView1.Columns[1].Name = "Price";
                        dataGridView1.Columns[1].DataPropertyName = "Price";

                        dataGridView1.Columns[2].Name = "Quantity";
                        dataGridView1.Columns[2].HeaderText = "Quantity";
                        dataGridView1.Columns[2].DataPropertyName = "Qty";
                        dataGridView1.DataSource = dt;
                        dataGridView1.AllowUserToAddRows = false;
                    }
                }
            }
        }
    }
}

VB.Net

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BindGrid()
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        For Each row As DataGridViewRow In dataGridView1.Rows
            Dim constring As String = "Data Source=.;Initial Catalog=master;User id = sa;password=pass@123"
            Using con As SqlConnection = New SqlConnection(constring)
                Using cmd As SqlCommand = New SqlCommand()
                    Dim itemName As String = Convert.ToString(row.Cells("ItemCode").Value)
                    Dim quantity As Integer = Convert.ToInt32(row.Cells("Quantity").Value)
                    Dim query As String = String.Format("Update tblStore set Qty = Qty - {0} where ItemName = N'{1}'", quantity, itemName)
                    cmd.CommandText = query
                    cmd.Connection = con
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        Next

        dataGridView1.DataSource = Nothing
        BindGrid()
    End Sub

    Private Sub BindGrid()
        Dim constring As String = "Data Source=.;Initial Catalog=master;User id = sa;password=pass@123"
        Using con As SqlConnection = New SqlConnection(constring)
            Using cmd As SqlCommand = New SqlCommand("SELECT * FROM tblStore", con)
                cmd.CommandType = CommandType.Text
                Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        dataGridView1.AutoGenerateColumns = False
                        dataGridView1.ColumnCount = 3

                        dataGridView1.Columns(0).Name = "ItemCode"
                        dataGridView1.Columns(0).HeaderText = "ItemCode"
                        dataGridView1.Columns(0).DataPropertyName = "ItemName"

                        dataGridView1.Columns(1).HeaderText = "Price"
                        dataGridView1.Columns(1).Name = "Price"
                        dataGridView1.Columns(1).DataPropertyName = "Price"

                        dataGridView1.Columns(2).Name = "Quantity"
                        dataGridView1.Columns(2).HeaderText = "Quantity"
                        dataGridView1.Columns(2).DataPropertyName = "Qty"
                        dataGridView1.DataSource = dt
                        dataGridView1.AllowUserToAddRows = False
                    End Using
                End Using
            End Using
        End Using
    End Sub
End Class

Screenshots

The Form

Database table after update