InsertCommand disappears!!!

[wylbur]

Hi,

I'm accessing a pair of databases with ASP.NET 2.0 (ADO),
and using stored procedures on the first database access.
SQL server 2005 is local: Client and server are on the same box.

The first access works OK - everything gets written and read
to where it's supposed to be.

On attempting to write to a pair of database tables
in the second database (second access attempt),
I get the following error:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Server Error in '/Webfolder01' Application.
--------------------------------------------------------------------------------

Update requires a valid InsertCommand when passed DataRow collection with new rows.
Description: An unhandled exception occurred during the execution of the current
web request. Please review the stack trace for more information about the error
and where it originated in the code.

Exception Details: System.InvalidOperationException: Update requires a valid
InsertCommand when passed DataRow collection with new rows.

Source Error:


Line 1115:
Line 1116: DB_adapter.Update (ctrl_set_table);
Line 1117:


Source File: i:\Virtual Webfolders\DBctrl.cs Line: 1116
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



I then started the CLR debugger, and began tracing the execution.

The chain of events went like this ...

1: A call to the function load_DB_struct_schema_to_datatable:
/* =============================================== */
/* =============================================== */
private bool load_DB_struct_schema_to_datatable ()
{
DataTable schema_datatable;

try
{
if (get_DB_adapter (actv_table))
{
DB_adapter.TableMappings.Add
(Get_struct_mapping);

ctrl_set_table
= new DataTable (actv_table.ADO_name);

schema_datatable
= DB_adapter.FillSchema
(ctrl_set_table, SchemaType.Mapped);

DB_adapter.Dispose ();
connctn_OK = true;
}
else connctn_OK = false;
}
catch
{ connctn_OK = false; }

return connctn_OK;
}
/* =============================================== */
/* =============================================== */


2: A call to the boolean function get_DB_adapter:
/* =============================================== */
/* =============================================== */
protected bool get_DB_adapter (DB_ref_class src_table)
{
bool adapter_created_ok;

try
{
DB_adapter
= new SqlDataAdapter ();

// build select and insert command
SqlCommand
selCmd = new SqlCommand
(actv_table.select_cmd, sql_connctn);
SqlCommand
insCmd = new SqlCommand
(src_table.insert_cmd, sql_connctn);

insCmd.Parameters.Add
("@unique_ref", SqlDbType.Int, 4, "DB_Unique_ref");
insCmd.Parameters.Add
("@ID_string", SqlDbType.VarChar, 80, "DB_ID_string");
insCmd.Parameters.Add
("@sequence", SqlDbType.Binary, 220, "DB_Sequence");

DB_adapter.SelectCommand = selCmd;
DB_adapter.InsertCommand = insCmd;

// sql_cmd_builder
// = new SqlCommandBuilder (DB_adapter);

adapter_created_ok = true;
}
catch
{ adapter_created_ok = false; }

return adapter_created_ok;
}
/* =============================================== */
/* =============================================== */


3: While stepping through this function, I stepped over
the assignment of the SelectCommand property:
DB_adapter.SelectCommand = selCmd;
... and I verified that this property was indeed assigned
(as I could see it in my local data window).


4: I THEN stepped over the assignment of the
InsertCommand property:
DB_adapter.InsertCommand = insCmd;

At this point, I noticed that the previous assignment
(DB_adapter.SelectCommand = selCmd;) had been inexplicably
reset to NULL.


5: I THEN stepped over the assignment of the
boolean switch:
adapter_created_ok = true;

At this point, I noticed that the previous assignment
(DB_adapter.InsertCommand = insCmd;) had ALSO been inexplicably
reset to NULL.

What is interesting to note is that no exception was thrown
at this point, as the "catch" block was never reached
- so a boolean value of "true" was returned.


6: Execution then returned to the calling function
(with a value of "true"), and I then stepped over
the next two statements without any problems:

DB_adapter.TableMappings.Add
(Get_struct_mapping);

ctrl_set_table
= new DataTable (actv_table.ADO_name);


7: I then stepped over the FillSchema method:
schema_datatable
= DB_adapter.FillSchema
(ctrl_set_table, SchemaType.Mapped);


