Autonumber issue with Jet, ASP.Net, C#

  • Thread starter Thread starter Marty
  • Start date Start date
M

Marty

Please take a look at this code. When the delegate fires, the debug
statements show the correct values (when not commented out). However,
the fields are not getting updated in my database. I want to capture
the autonumber field when it is created and use it's new value as part
of the HREF field. As you can see, I have those coemmented out.

Any ideas?

Thanks,
Marty
(e-mail address removed)


// Create OleDbCommand for SELECT @@IDENTITY statement
public static OleDbCommand cmdGetIdentity;
// public ID
public static int pID;

public static void InsertMenuItem(int FamilyID, int L1, int L2, int
L3, int L4, int L5, int TreeLevel, string Category, string MyPath)
{
string MyString;
MyString = ConfigurationSettings.AppSettings["ConnString"] + "Data
Source=" + MyPath.Trim() + "nfw.mdb";
OleDbConnection conn = new OleDbConnection(MyString);
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from
tblMenu", conn);
string SQL = "INSERT INTO tblMenu (Family, Level1, Level2, Level3,
Level4, Level5, TreeLevel, Category) VALUES (@Fam, @L1, @L2, @L3, @L4,
@L5, @TreeLevel, @Category)";
OleDbCommand command = new OleDbCommand(SQL, conn);
conn.Open();
command.Parameters.Add(new OleDbParameter("@Fam", OleDbType.Integer,
0, "Family"));
command.Parameters.Add(new OleDbParameter("@L1", OleDbType.Integer, 0,
"Level1"));
command.Parameters.Add(new OleDbParameter("@L2", OleDbType.Integer, 0,
"Level2"));
command.Parameters.Add(new OleDbParameter("@L3", OleDbType.Integer, 0,
"Level3"));
command.Parameters.Add(new OleDbParameter("@L4", OleDbType.Integer, 0,
"Level4"));
command.Parameters.Add(new OleDbParameter("@L5", OleDbType.Integer, 0,
"Level5"));
command.Parameters.Add(new OleDbParameter("@TreeLevel",
OleDbType.Integer, 0, "TreeLevel"));
command.Parameters.Add(new OleDbParameter("@Category",
OleDbType.VarChar, 50, "Category"));
adapter.InsertCommand = command;

// Create a DataTable
DataTable datatable = new DataTable();
adapter.Fill(datatable);

// create data row
DataRow dr;

// add row to the table
dr = datatable.NewRow();
dr["Family"] = FamilyID;
dr["Level1"] = L1;
dr["Level2"] = L2;
dr["Level3"] = L3;
dr["Level4"] = L4;
dr["Level5"] = L5;
dr["TreeLevel"] = TreeLevel;
dr["Category"] = Category;
datatable.Rows.Add(dr);

cmdGetIdentity = new OleDbCommand("Select @@IDENTITY", conn);

// Delegate for Handling RowUpdated event
adapter.RowUpdated += new
OleDbRowUpdatedEventHandler(HandleRowUpdated);

// Update the Data
adapter.Update(datatable);

conn.Close();
}

private static void HandleRowUpdated(object sender,
OleDbRowUpdatedEventArgs e)
{
if (e.Status == UpdateStatus.Continue && e.StatementType ==
StatementType.Insert )
{
// Get the Identity column value
pID = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
e.Row["CategoryID"] = pID;
//e.Row["HREF"] = "DisplayImages.aspx?ID=" + pID;
//System.Diagnostics.Debug.WriteLine(e.Row["CategoryID"]);
//System.Diagnostics.Debug.WriteLine(e.Row["HREF"]);
e.Row.AcceptChanges();
}
}
 
Hi Marty,

I guess you've misunderstood the RowUpdated meaning.
It fires when the row has been *already* updated in database.
Further changes to DataRow will have no effect whatsoever on database.
If you want to modify its HREF column, you'll have to invoke an UPDATE
within RowUpdated event implementation.
And, probably you shouls use SCOPE_IDENTITY() rather then @@IDENTITY

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Marty said:
Please take a look at this code. When the delegate fires, the debug
statements show the correct values (when not commented out). However,
the fields are not getting updated in my database. I want to capture
the autonumber field when it is created and use it's new value as part
of the HREF field. As you can see, I have those coemmented out.

Any ideas?

Thanks,
Marty
(e-mail address removed)


// Create OleDbCommand for SELECT @@IDENTITY statement
public static OleDbCommand cmdGetIdentity;
// public ID
public static int pID;

public static void InsertMenuItem(int FamilyID, int L1, int L2, int
L3, int L4, int L5, int TreeLevel, string Category, string MyPath)
{
string MyString;
MyString = ConfigurationSettings.AppSettings["ConnString"] + "Data
Source=" + MyPath.Trim() + "nfw.mdb";
OleDbConnection conn = new OleDbConnection(MyString);
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from
tblMenu", conn);
string SQL = "INSERT INTO tblMenu (Family, Level1, Level2, Level3,
Level4, Level5, TreeLevel, Category) VALUES (@Fam, @L1, @L2, @L3, @L4,
@L5, @TreeLevel, @Category)";
OleDbCommand command = new OleDbCommand(SQL, conn);
conn.Open();
command.Parameters.Add(new OleDbParameter("@Fam", OleDbType.Integer,
0, "Family"));
command.Parameters.Add(new OleDbParameter("@L1", OleDbType.Integer, 0,
"Level1"));
command.Parameters.Add(new OleDbParameter("@L2", OleDbType.Integer, 0,
"Level2"));
command.Parameters.Add(new OleDbParameter("@L3", OleDbType.Integer, 0,
"Level3"));
command.Parameters.Add(new OleDbParameter("@L4", OleDbType.Integer, 0,
"Level4"));
command.Parameters.Add(new OleDbParameter("@L5", OleDbType.Integer, 0,
"Level5"));
command.Parameters.Add(new OleDbParameter("@TreeLevel",
OleDbType.Integer, 0, "TreeLevel"));
command.Parameters.Add(new OleDbParameter("@Category",
OleDbType.VarChar, 50, "Category"));
adapter.InsertCommand = command;

// Create a DataTable
DataTable datatable = new DataTable();
adapter.Fill(datatable);

// create data row
DataRow dr;

// add row to the table
dr = datatable.NewRow();
dr["Family"] = FamilyID;
dr["Level1"] = L1;
dr["Level2"] = L2;
dr["Level3"] = L3;
dr["Level4"] = L4;
dr["Level5"] = L5;
dr["TreeLevel"] = TreeLevel;
dr["Category"] = Category;
datatable.Rows.Add(dr);

cmdGetIdentity = new OleDbCommand("Select @@IDENTITY", conn);

// Delegate for Handling RowUpdated event
adapter.RowUpdated += new
OleDbRowUpdatedEventHandler(HandleRowUpdated);

// Update the Data
adapter.Update(datatable);

conn.Close();
}

private static void HandleRowUpdated(object sender,
OleDbRowUpdatedEventArgs e)
{
if (e.Status == UpdateStatus.Continue && e.StatementType ==
StatementType.Insert )
{
// Get the Identity column value
pID = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
e.Row["CategoryID"] = pID;
//e.Row["HREF"] = "DisplayImages.aspx?ID=" + pID;
//System.Diagnostics.Debug.WriteLine(e.Row["CategoryID"]);
//System.Diagnostics.Debug.WriteLine(e.Row["HREF"]);
e.Row.AcceptChanges();
}
}
Click to expand...
 
Back
Top