0

I've just gotten started with creating a simple inventory extension for a web app and I've run into an issue where I'm having trouble updating the rows in a table for values that already exist in the Product column.

The method is pretty similar to the insert data method, which is working so I'm not exactly sure about what is going on. Here's the code that I have:

static void ReplaceInventory(string product, int qty)
{
    using (SqlConnection con = new SqlConnection(
           "Data Source=TestSQL;Initial Catalog=TestInv;User ID=tester;Password=passwordtest;"))
    {
        con.Open();

        try
        {
            using (SqlCommand command = new SqlCommand(
                   "UPDATE Inventory SET Qty = @Qty WHERE Product = @Product", con))
            {
                command.Parameters.Add(new SqlParameter("@Qty", qty));
                command.Parameters.Add(new SqlParameter("@Product", product));
                command.ExecuteNonQuery();
            }
        }
        catch  
        {
            Console.WriteLine("Record not updated");
        }

        con.Close();
    }
}

This works however:

static void AddInventory(string product, int qty)
{
    using (SqlConnection con = new SqlConnection(
           "Data Source=TestSQL;Initial Catalog=TestInv;User ID=tester;Password=passwordtest;"))
    {
        con.Open();

        try
        {
            using (SqlCommand command = new SqlCommand(
                   "INSERT INTO Inventory VALUES(@Product, @Qty)", con))
            {
                command.Parameters.Add(new SqlParameter("Product", product));
                command.Parameters.Add(new SqlParameter("Qty", qty));
                command.ExecuteNonQuery();
            }
        }
        catch
        {
            Console.WriteLine("Count not insert.");
        }

        con.Close();
    }
}

Any help or pointers would be much appreciated!

5
  • Try to remove @ symbol in command.Parameters.Add(new SqlParameter("@Qty", qty)); and command.Parameters.Add(new SqlParameter("@Product", product)); so both lines have "Qty" and "Product" as query parameter names, you have it that way in INSERT query which works. Commented Jul 2, 2015 at 22:30
  • 2
    Is it throwing an exception? What is the exception? Commented Jul 2, 2015 at 22:47
  • It isn't throwing an exception, which makes troubleshooting this maddening since I'm getting no message that would indicate a specific error. Removed the @ symbols, but it still is behaving this way. Commented Jul 2, 2015 at 23:15
  • You're passing in a @product string as the "key" to your product table. Are you absolutely sure that you got that right? Do you have a difference in spelling, or is there an extra space at the end of that string, either in your C# code or in the database? Strings as keys are a tricky business..... Commented Jul 3, 2015 at 4:58
  • I'm pretty sure it's the same, I just checked the table on the server and the two columns in it are "Product" which is a "text" type column, and "Qty" which is an "int". The table was added programmatically-- the command was "CREATE TABLE Inventory (Product TEXT, Qty INT)", { command.ExecuteNonQuery(); } Commented Jul 3, 2015 at 18:11

3 Answers 3

1

Try setting the command type as Text, before executing the query:

command.CommandType = System.Data.CommandType.Text
Sign up to request clarification or add additional context in comments.

1 Comment

Added this before the command.parameter statements, no dice so far.
0

Have you debugged your code and checked if the values are passed properly?

Try including the SqlDbType and size.

command.Parameters.Add("@Product",SqlDbType.NVarChar, 20,product);

For implicit conversion, try AddWithValue

command.Parameters.AddWithValue(new SqlParameter("@Qty", qty));
command.Parameters.AddWithValue(new SqlParameter("@Product", product));

According to MSDN:

AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value.

See this link for reference.

But using command.Parameters.Add is more reliable and safe because the type of the data is defined and the database doesn't need to identify and convert the data being sent.

2 Comments

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...
Hi @marc_s , I know about it. Edited my answer for better purposes. Thanks!
0

Figured it out. The "Product" column in the table that I had created in my database was of a "text" data type, and so it wasn't recognizing the "=" as valid in the UPDATE expression. When I swapped the "=" sign out for LIKE the function worked as expected. Thanks for the feedback!

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.