I have an application that I'm trying to use to make some database updates. However, whenever I try to save, I get this error message:

Incorrect Syntax Near '1'

What does this error message mean, and how can I solve it? Here's my code:

try
{
    if (dgvStockEntry.Rows.Count > 0)
    {
        if (MessageBox.Show("Are you sure you want to save this records?", stitle, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
        {
            for (int i = 0; i < dgvStockEntry.Rows.Count; i  )
            {
                cn.Open();
                cm = new SqlCommand("Update Products set Quantity = Quantity '"   dgvStockEntry.Rows[i].Cells[5].Value.ToString()   "' where Pcode like '"   dgvStockEntry.Rows[i].Cells[2].Value.ToString()   "'", cn);
                cm.ExecuteNonQuery();
                cn.Close();

                cn.Open();
                cm = new SqlCommand("Update StockEntry set Quantity = Quantity '"   dgvStockEntry.Rows[i].Cells[5].Value.ToString()   ", Status = 'Done' where Id like '"   dgvStockEntry.Rows[i].Cells[0].Value.ToString()   "'", cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            Clear();
            LoadStockEntry();
        }
    }
}
catch (Exception ex)
{
    cn.Close();
    MessageBox.Show(ex.Message, stitle, MessageBoxButtons.OK, MessageBoxIcon.Warning);
} 

CodePudding user response：

Well apart from the comments made by others like it is better to use parametrized queries, and you should not use like without wildcards, it is obvious that the current queries cannot work.

Your code to generate the first query:

cm = new SqlCommand("Update Products set Quantity = Quantity '"   dgvStockEntry.Rows[i].Cells[5].Value.ToString()   "' where Pcode like '"   dgvStockEntry.Rows[i].Cells[2].Value.ToString()   "'", cn);
                cm.ExecuteNonQuery();

This would evaluate to something like

Update Products set Quantity = Quantity '1' where Pcode like 'SomeCode'

which is invalid sql.

Correct would be

Update Products set Quantity = 1 where Pcode like 'SomeCode'

or

Update Products set Quantity = '1' where Pcode like 'SomeCode'

or

Update Products set Quantity = Quantity   1 where Pcode like 'SomeCode'

so you would have to update your code to make it output something like this (I don't know the exact requirement and column data type.)

CodePudding user response：

You can debug and run your SQL query in SQL management studio to check what is wrong with your query.

try
        {
            if (dgvStockEntry.Rows.Count > 0)
            {
                if (MessageBox.Show("Are you sure you want to save this records?", stitle, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    for (int i = 0; i < dgvStockEntry.Rows.Count; i  )
                    {
                        cn.Open();   

                        var quantity = dgvStockEntry.Rows[i].Cells[5].Value.ToString();
                        var pcode = dgvStockEntry.Rows[i].Cells[2].Value.ToString();
                        var sql = $"Update Products set Quantity = '{stockeEntry}' where Pcode like 'pcode'"
                        cm = new SqlCommand(, cn);
                        cm.ExecuteNonQuery();
                     
                        cm = new SqlCommand("Update StockEntry set Quantity = Quantity '"   dgvStockEntry.Rows[i].Cells[5].Value.ToString()   ", Status = 'Done' where Id like '"   dgvStockEntry.Rows[i].Cells[0].Value.ToString()   "'", cn);
                        cm.ExecuteNonQuery();
                        cn.Close();
                    }
                    Clear();
                    LoadStockEntry();
                }
                
                
            }
        }
        catch (Exception ex)
        {
            cn.Close();
            MessageBox.Show(ex.Message, stitle, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        } 

CodePudding user response：

You can try something like this with multiple added considerations.

try
{
    if (dgvStockEntry.Rows.Count > 0)
    {
        if (MessageBox.Show("Are you sure you want to save this records?", stitle, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
        {
            for (int i = 0; i < dgvStockEntry.Rows.Count; i  )
            {
                cn.Open();
                cm = new SqlCommand("Update Products set Quantity = @quantity where Pcode = @pcode", cn);
                SqlParameter[] paramarray = 
                    {
                        new SqlParameter("@quantity", SqlDbType.Varchar) {Value = dgvStockEntry.Rows[i].Cells[5].Value.ToString()},
                        new SqlParameter("@pcode", SqlDbType.Varchar) {Value = dgvStockEntry.Rows[i].Cells[2].Value.ToString()}
                    };
                cm.Parameters.AddRange(paramarray);
                cm.ExecuteNonQuery();
                cn.Close();

                cn.Open();
                cm = new SqlCommand("Update StockEntry set Quantity = @quantity, Status = 'Done' where Id = @id", cn);
                paramarray.clear();
                cm.Parameters.Clear();
                paramarray = 
                    {
                        new SqlParameter("@quantity", SqlDbType.Varchar) {Value = dgvStockEntry.Rows[i].Cells[5].Value.ToString()},
                        new SqlParameter("@id", SqlDbType.Varchar) {Value = dgvStockEntry.Rows[i].Cells[0].Value.ToString()}
                    };                
                cm.Parameters.AddRange(paramarray);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            Clear();
            LoadStockEntry();
        }
    }
}
catch (Exception ex)
{
    cn.Close();
    MessageBox.Show(ex.Message, stitle, MessageBoxButtons.OK, MessageBoxIcon.Warning);
} 

• Use parametrized queries
• Don't use like if the right side of the expression do not contain a wilcard (%)
• You don't need to open/close your connection twice, you can use the same connection to run multiple commands.