need to minus value from the database using parameters c#

Question

1.00/5 (1 vote)

See more:

, +

i had that code that minus value from database and when i want to use it with datagridview mus make it in parameter and i cant make it

C#

SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=inventory;Integrated Security=True");

           con.Open();
          for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
        SqlCommand com = new SqlCommand(" update product set quantity = quantity + '" + Convert.ToInt32(dataGridView1.Rows[i].Cells[5].Value ?? DBNull.Value) + "' where itemname = '"  + dataGridView1.Rows[i].Cells[0].Value ?? DBNull.Value + "' ", con);
          com.ExecuteNonQuery();
          }

because when i write it in this form i get error Unclosed quotation mark after the character string 'منتج 3'.

Incorrect syntax near 'منتج 3'.

i think it give m this error because it want it in parameter

Posted 6-Aug-15 7:14am

ÃHmed Élkady

Add a Solution

Comments

PIEBALDconsult 6-Aug-15 13:18pm

Yes, I'm sure you want to use a parameter.

ÃHmed Élkady 6-Aug-15 13:21pm

how i can do tis statement in parameters

[no name] 6-Aug-15 13:34pm

It would be the exact same way that you have done before. Why is it all of a sudden you are unable? You did it before, you can do it now.

ÃHmed Élkady 6-Aug-15 13:38pm

yes i make parameters wit insert put never with update i search on the internet how to ma the minus operation using the parmeters but i cant find

[no name] 6-Aug-15 13:42pm

What is it that makes you think that makes any difference at all?

PIEBALDconsult 6-Aug-15 13:57pm

All SQL parameterization is done exactly the same way.

ZurdoDev 6-Aug-15 13:53pm

Use com.Parameters.AddWithValue("@paramName", paramValue) instead of string concatenation. Not only will it fix your problem but it will make your code safe from SQL injections.

ÃHmed Élkady 6-Aug-15 15:21pm

Thank All For Help

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Answer 1 · 2015-08-06T09:45:00

Your approach is wrong from the very beginning. The query composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

Are you getting the idea? The string taken from a control can be anything, including… a fragment of SQL code.

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

—SA

Richard Deeming · Answer 2 · 2015-08-06T08:22:00

Solution 1

As mentioned in the comments, there's no difference between using parameters for an UPDATE statement, using parameters for an INSERT statement, using parameters for a SELECT statement, or using parameters for a DELETE statement.

Since you're updating multiple rows, you probably want to use a transaction. That way, if there's an error on any row, all of the changes will be rolled back.

Something like this should work:

C#

using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=inventory;Integrated Security=True"))
{
    connnection.Open();
    
    using (SqlTransaction transaction = connection.BeginTransaction())
    using (SqlCommand command = new SqlCommand("UPDATE product SET quantity = quantity + @quantity WHERE itemname = @itemname", connection, transaction))
    {
        var pQuantity = command.Parameters.Add("@quantity", SqlDbType.Int);
        var pItemName = command.Parameters.Add("@itemname", SqlDbType.NVarChar, 50);
        
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            pQuantity.Value = Convert.ToInt32(dataGridView1.Rows[i].Cells[5].Value);
            pItemName.Value = dataGridView1.Rows[i].Cells[0].Value ?? DBNull.Value;
            command.ExecuteNonQuery();
        }
        
        transaction.Commit();
    }
}

Posted 6-Aug-15 8:22am

Richard Deeming

Comments

ÃHmed Élkady 6-Aug-15 15:21pm

i will try bro and give you fedback

ÃHmed Élkady 6-Aug-15 16:07pm

it's work witout rror ut nothing happn

Richard Deeming 6-Aug-15 16:28pm

Debug your code and make sure the loop is executing, and the parameters are getting the values you expect.

Try storing the value returned from ExecuteNonQuery in a variable, and inspect that value. If it's 0 every time, then your query isn't matching any rows in the database.

ÃHmed Élkady 6-Aug-15 16:36pm

connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
using (SqlCommand command = new SqlCommand("UPDATE product SET quantity = quantity + @quantity where itemname = @itemname", connection, transaction))
{
SqlParameter pQuantity = command.Parameters.Add("@quantity", SqlDbType.Int );
SqlParameter pItemName = command.Parameters.Add("@itemname", SqlDbType.VarChar, 50);

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
pQuantity.Value = Convert.ToInt32(dataGridView1.Rows[i].Cells[5].Value);
pItemName.Value = dataGridView1.Rows[i].Cells[0].Value ?? DBNull.Value;
command.ExecuteNonQuery();
}
command.ExecuteNonQuery();
transaction.Commit();

ÃHmed Élkady 6-Aug-15 16:37pm

what do i had to change

Richard Deeming 6-Aug-15 16:44pm

You need to debug your code.

ÃHmed Élkady 6-Aug-15 16:49pm

how i know it in unit game engine ma it by try and catch statment

Richard Deeming 6-Aug-15 16:52pm

I've not used Unity, but there are apparently extensions for Visual Studio which will enable debugging:
Visual Studio Tools for Unity 1.9 | MSDN Blogs[^]