-1

As the title says I'm trying to change the rating of a book in my database via my GUI. I change it from 1 to 5 for example. When I use breakpoints I see that the query translates correctly to what it should be (I think) and I get no errors. Yet it doesn't change the actual row in the database.

        public int ChangeRating(Book book, int rating, string title)
    {
        book.Query = "UPDATE Books SET Rating = " + rating + " WHERE Title = '" + title + "'";

        return SqlManager.RunNonQuery(book);
    }

After that it goes to my SqlManager.

       public static int RunNonQuery<T>(T value) where T : IIsQueriable, new()
    {
        using (SqlConnection con = new SqlConnection(cs)) //connectie wordt aangemaakt
        {
            using (SqlCommand cmd = con.CreateCommand()) //maakt een command aan
            {
                cmd.CommandText = value.Query; //de waarde van Query wordt de command
                return TryExecuteNonQuery<T>(cmd);
            }
        }
    }

    private static int TryExecuteNonQuery<T>(SqlCommand cmd) where T : IIsQueriable, new()
    {
        int result;

        try
        {
            cmd.Connection.Open();
            cmd.Prepare();
            result = cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
            throw;
        }
        finally
        {
            cmd.Connection.Close();
        }
        MessageBox.Show(result.ToString());
        return result;
    }

in the database. Rating is an int and the title is a Nvarchar(MAX) incase that matters.

Chaoss1848
  • 19
  • 7
  • ,Provide your sample data. – Mansoor May 23 '17 at 07:07
  • 1
    Are you 100% sure your connection string (`cs` in your code) is pointing to the right database? – Jcl May 23 '17 at 07:07
  • 1
    This code is highly exposed to Sql Injection. In particular I would advise you to not build your own database framework that doesn't support parameterized queries – Steve May 23 '17 at 07:07
  • This seems a case of DataDirectory side effect. Look at this answer [Why saving changes to database fails](https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460) – Steve May 23 '17 at 07:10
  • The connection does work as I got other methods already in place that work fine. also it's just for school. and alright i'll read through it – Chaoss1848 May 23 '17 at 07:16
  • I changed the datadirectory things but nothing has changed. – Chaoss1848 May 23 '17 at 07:26
  • I'd try something really silly: change the update for a select and check whether it is returning the expected row. – Zalomon May 23 '17 at 07:27
  • Just tested it in a seperate query. it works fine. (used a breakpoint to see what the exact query would be and just put that in a query and it worked. both select and update) – Chaoss1848 May 23 '17 at 07:36

2 Answers2

0

Instead if equal to sign use like operator like this 

book.Query = "UPDATE Books SET Rating = " + rating + " WHERE Title like %'" + title + "'%";

or check the value in database which you are using in where clause. I think this will help you.

Anurag_Soni
  • 542
  • 2
  • 17
0

The return value 1 of your ExecuteNonQuery() command dont mean that 1 row has been changed but one row triggered the command. If your database user has only a read permission the command is triggered at one row but not executed. So please make shure that your database user has the a write permission.

mn_dc466
  • 143
  • 1
  • 1
  • 8