locked
The database is not updated with Update Command of SqlDataAdapter. RRS feed

  • Question

  • User289153921 posted

    string connectionString =
    ConfigurationManager.ConnectionStrings["WSConectionString"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    string selectQuery = "Select * from tblEmployer";
    SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);


    string strUpdateCommand = "UPDATE tblEmployer SET Name = @Name, DBA=@DBA, PriName=@PriName, PriAddress=@PriAddress,PriPhone=@PriPhone,SecName=@SecName,SecAddress=@SecAddress,SecPhone=@SecPhone, AltName1=@AltName1, AltAddress1=@AltAddress1, AltPhone1=@AltPhone1,AltName2 = @AltName2, AltAddress2 = @AltAddress2, AltPhone2 = @AltPhone2 where EAN = @EAN";
    SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection);


    updateCommand.Parameters.Add(new SqlParameter("@EAN", SqlDbType.NVarChar));
    updateCommand.Parameters["@EAN"].SourceVersion = DataRowVersion.Original;
    updateCommand.Parameters["@EAN"].SourceColumn = "EAN";


    updateCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
    updateCommand.Parameters["@Name"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@Name"].SourceColumn = "Name";

    updateCommand.Parameters.Add(new SqlParameter("@DBA", SqlDbType.NVarChar));
    updateCommand.Parameters["@DBA"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@DBA"].SourceColumn = "DBA";

    updateCommand.Parameters.Add(new SqlParameter("@PriName", SqlDbType.NVarChar));
    updateCommand.Parameters["@PriName"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@PriName"].SourceColumn = "PriName";

    updateCommand.Parameters.Add(new SqlParameter("@PriAddress", SqlDbType.NVarChar));
    updateCommand.Parameters["@PriAddress"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@PriAddress"].SourceColumn = "PriAddress";

    updateCommand.Parameters.Add(new SqlParameter("@PriPhone", SqlDbType.Char));
    updateCommand.Parameters["@PriPhone"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@PriPhone"].SourceColumn = "PriPhone";


    updateCommand.Parameters.Add(new SqlParameter("@SecName", SqlDbType.NVarChar));
    updateCommand.Parameters["@SecName"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@SecName"].SourceColumn = "SecName";

    updateCommand.Parameters.Add(new SqlParameter("@SecAddress", SqlDbType.NVarChar));
    updateCommand.Parameters["@SecAddress"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@SecAddress"].SourceColumn = "SecAddress";

    updateCommand.Parameters.Add(new SqlParameter("@SecPhone", SqlDbType.Char));
    updateCommand.Parameters["@SecPhone"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@SecPhone"].SourceColumn = "SecPhone";

    updateCommand.Parameters.Add(new SqlParameter("@AltName1", SqlDbType.NVarChar));
    updateCommand.Parameters["@AltName1"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@AltName1"].SourceColumn = "AltName1";

    updateCommand.Parameters.Add(new SqlParameter("@AltAddress1", SqlDbType.NVarChar));
    updateCommand.Parameters["@AltAddress1"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@AltAddress1"].SourceColumn = "AltAddress1";

    updateCommand.Parameters.Add(new SqlParameter("@AltPhone1", SqlDbType.Char));
    updateCommand.Parameters["@AltPhone1"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@AltPhone1"].SourceColumn = "SecPhone";

    updateCommand.Parameters.Add(new SqlParameter("@AltName2", SqlDbType.NVarChar));
    updateCommand.Parameters["@AltName2"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@AltName2"].SourceColumn = "AltName2";

    updateCommand.Parameters.Add(new SqlParameter("@AltAddress2", SqlDbType.NVarChar));
    updateCommand.Parameters["@AltAddress2"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@AltAddress2"].SourceColumn = "AltAddress2";

    updateCommand.Parameters.Add(new SqlParameter("@AltPhone2", SqlDbType.Char));
    updateCommand.Parameters["@AltPhone2"].SourceVersion = DataRowVersion.Current;
    updateCommand.Parameters["@AltPhone2"].SourceColumn = "AltPhone2";

    dataAdapter.UpdateCommand= updateCommand;

    DataTable ds = new DataTable();
    dataAdapter.Fill(ds);
    DataRow Row = ds.Rows[0];
    Row["DBA"] = empInfo.DBA; ;
    Row["PriName"] = empInfo.PriName;
    Row["PriAddress"] = empInfo.PriAddress;
    Row["PriPhone"] = empInfo.PriPhone;
    Row["SecName"] = empInfo.SecName;
    Row["SecAddress"] = empInfo.SecAddress;
    Row["SecPhone"] = empInfo.SecPhone;
    Row["AltName1"] = empInfo.AltName1;
    Row["AltAddress1"] = empInfo.AltAddress1;
    Row["AltPhone1"] = empInfo.AltPhone1;
    Row["AltName2"] = empInfo.AltName2;
    Row["AltAddress2"] = empInfo.AltAddress2;
    Row["AltPhone2"] = empInfo.AltPhone2;

    dataAdapter.Update(ds);

    }

    Tuesday, August 7, 2018 5:22 PM

All replies

  • User-821857111 posted

    Don't use DataAdapters and DataSets to perform updates against the database. They are very inefficient especially in a web application. Use a SQL UPDATE command instead with DbCommand.ExecuteNonQuery:

    string strUpdateCommand = "UPDATE tblEmployer SET Name = @Name, DBA=@DBA, PriName=@PriName, PriAddress=@PriAddress,PriPhone=@PriPhone,SecName=@SecName,SecAddress=@SecAddress,SecPhone=@SecPhone, AltName1=@AltName1, AltAddress1=@AltAddress1, AltPhone1=@AltPhone1,AltName2 = @AltName2, AltAddress2 = @AltAddress2, AltPhone2 = @AltPhone2 where EAN = @EAN";
    SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection);
    
    updateCommand.Parameters.AddWithValue("@DBA", empInfo.DBA); 
    updateCommand.Parameters.AddWithValue("@PriName", empInfo.PriName;
    updateCommand.Parameters.AddWithValue("@PriAddress",  empInfo.PriAddress;
    updateCommand.Parameters.AddWithValue("@PriPhone",  empInfo.PriPhone;
    updateCommand.Parameters.AddWithValue("@SecName", empInfo.SecName;
    updateCommand.Parameters.AddWithValue("@SecAddress", empInfo.SecAddress;
    updateCommand.Parameters.AddWithValue("@SecPhone", empInfo.SecPhone;
    updateCommand.Parameters.AddWithValue("@AltName1", empInfo.AltName1;
    updateCommand.Parameters.AddWithValue("@AltAddress1", empInfo.AltAddress1;
    updateCommand.Parameters.AddWithValue("@AltPhone1", empInfo.AltPhone1;
    updateCommand.Parameters.AddWithValue("@AltName2",  empInfo.AltName2;
    updateCommand.Parameters.AddWithValue("@AltAddress2",  empInfo.AltAddress2;
    updateCommand.Parameters.AddWithValue("@AltPhone2", empInfo.AltPhone2;
    updateCommand.Parameters.AddWithValue("@EAN", .....);
    updateCommand.ExecuteNonQuery();

    You need to provide a value for the @EAN parameter.

    Tuesday, August 7, 2018 7:44 PM