Vendredi Vendredi - 11 days ago 5
SQL Question

Can anyone tell me what's wrong with this Update Statement? Access Mdb

private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
using (OleDbConnection conn = new OleDbConnection(Con))
{
conn.Open();

OleDbCommand cmd = new OleDbCommand("UPDATE ACTB SET FirstName = @FirstName, LastName = @LastName, GrossIncome = @GrossIncome, LessTNT = @LessTNT, TaxableIncomeCE = @TCI, " +
"TaxableIncomePE = @ADDTI, GrossTaxableIncome = @GTI, LessTE = @LessTE, LessPPH = @LessPPH, NetTax = @LessNTI," +
"TaxDue = @TD, HeldTaxCE = @TWCE, HeldTaxPE = @TWPE, TotalTax = @TATW WHERE ID = @ID", conn);

// OleDbCommand cmd = new OleDbCommand("UPDATE ACTB SET (FirstName, LastName, GrossIncome, LessTNT, TaxableIncomeCE, TaxableIncomePE, GrossTaxableIncome, LessTE, LessPPH, NetTax, TaxDue, HeldTaxCE, HeldTaxPE, TotalTax) = " +
// "VALUES(@FirstName, @LastName, @GrossIncome, @LessTNT, @TCI, @ADDTI, @GTI, @LessTE, @LessPPH, @LessNTI, @TD, @TWCE, @TWPE, @TATW)", conn);
cmd.Parameters.AddWithValue("@ID", txtID.Text);
cmd.Parameters.AddWithValue("@FirstName", txtFirst.Text);
cmd.Parameters.AddWithValue("@LastName", txtLast.Text);
cmd.Parameters.AddWithValue("@GrossIncome", Convert.ToDouble(txtGross.Text));
cmd.Parameters.AddWithValue("@LessTNT", Convert.ToDouble(txtLessTNT.Text));
cmd.Parameters.AddWithValue("@TCI", Convert.ToDouble(txtTCI.Text));
cmd.Parameters.AddWithValue("@ADDTI", Convert.ToDouble(txtADDTI.Text));
cmd.Parameters.AddWithValue("@GTI", Convert.ToDouble(txtGTI.Text));
cmd.Parameters.AddWithValue("@LessTE", Convert.ToDouble(txtLessTE.Text));
cmd.Parameters.AddWithValue("@LessPPH", Convert.ToDouble(txtLessPPH.Text));
cmd.Parameters.AddWithValue("@LessNTI", Convert.ToDouble(txtLessNTI.Text));
cmd.Parameters.AddWithValue("@TD", Convert.ToDouble(txtTD.Text));
cmd.Parameters.AddWithValue("@TWCE", Convert.ToDouble(txtTWCE.Text));
cmd.Parameters.AddWithValue("@TWPE", Convert.ToDouble(txtTWPE.Text));
cmd.Parameters.AddWithValue("@TATW", Convert.ToDouble(txtTATW.Text));
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
lblSuccess.Text = "Successfully updated the records!";
Restore();
}
}


Been looking for something wrong with this but I can't find it. It doesn't update the table which
Restore()
handles btw. No errors are thrown.

Though it did have a "Connection string not properly initialized" and error in update statement errors earlier.

Answer

OleDbCommand doesn't have the concept of Name value parameters. So you MUST define them in the same order as in the query.

So first parameter should be

cmd.Parameters.AddWithValue("@FirstName", txtFirst.Text);
cmd.Parameters.AddWithValue("@LastName", txtLast.Text);
//and so on ...

//last one
cmd.Parameters.AddWithValue("@ID", txtID.Text);

Also you can use @ to write strings on multiple lines.

P.S There is no need to Close() the connection when you use using, this is done automatically by Dispose()

Comments