CODEWITHSUNDEEP
c#asp.netasp.net-mvcms-accessrazor
Teun
Teun

Reputation: 23

ASP.NET MVC C# Razor SQL 1 of 2 almost identical Update Queries doesn't work

I am making an application to keep our stockroom easier to use.
I made two methods to change some values in my database. one to change te stock of an item and one to change the location where this item is stored.
for some reason when I use the first method the program changes the stock, but when I use the second method it won't change anything...
I really don't get it cuz they're almost identical.
Is there anyone who sees something I do not?

public void ChangeStock(double value, string Number)
    {
        string ConnString = "[connectionstring]";
        string SqlString = "Update Item Set Stock = ? WHERE ItemNumber= ?";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("Stock", value);
                cmd.Parameters.AddWithValue("ItemNumber", Number);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    public void ChangeLocation(string Location, string Number)
    {
        string ConnString = "[connectionstring]";
        string SqlString = "Update Item Set Location = ? WHERE ItemNumber = ?";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("ItemNumber", Number);
                cmd.Parameters.AddWithValue("Location", Location);       
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

Upvotes: 2

Views: 98

Answers (1)

HansUp
HansUp

Reputation: 97131

Since you're using OleDb, Access ignores the parameter names. Therefore you must supply the parameter values in the order Access expects them, which is the order in which the parameter placeholders appear in the CommandText.

In the first UPDATE case, you're supplying the parameter values in the correct order. However, in the second case, you're supplying them in the opposite order compared to what Access expects. Use this order ...

cmd.Parameters.AddWithValue("Location", Location);
cmd.Parameters.AddWithValue("ItemNumber", Number);

Upvotes: 3

Related Questions