Member’s books in possession in Library System

Hello I am currently working into a library system and I do want to view how many books are in possession of a specific member.

I want to add a copies to student whenever I issue a book to them.

I have tried:

public void StudentPossession()
    {
        cm = new SqlCommand("INSERT INTO tblStudent(stCopies) VALUES (@stCopies)", cn);
        cm.Parameters.AddWithValue("@stCopies", txtCopies.Text);
        cm.ExecuteNonQuery();
    }

but it doesn’t insert into members row, instead it shows

|-------------|---------------|------------|
|   ID        |  studNumber   |  Copies    |
|-------------|---------------|------------|
|   1         |  34543534     |    NULL    |
|-------------|---------------|------------|
|   2         |  NULL         |    1       |
|-------------|---------------|------------|

I have also tried:

cm = new SqlCommand("INSERT INTO tblStudent(stCopies) VALUES (@stCopies) WHERE studNumber ='" + txtStudNumber.Text + "'", cn);

But doesn’t work either.

I don’t know how to fix this so any help would be appreciated. Or is there anyway how to achieve this?

Answer

If you want to insert a new record:

cm = new SqlCommand("INSERT INTO tblStudent (studNumber, stCopies) VALUES (@studNumber, @stCopies)", cn);
cm.Parameters.AddWithValue("@stCopies", txtCopies.Text);
cm.Parameters.AddWithValue("@studNumber", txtStudNumber.Text);
cm.ExecuteNonQuery();

If you want to update an existing record, as opposed to inserting a new one, use this code:

cm = new SqlCommand("UPDATE tblStudent SET stCopies = @stCopies WHERE studNumber = @studNumber", cn);
cm.Parameters.AddWithValue("@stCopies", txtCopies.Text);
cm.Parameters.AddWithValue("@studNumber", txtStudNumber.Text);
cm.ExecuteNonQuery();

Also, it’s not recommended to use AddWithValue for strings. Instead you should prepare values like so:

cmd.Parameters.Add("@studNumber", SqlDbType.VarChar, 30).Value= txtStudNumber.Text;

Where VarChar is the type of the field in the database, and 30 is the maximum length of the field in the database. From this code I would expect to see VARCHAR(30).