Update statement in MySQL using C#

Question

I've been building a small inventory system for my workplace and have stumbled on an error that I cannot seem to fix

 private void Update(string num,string name, string quant, string location, string category, string numquery)
    {
       // "UPDATE Inventory SET Inventorynumber='"+ num +"',Inventory_Name='"+name+"', Quantity ='"+ quant+"',Location ='"+ location+"' Category ='"+ category+"' WHERE Inventorynumber ='"+ numquery +"';";
        string query = "UPDATE Inventory SET Inventorynumber='" + Convert.ToInt16(num) + "',Inventory_Name='" + name + "', Quantity ='" + quant + "',Location ='" + location + "' Category ='" + category + "' WHERE Inventorynumber ='" + Convert.ToInt16(numquery) + "'";
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = query;
            cmd.Connection = serverconnection;
            cmd.ExecuteNonQuery();
            this.CloseConnection();
            Bind();
        }
    }

I have no idea what to change here. Any help would be appreciated.

Any clue to what error? I just see code, no errors. Please post stacktrace. — SynerCoder, Dec 10 '13 at 10:32
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Category ='test' WHERE Inventorynumber ='2'' at line 1 — Darkestlyrics, Dec 10 '13 at 10:33

score 13 · Accepted Answer · edited Mar 28 '20 at 22:24

Problem: You are missing the comma after location parameter in your query.
Solution: You need to separate the parameters using a comma.

Suggestion : Use parameterized queries to avoid SQL Injection Attacks.

Try this:

private void Update(string num,string name, string quant, string location, string category, string numquery)
    {
       // "UPDATE Inventory SET Inventorynumber='"+ num +"',Inventory_Name='"+name+"', Quantity ='"+ quant+"',Location ='"+ location+"' Category ='"+ category+"' WHERE Inventorynumber ='"+ numquery +"';";
        string query = "UPDATE Inventory SET Inventorynumber=@Inventorynumber,Inventory_Name=@Inventory_Name, Quantity =@Quantity ,Location =@Location,Category =@Category WHERE Inventorynumber =@Inventorynumber";
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Inventorynumber",Convert.ToInt16(num));
            cmd.Parameters.AddWithValue("@Inventory_Name",name);
            cmd.Parameters.AddWithValue("@Quantity",quant);
            cmd.Parameters.AddWithValue("@Location",location);
            cmd.Parameters.AddWithValue("@Category",category);
            cmd.Parameters.AddWithValue("@Inventorynumber",Convert.ToInt16(numquery));
            cmd.Connection = serverconnection;
            cmd.ExecuteNonQuery();
            this.CloseConnection();
            Bind();
        }
    }

dude, liked your way of answering Problem,solution,suggestion ... nice — C Sharper, Dec 10 '13 at 10:57

Steve · Answer 2 · 2013-12-10T10:52:05.210

Yes the error is in the missing comma, but this is the result of all that mess with string concatenation that ends always in subtle syntax errors.
Why don't you use a parameterized query? It is a lot simpler to write and you avoid parsing errors like this and (more important) you avoid Sql Injections

private void Update(string num,string name, string quant, string location, string category, string numquery)
{
    string query = "UPDATE Inventory SET Inventorynumber=@num, Inventory_Name=@name, " +
                   "Quantity =@qty,Location =@loc, Category =@cat " + 
                   "WHERE Inventorynumber =@numquery";
    if (this.OpenConnection() == true)
    {
        MySqlCommand cmd = new MySqlCommand(query, serverconnection);
        cmd.Parameters.AddWithValue("@num", Convert.ToInt16(num));
        cmd.Parameters.AddWithValue("@name", name);
        cmd.Parameters.AddWithValue("@qty", quant);
        cmd.Parameters.AddWithValue("@loc", location);
        cmd.Parameters.AddWithValue("@cat", category);
        cmd.Parameters.AddWithValue("@numquery", Convert.ToInt16(numquery));
        cmd.ExecuteNonQuery();
        this.CloseConnection();
        Bind();
    }
}

As a side note I have some doubts about some parameters type. Are you sure that quantity is really a string as implied by the presence of quotes around your original value? Also the numquery and num variables are of type string, you try to convert then to short integer and then you put them inside quotes (meaning that in the database the fields are of type text). This makes no sense at all. If the database expects numbers then do not use quotes, if the database expects strings then do not try to convert. Another reason to use a parameterized query that force you to reflect on these issues.

It's really my first time using this, I plan to clean it up. But for the moment we need it done quick and dirty. — Darkestlyrics, Dec 10 '13 at 10:42
Not to argue with you, but trust me, string concatenation is NEVER the quick way to go. I agree on the dirty part though :-) — Steve, Dec 10 '13 at 10:46

score 2 · Answer 3 · answered Dec 10 '13 at 10:34

2

You are missing a Comma between location and category. You have heard this million times befor i know, but its really much better using prepared statements so you do not have to take care of this kind of things and your code is much more readable.

answered Dec 10 '13 at 10:34

CloudyMarble

36,908
70
97
130

My bad. Thanks for the help. guys at work had a good laugh at me for that. – Darkestlyrics Dec 10 '13 at 10:40
Its a very common thing, happens to all of us. – CloudyMarble Dec 10 '13 at 10:43

score 1 · Answer 4 · answered Dec 10 '13 at 10:35

1

You missed the comma

Location ='" + location + "', Category ='" + category + "'
//  see the `,` between Location and Category

answered Dec 10 '13 at 10:35

Linga

10,379
10
52
104

score 1 · Answer 5 · answered Dec 10 '13 at 10:35

you have missed comma(,) in query:

string query = "UPDATE Inventory SET Inventorynumber='" + Convert.ToInt16(num) + "',Inventory_Name='" + name + "', Quantity ='" + quant + "',Location ='" + location + "' Category ='" + category + "' WHERE Inventorynumber ='" + Convert.ToInt16(numquery) + "'";

Make it as:

string query = "UPDATE Inventory SET Inventorynumber='" + Convert.ToInt16(num) + "',Inventory_Name='" + name + "', Quantity ='" + quant + "',Location ='" + location + "', Category ='" + category + "' WHERE Inventorynumber ='" + Convert.ToInt16(numquery) + "'";

score 0 · Answer 6 · answered Dec 10 '13 at 10:34

0

Try removing the ' single quotes around the integers?

answered Dec 10 '13 at 10:34

Bradley Smith

119
3

Update statement in MySQL using C#

6 Answers6

Linked