Error inserting record

  • Thread starter Thread starter Carlos Hernandez
  • Start date Start date
C

Carlos Hernandez

I am using a dataset bound to a grid(component one flexgrid), by some
strange reason, when the record is inserted, I get a cannot insert null
exception from the database. Checking I discovered that the insertcommand
was changed in the Adapter.Update( Dataset ). Before calling thus funcion,
the inser command returned this:

INSERT INTO table_name ( @pkfield1, @field2, @field3) values ( @p1, @p2,
@p3)

this command is created bythe command builder, at the beginning. But after
calling the update function
i get this:

INSERT INTO table_name ( @field2, @field3) values ( @p1, @p2)

Why? It seems the adapter has erased my pkey, so it doens sent the insert
correctly. Before calling this function I cheked the rows, and everything is
correct, I have all the data complete. And the insert looks good. What
could this problem be?

Thanks in advance.
 
Hi Carlos,

Could you post exact steps to reproduce this? Which database and provider
are you using?
 
Hi, I am using the Sql .Net Provider, my database is Sql Server 2000.

This are the steps:

// CICommand is a class which return a SqlCommand, we use the IDbCommand
interfase
//so in the future we can use different providers.
IDbCommand masterCmd = CICommand.GetNewCommand();
IDbCommand detailCmd = CICommand.GetNewCommand();

masterCmd.Connection = cn;
masterCmd.Transaction = trans
detailCmd.Connection = cn;
detailCmd.Transaction = trans;

//CreateMasterSelect() returns a string with the schema of the master table
//example: select * from table where 1= 2
masterCmd.CommandText = CreateMasterSelect();
detailCmd.CommandText = CreateDetailSelect();

//we set the adapters
masterAdapter.SelectCommand = masterCmd;
detailAdapter.SelectCommand = detailCmd;

//we create the commandbuilders
masterCommandBuilder = new CICommandBuilder( masterAdapter );
detailCommandBuilder = new CICommandBuilder( detailAdapter );

//we set each adapter insert,delete and update command with its
CommandBuilder..
//it returns the string with parameters to use when updateing or inserting.
masterAdapter.InsertCommand = masterCommandBuilder.GetInsertCommand();
masterAdapter.DeleteCommand = masterCommandBuilder.GetDeleteCommand();
masterAdapter.UpdateCommand = masterCommandBuilder.GetUpdateCommand();
detailAdapter.InsertCommand = detailCommandBuilder.GetInsertCommand();
detailAdapter.DeleteCommand = detailCommandBuilder.GetDeleteCommand();
detailAdapter.UpdateCommand = detailCommandBuilder.GetUpdateCommand();


//creates the dataset
masterData = new DataSet();
detailData = new DataSet();

//we fill the dataset
masterAdapter.Fill( masterData );
detailAdapter.Fill( detailData );


///in this part the user is editing data
//we have a control the controls this process
//it have a textboxs which connect to the masterdatase, and a grid, which
bounds
//to the detaildata set. When the user saves, a transaction begins, and we
insert the records.

//Inserting record
//this process is done at the end, when the users saves the record


//update connection and transaction in the adapters
masterAdapter.SelectCommand.Connection = cn;
masterAdapter.SelectCommand.Transaction = trans;
masterAdapter.InsertCommand.Connection = cn;
masterAdapter.InsertCommand.Transaction = trans;
masterAdapter.DeleteCommand.Connection = cn;
masterAdapter.DeleteCommand.Transaction = trans;
masterAdapter.UpdateCommand.Connection = cn;
masterAdapter.UpdateCommand.Transaction = trans;

//detail part
detailAdapter.SelectCommand.Connection = cn;
detailAdapter.SelectCommand.Transaction = trans;
detailAdapter.InsertCommand.Connection = cn;
detailAdapter.InsertCommand.Transaction = trans;
detailAdapter.DeleteCommand.Connection = cn;
detailAdapter.DeleteCommand.Transaction = trans;
detailAdapter.UpdateCommand.Connection = cn;
detailAdapter.UpdateCommand.Transaction = trans;

if ( newRowTmp.RowState == DataRowState.Detached )
masterData.Tables[0].Rows.Add( this.newRowTmp );

masterAdapter.Update( masterData );

//set primaries keys of master to detail table
IComparable cmp;
foreach ( DictionaryEntry e in this.masterToDetailMappings )
{
foreach ( DataRow r in this.detailData.Tables[0].Rows )
{
if ( r.RowState != DataRowState.Deleted )
{
cmp = r[(string)e.Value] as IComparable;
if ( cmp == null )
{
r[ (string)e.Value ] = this.MasterData[ (string)e.Key ];
}
else
{
if ( cmp.CompareTo( this.MasterData[ (string)e.Key ] ) !=
0 )
{
r[ (string)e.Value ] = this.MasterData[
(string)e.Key ];
}
}
}
}
}

try
{
//this is were the error is produced
//this is the part where everything looks ok, and then changes on what I
told you before.
detailAdapter.Update( detailData );
}
catch
{
throw;
}



We are seriously going to change this class, and we are going to implement
our insert and update manually, if we dont found
a cause. One more thing. If we only add one record to the detail part, it
works fine.
The masterdata doesn't show any problems in any situation, it works fine,
the problem is only in the detaildata set.


Thaks in advance.
 
Back
Top