CODEWITHSUNDEEP
c#sql-server
Aman
Aman

Reputation: 9

Error when updating in database

I am getting error on updating database in C#. Here is the code:

string connectionstring = "server=AMAN;database=student;Integrated Security=True";
SqlConnection conn;
string Admission_no = txtAddmissionNo.Text;
SqlCommand cmd;
conn = new SqlConnection(connectionstring);
conn.Open();
string query = "update fees set prospectues_fee=@prospectues_fee, registration_fee=@registration_fee,admission_fee=@admission_fee ,security_money=@security_money,misslaneous_fee=@misslaneous_fee,development_fee=@development_fee,transport_fair=@transport_fair,computer_fee=@computer_fee ,activity=@activity,hostel_fee=@hostel_fee,dely_fine=@dely_fine,back_dues=@back_dues,tution_feemonth=@tution_feemonth ,tution_fee=@tution_fee,other_fee=@other_fee,total=@total,deposit=@deposit,dues=@dues where Admission_no=@Admission_no";
cmd=new SqlCommand(query,conn);
cmd.Parameters.AddWithValue("@Admission_no", Admission_no);
cmd.Parameters.AddWithValue("@prospectues_fee", prospectues_fee);
cmd.Parameters.AddWithValue("@registration_fee", registration_fee);
cmd.Parameters.AddWithValue("@admission_fee", admission_fee);
cmd.Parameters.AddWithValue("@security_money", security_money);
cmd.Parameters.AddWithValue("@misslaneous_fee", misslaneous_fee);
cmd.Parameters.AddWithValue("@development_fee", development_fee);
cmd.Parameters.AddWithValue("@transport_fair", transport_fair);
cmd.Parameters.AddWithValue("@computer_fee", computer_fee);
cmd.Parameters.AddWithValue("@activity", activity);
cmd.Parameters.AddWithValue("@hostel_fee", hostel_fee);
cmd.Parameters.AddWithValue("@dely_fine", dely_fine);
cmd.Parameters.AddWithValue("@back_dues", back_dues);
cmd.Parameters.AddWithValue("@tution_fee", tution_fee);
cmd.Parameters.AddWithValue("@other_fee", other_fee);
cmd.Parameters.AddWithValue("@total", total);
cmd.Parameters.AddWithValue("@tution_feemonth", tution_feemonth);
cmd.Parameters.AddWithValue("@deposit", deposit_fee);
cmd.Parameters.AddWithValue("@dues", dues);

cmd  = new SqlCommand(query, conn);

try
{
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

Error is @prospectues_fee scalar must be declared, which I have already declared.

Upvotes: 1

Views: 78

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062745

The error is simpler than I thought:

cmd  = new SqlCommand(query, conn);
... // lots of code
cmd  = new SqlCommand(query, conn);
try
{
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

You are creating a second command just prior to executing it; this second command has the text but no parameters. Remove this second new SqlCommand line.

This sounds like the dreaded null vs DBNull issue. null in a parameter means "don't send this". Which is really really silly, but there we are. Try with:

cmd.Parameters.AddWithValue("@prospectues_fee",
    ((object)prospectues_fee) ?? DBNull.Value);

now repeat for all of the parameters... or just add a method that loops over them and checks them:

static void FixTheCrazy(DbCommand command) {
    foreach(DbParameter param in command.Parameters) {
        if(param.Value == null) param.Value = DBNull.Value;
    }
}

Alternatively, use a tool like dapper that will do it for you:

using(varconn = new SqlConnection(connectionstring))
{
    conn.Execute(query, new {
        Admission_no, prospectues_fee, registration_fee, ...
        deposit_fee, dues });
}

Upvotes: 4

Related Questions