SqlDataAdapter.Update doesn't delete, will however insert and update, what gives?

  • Thread starter Thread starter Andy Baldwin
  • Start date Start date
A

Andy Baldwin

Even though the Update, Insert and Select portions of my DataAdapter
function fine using Adapter.Update, the delete functionality never
works. I have tried multiple things and frankly I am lost at this
point. It will be a pretty handy dandy class when I am done, but I
need help to figure out this last problem.

I have a database class (a c# component class so that I could use the
designer for the datasets and adapters etc...). I am using MSDE (MS
Database Engine) on a win2k box. I can connect to it via access and
VS.Net fine, as well as through my code in the way that I can insert,
select and edit existing records through my dataset. In my following
code, you will see that there are three methods, all three of which
work with a minor exception. Both getDataGridData and addRecord work
consistantly, add record uses the Adapter.Update method successfully
(the select doesn't need to update anything). The last method,
updateDataset, works when I pass in a dataset that has records marked
with a rowstate of changed, but not delete (or remove). I must be
missing something silly here any help will be greatly appreciated, and
I apologize for my short naming conventions and long nested
statements!

using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;

namespace EMS {
public class DataBase : System.ComponentModel.Component {
private System.Data.SqlClient.SqlCommand InsertCommand;
private System.Data.SqlClient.SqlCommand DeleteCommand;
private System.Data.SqlClient.SqlCommand UpdateCommand;
private System.Data.SqlClient.SqlCommand SelectCommand;
private System.Data.SqlClient.SqlConnection Conn;
private System.Data.SqlClient.SqlDataAdapter Adapter;
public static bool insertCheckboxColumn;
private EMS.DataSet1 ds;
private System.ComponentModel.Container components = null;
public DataBase(System.ComponentModel.IContainer container) {
container.Add(this);
InitializeComponent();
}
public DataBase() {
InitializeComponent();
Conn.Open();
}
~DataBase() {
Conn.Close();
}

//Snipped the initialize component chunk, waaaaay to big to put it all
here. I
// am only putting in the delete command specific things

this.DeleteCommand = new System.Data.SqlClient.SqlCommand();
this.Adapter.DeleteCommand = this.DeleteCommand;
this.DeleteCommand.CommandText = "DELETE FROM Offenses WHERE
IP = @IP";
this.DeleteCommand.Connection = this.Conn;
this.DeleteCommand.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@IP",
System.Data.SqlDbType.VarChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "IP", System.Data.DataRowVersion.Original, null));


public static void addRecord(Record r) {
DataBase db = new DataBase();
DataRow dr;
db.Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
try {
db.Adapter.Fill(db.ds, "Offenses");
} catch (System.Exception e) {
System.Console.WriteLine(e.Message);
}
//get a new blank row to populate
dr = db.ds.Tables["Offenses"].NewRow();

dr["field1"] = r.field1;
dr["field2"] = r.field2;
//etc..
//etc...

db.ds.Tables["Records"].Rows.Add(dr);
db.Adapter.Update(db.ds,"Records");
}
public static DataSet getDataGridData() {
DataBase db = new DataBase();
DataSet temp = new DataSet();
try {
db.Adapter.Fill(temp, "Offenses");
} catch (System.Exception e) {
System.Console.WriteLine(e.Message);
}
if(insertCheckboxColumn) {
DataSet junk = new DataSet();
DataTable table = new DataTable("Offenses");
junk.Tables.Add(table);
DataColumn col = new DataColumn("Select", typeof(bool));
col.DefaultValue = false;
junk.Tables["Offenses"].Columns.Add(col);
int i=1;
db.Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
junk.Merge(temp);
temp = junk;
}
return temp;
}
public static void updateDataset(DataSet data) {
DataBase db = new DataBase();
data.Tables["Offenses"].Columns.Remove("Select");
db.Adapter.MissingSchemaAction=
ystem.Data.MissingSchemaAction.AddWithKey;
db.Adapter.Update(data,"Offenses");
db.Conn.Close();
}
}
}
 
Oops, sorry guys, needed to do a "dr.Delete()" instead of removing it
from the table object...

Thanks anyways!!

AB
 
Back
Top