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
– SynerCoder, Commented Dec 10, 2013 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
– Darkestlyrics, Commented Dec 10, 2013 at 10:33

Pablo Alexis Domínguez Grau · Accepted Answer · 2020-03-28 22:24:10Z

13

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();
        }
    }

edited Mar 28, 2020 at 22:24

Pablo Alexis Domínguez Grau

7597 silver badges20 bronze badges

answered Dec 10, 2013 at 10:43

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

C Sharper Over a year ago

dude, liked your way of answering Problem,solution,suggestion ... nice

Steve · Accepted Answer · 2013-12-10 10:52:05Z

3

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.

edited Dec 10, 2013 at 10:52

answered Dec 10, 2013 at 10:38

Steve

217k22 gold badges242 silver badges296 bronze badges

2 Comments

Darkestlyrics Over a year ago

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.

Steve Over a year ago

Not to argue with you, but trust me, string concatenation is NEVER the quick way to go. I agree on the dirty part though :-)

CloudyMarble · Accepted Answer · 2013-12-10 10:34:47Z

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, 2013 at 10:34

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

2 Comments

Darkestlyrics Over a year ago

My bad. Thanks for the help. guys at work had a good laugh at me for that.

CloudyMarble Over a year ago

Its a very common thing, happens to all of us.

Linga · Accepted Answer · 2013-12-10 10:35:06Z

1

You missed the comma

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

answered Dec 10, 2013 at 10:35

Linga

10.6k10 gold badges57 silver badges109 bronze badges

Comments

C Sharper · Accepted Answer · 2013-12-10 10:35:13Z

1

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) + "'";

answered Dec 10, 2013 at 10:35

C Sharper

8,66430 gold badges96 silver badges158 bronze badges

Comments

Bradley Smith · Accepted Answer · 2013-12-10 10:34:11Z

0

Try removing the ' single quotes around the integers?

answered Dec 10, 2013 at 10:34

Bradley Smith

1193 bronze badges

Collectives™ on Stack Overflow

Update statement in MySQL using C#

6 Answers 6

1 Comment

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related