0

I created this table in SQL Server to store data about a student's fees:

create table Fee(
    feeID int primary key identity(1,1),
    classID int foreign key references Class(classID),
    nameID int foreign key references Students(studentsID),
    firstNameID int foreign key references Students(studentsID),
    dateOfAdmission varchar(50),
    monthKey int foreign key references Month(monthID), sum int
)

And in Visual Studio I tried to save the data in the table with the following method:

private void btn_fee_Save_Click(object sender, EventArgs e)
{
    int Class = Convert.ToInt32(cmbFeeClass.SelectedValue.ToString());
    int Name = Convert.ToInt32(cmbFeeName.SelectedValue.ToString());
    int firstName = Convert.ToInt32(cmbFeeFirstName.SelectedValue.ToString());
    int Month = Convert.ToInt32(cmbMonth.SelectedValue.ToString());
    try { 
    if (cmbFeeClass.Text != "" && cmbFeeName.Text != "" && cmbFeeFirstName.Text != "" && cmbMonth.Text != "" && txtSum.Text != "")
    {
        cmd = new SqlCommand("insert into Fee values('"+ Class + "', '" + Name + "', '" + firstName + "', " +
            "'" + dtDateOfAdmission.Text + "', '" + Month + "', '" + "', '" + txtSum.Text + "')", con.ConnectionOpening());

        cmd.ExecuteNonQuery();

        MessageBox.Show("The data has been saved", "INFO", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    else
    {
        MessageBox.Show("Please fill in all the fields!", "INFO", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    con.CloseConnection();
}

How can I change the code so that I no longer have this error:

An explicit value for the identity column in table 'Fee' can only be specified when a column list is used and IDENTITY_INSERT is ON

Dale K
  • 25,246
  • 15
  • 42
  • 71
Turing
  • 25
  • 4
  • 2
    1) Use the full `insert` syntax where you explicitly specify the columns you are inserting (its best practice to always do this). 2) Use `SqlParameter`'s to pass data, not concatenated string which leave you wide open to SQL injection. – Dale K Dec 23 '20 at 21:11
  • https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection – mjwills Dec 23 '20 at 21:48
  • `dateOfAdmission varchar(50),` Nope - start over! – SMor Dec 24 '20 at 00:07

1 Answers1

1

You are getting the error because you haven't specified the columns in the INSERT statement. Without these, SQL is going to assign the values to columns on a first-come, first-served basis and in your table definition, the feeID column is first. The feeID column is defined as a Primary Key Identity column and this columns value is always auto-generated.

The code below shows how to construct the Sql statement with a Columns section, and also makes use of Parameters. This will help you to prevent significant errors in the future.

using (SqlConnection con = new SqlConnection(<__ADD_YOUR_CONNECTION_STRING_HERE__>))
{
con.open()
using (SqlCommand cmd = con.CreateCommand())
{
    String sql = "INSERT INTO FEE ( classID,  nameID,  firstNameID,  dateOfAdmission,  monthKey)" +
                 "VALUES          (@classID, @nameID, @firstNameID, @dateOfAdmission, @monthKey)";

    cmd.CommandText = sql;
    cmd.CommandType = CommandType.Text;

    cmd.Parameters.Add(new SqlParameter("classID", SqlDbType.Int) { Value = Class });
    cmd.Parameters.Add(new SqlParameter("nameID", SqlDbType.Int) { Value = Name });
    cmd.Parameters.Add(new SqlParameter("firstnameID", SqlDbType.Int) { Value = firstName });
    cmd.Parameters.Add(new SqlParameter("dateOfAdmission", SqlDbType.VarChar) { Value = dtDateOfAdmission.Text });
    cmd.Parameters.Add(new SqlParameter("monthKey", SqlDbType.Int) { Value = Month });

    command.ExecuteNonQuery();
}
}

Note:

You should seriously consider changing the type of the dateOfAdmission column from VARCHAR(50) to DATE - this will store the date values as proper dates, not as strings. This will also make your job much easier in the future.

Making using of the code using (...) is recommended practice to ensure the database resources are released when they are not needed.

JayV
  • 3,238
  • 2
  • 9
  • 14