At this point, an exception was thrown:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
+ $exception {System.InvalidOperationException:
The SelectCommand property has not been initialized before calling 'FillSchema'.
at System.Data.Common.DbDataAdapter.FillSchema(DataTable dataTable,
SchemaType schemaType, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillSchema(DataTable dataTable,
SchemaType schemaType)
at Database_control.DB_ctrl_class.load_DB_struct_schema_to_datatable()
in i:\Virtual Webfolders\CtrlInit\DBctrl.cs:line 330}
System.Exception {System.InvalidOperationException}
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


"The SelectCommand property has not been initialized before calling 'FillSchema'."

<sarcasm>
Well duh - I wonder why.
</sarcasm>


I'm hoping that someone smarter than me can tell me why those properties
are being magically reset to null.


PLEASE???

[wylbur]

I had to break this down - it was too big to post as one post.


The relevant source code follows:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

/* * * * * * * * * * * * * * * * * */
public class DB_ref_class
{
public string ADO_name, DB_name,
create_cmd, insert_cmd,
select_cmd;
/* . . . . . . . . . . . */

public string Set_with
{ set {
ADO_name = value;
DB_name = "dbo." + value;
} }
/* . . . . . . . . . . . */
}
/* * * * * * * * * * * * * * * * * */

protected DB_ref_class actv_table;


/* =============================================== */
/* CONSTRUCTOR */
/* =============================================== */

public DB_ctrl_class (sys_obj_class p_sys_obj)
{
connctn_OK = false;

actv_table = new DB_ref_class ();
actv_table.Set_with = "Ctrl_Init_Struct01";

actv_table.create_cmd
= create_table_cmd (actv_table.DB_name);

actv_table.insert_cmd
= insert_table_cmd (actv_table.DB_name);

actv_table.select_cmd
= select_table_cmd (actv_table.DB_name);
}
/* =============================================== */
/* =============================================== */

/* + + + + + + + + + + + */
private string create_table_cmd (string p_table_name)
{ return "CREATE TABLE " + p_table_name
+ "( DB_Unique_ref INTEGER IDENTITY PRIMARY KEY,"
+ " DB_ID_string VARCHAR(80) NOT NULL,"
+ " DB_Sequence VARBINARY(220))";
}
/* + + + + + + + + + + + */
private string insert_table_cmd (string p_table_name)
{ return "INSERT INTO " + p_table_name
+ " (DB_Unique_ref, DB_ID_string, DB_Sequence) "
+ " VALUES (@unique_ref, @ID_string, @sequence)";
}
/* + + + + + + + + + + + */
private string select_table_cmd (string p_table_name)
{ return " SELECT DB_Unique_ref, DB_ID_string, DB_Sequence "
+ "FROM " + p_table_name;
}
/* + + + + + + + + + + + */

/* =============================================== */
/* =============================================== */
private string [][] Get_struct_map_set
{ get {
return
new string [][]
{ new string [] { "DB_Unique_ref", "Unique_ref" },
new string [] { "DB_ID_string", "ID_string" },
new string [] { "DB_Sequence", "Sequence" } };
} }
/* =============================================== */
/* =============================================== */
private DataTableMapping Get_struct_mapping
{ get {
return
get_mapping (actv_table, Get_struct_map_set);
} }
/* =============================================== */
/* =============================================== */

/* =============================================== */
/* =============================================== */
protected bool get_DB_adapter (DB_ref_class src_table)
{
bool adapter_created_ok;

try
{
DB_adapter
= new SqlDataAdapter ();

// build select and insert command
SqlCommand
selCmd = new SqlCommand
(actv_table.select_cmd, sql_connctn);
SqlCommand
insCmd = new SqlCommand
(src_table.insert_cmd, sql_connctn);

insCmd.Parameters.Add
("@unique_ref", SqlDbType.Int, 4, "DB_Unique_ref");
insCmd.Parameters.Add
("@ID_string", SqlDbType.VarChar, 80, "DB_ID_string");
insCmd.Parameters.Add
("@sequence", SqlDbType.Binary, 220, "DB_Sequence");

DB_adapter.SelectCommand = selCmd;
DB_adapter.InsertCommand = insCmd;

// sql_cmd_builder
// = new SqlCommandBuilder (DB_adapter);

adapter_created_ok = true;
}
catch
{ adapter_created_ok = false; }

return adapter_created_ok;
}
/* =============================================== */
/* =============================================== */


/* =============================================== */
/* =============================================== */
private bool load_DB_struct_schema_to_datatable ()
{
DataTable schema_datatable;

try
{
if (get_DB_adapter (actv_table))
{
DB_adapter.TableMappings.Add
(Get_struct_mapping);

ctrl_set_table
= new DataTable (actv_table.ADO_name);

schema_datatable
= DB_adapter.FillSchema
(ctrl_set_table, SchemaType.Mapped);

DB_adapter.Dispose ();
connctn_OK = true;
}
else connctn_OK = false;
}
catch
{ connctn_OK = false; }

return connctn_OK;
}
/* =============================================== */
/* =============================================== */
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

THANKS AGAIN!!!