-2

I have 3 tables: Student, Books and Borrows:

  • Student (id, name, FK_borrow)
  • Books (id, name, nbre_books_available int)
  • Borrows (borrow_from, borrow_to, FK_student, FK_books)

I want to insert values into the table Borrows after checking if the column nbre_books_available is not 0, and update it.

This my attempt 

private void fillborrow()
{
    cmd.Connection = cn;
    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "insert into borrows Values ('"+dateTimePicker1.Value+"','"+dateTimePicker2.Value+"',"+int.Parse(textBox1.Text)+","+int.Parse(textBox2.Text)+")" ;

    cn.Open();
    int a = cmd.ExecuteNonQuery();
    cn.Close();

    if (a == 0)
    { 
        MessageBox.Show("Erreur"); 
    }
    else
    {
        MessageBox.Show("Ajouter avec success"); 
    }

    cmd.CommandText = "update books set nbre_current = nbre_current - 1 where CodeO = " + int.Parse(textBox1.Text);

    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
}

I don't know how to Add the requete that check if the nbre_books_available column is 0 or not

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ted man
  • 1
  • 3
    Do Not concat+values+into+strings+like+that+to+make+SQL. It is error prone and makes gruesome code. Use SQL parameters always. Please read [ask] and take the [tour] – Ňɏssa Pøngjǣrdenlarp Jan 28 '18 at 22:35
  • Thank you for your comment i haven't used SQL parameters yet i'm still learning from some tutorials and most of them use the concatenation,and yes doesn't look that good,i will surely get into parameters – ted man Jan 28 '18 at 22:43
  • 1
    @tedman you **must** get into parameters; it doesn't need to be hard - tools like "Dapper" make it incredibly easy; without parameters: your system is toast within minutes on the internet – Marc Gravell Jan 28 '18 at 22:48

1 Answers1

-1

This is essentially the same question as this

You can perform your exist check at the same time as inserting and then check the return value of ExecuteNonQuery().

If it returns 0 then no rows were inserted, otherwise it was successful.

Scott Perham
  • 2,410
  • 1
  • 10
  • 20