I have trouble in deleting and updating records in the database using SqlDAtaAdapter, UpdateCommand and DeleteCommand.
Any suggestions are very helpful. Thanks
these are the commands with adapter
class SQLServer
{
public static SqlDataAdapter daFaculty = new SqlDataAdapter();
using(SqlCommand cmdUpdate = new SqlCommand())
{
cmdUpdate.Connection = SQLServer.attendanceDB;
cmdUpdate.CommandText = "UPDATE tblFaculty SET idno = @idno, lastname = @lastname, firstname = @firstname, middlename = @middlename, fieldSpecs = @fieldspecs WHERE idno = '@idnoref'";
cmdUpdate.Parameters.Add("@idno", SqlDbType.VarChar, 0, "idno");
cmdUpdate.Parameters.Add("@lastname", SqlDbType.VarChar, 0, "lastname");
cmdUpdate.Parameters.Add("@firstname", SqlDbType.VarChar, 0, "firstname");
cmdUpdate.Parameters.Add("@middlename", SqlDbType.VarChar, 0, "middlename");
cmdUpdate.Parameters.Add("@fieldspecs", SqlDbType.VarChar, 0, "fieldspecs");
SqlParameter paramUpdate = cmdUpdate.Parameters.Add("@idnoref", SqlDbType.VarChar, 0, "idno");
paramUpdate.SourceVersion = DataRowVersion.Original;
daFaculty.UpdateCommand = cmdUpdate;
}
using (SqlCommand cmdDelete = new SqlCommand())
{
cmdDelete.Connection = SQLServer.attendanceDB;
cmdDelete.CommandText = "DELETE FROM tblFaculty WHERE idno = '@oldidno'";
SqlParameter paramDelete = new SqlParameter();
paramDelete = cmdDelete.Parameters.Add("@oldidno", SqlDbType.VarChar, 0, "IDno");
paramDelete.SourceVersion = DataRowVersion.Original;
daFaculty.DeleteCommand = cmdDelete;
}
}
These are the function for updating and deleting
public static void DeleteFaculty(string idno)
{
General.dtFaculty.Select("idno = '" + idno + "'")[0].Delete();
SQLServer.daFaculty.Update(General.dtFaculty);
}
public static void UpdateFaculty(string idReference, string idno, string lastname, string firstname, string middlename, string fieldspecs)
{
DataRow[] row = General.dtFaculty.Select("idno = '" + idReference + "'");
foreach (DataRow item in row)
{
item["lastname"] = lastname;
item["firstname"] = firstname;
item["middlename"] = middlename;
item["fieldspecs"] = fieldspecs;
}
SQLServer.daFaculty.Update(General.dtFaculty);
}
and this is the event for updating and deleting
private void btnSave_Click(object sender, EventArgs e)
{
General.UpdateFaculty(idsel, txtID.Text, txtLname.Text, txtFname.Text, txtMname.Text, txtFieldSpec.Text);
}
private void btnDelete_Click(object sender, EventArgs e)
{
General.DeleteFaculty(idsel);
}