help ! what's wrong with my da.Update()?

  • Thread starter Thread starter Topeak
  • Start date Start date
T

Topeak

I want to transfer data from sqlserver to local access database ,
and i use dataset.merger() method , but i find the accessDS's datarow state
keeps unchanged !
so i have to change some unimportant column's data,

but it throws exception "No value given for one or more required parameters.
"

The access table columns allow null ,except key column "unitcode"

Help Me!!!!

DataSet dsSqlServer=BasicConn.GetDataSet("select * from
hrmis_t_unit_base","unitBase",objConn);
OleDbCommand cmd=new OleDbCommand();


cmd.Connection=Form1.connAccess;


try
{
cmd.CommandText="delete from hrmis_t_unit_base";
cmd.ExecuteNonQuery();
OleDbDataAdapter myDA=new OleDbDataAdapter("select * from
hrmis_t_unit_base",Form1.connAccess);


OleDbCommandBuilder cb=new OleDbCommandBuilder(myDA);
// cb.QuotePrefix="[";
// cb.QuoteSuffix="]";
string strInsert=cb.GetInsertCommand().CommandText;
// strInsert="INSERT INTO hrmis_t_unit_base( unitcode , unitname ,
unitgrade , upunitcode , sequence, unitid , duty ) VALUES ( ? , ? , ? ,?, ?
, ? , ? )";
myDA.InsertCommand=new OleDbCommand(strInsert,Form1.connAccess);
myDA.UpdateCommand=new
OleDbCommand(cb.GetUpdateCommand().CommandText,Form1.connAccess);
myDA.DeleteCommand=new
OleDbCommand(cb.GetDeleteCommand().CommandText,Form1.connAccess);

myDA.MissingSchemaAction=MissingSchemaAction.AddWithKey;


DataSet accessDS=new DataSet();
myDA.Fill(accessDS,"unitBase");


accessDS.Merge(dsSqlServer,true);

for(int i=0;i<accessDS.Tables["unitBase"].Rows.Count;i++)
{
accessDS.Tables["unitBase"].Rows["sequence"]=0;

}



try
{
myDA.Update(accessDS.Tables["unitBase"]);
accessDS.Tables["unitBase"].AcceptChanges();
MessageBox.Show("Synchronise Access Table Success!");
}
catch(Exception exx)
{
throw new Exception(exx.Message ,exx);
}

}
catch(Exception EX)
{


MessageBox.Show("¸üÐÂÊý¾Ý¿â³ö´í!"+EX.Message );
}
finally
{

}
 
Topeak:

Where are you verifiying row state? If it's right before the Da.Udpate and
you aren't seeing any changes DataSet.HasChanges = False then the rowstate
is the problem. When you fill each of the datasets, you don't have
AcceptChangesDuringFill set to false, so the rowstate for each of the
datasets is unchanged. If that's the case, your update isn't going to work.
However, it looks like you are setting everything to 0 but that's not a
solution if you don't need to do it. Set the AcceptChangesDuringFill to
false for each dataset when you fill (it's a property of the dataadapter),
then the rowstate will be changed and your update will work. As far as the
other issue, it looks like some parameter isn't being mapped correctly. If
you have a value of nothing in the parameter, it's going to give you
problems. I'd set the ContinueUpdateOnError property of the dataadapter to
true and then see if anything is getting updated (otherwise everythign will
stop if you get an exception) ....that will help you narrow down the colum
where the problem is.
Topeak said:
I want to transfer data from sqlserver to local access database ,
and i use dataset.merger() method , but i find the accessDS's datarow state
keeps unchanged !
so i have to change some unimportant column's data,

but it throws exception "No value given for one or more required parameters.
"

The access table columns allow null ,except key column "unitcode"

Help Me!!!!

DataSet dsSqlServer=BasicConn.GetDataSet("select * from
hrmis_t_unit_base","unitBase",objConn);
OleDbCommand cmd=new OleDbCommand();


cmd.Connection=Form1.connAccess;


try
{
cmd.CommandText="delete from hrmis_t_unit_base";
cmd.ExecuteNonQuery();
OleDbDataAdapter myDA=new OleDbDataAdapter("select * from
hrmis_t_unit_base",Form1.connAccess);


OleDbCommandBuilder cb=new OleDbCommandBuilder(myDA);
// cb.QuotePrefix="[";
// cb.QuoteSuffix="]";
string strInsert=cb.GetInsertCommand().CommandText;
// strInsert="INSERT INTO hrmis_t_unit_base( unitcode , unitname ,
unitgrade , upunitcode , sequence, unitid , duty ) VALUES ( ? , ? , ? ,?, ?
, ? , ? )";
myDA.InsertCommand=new OleDbCommand(strInsert,Form1.connAccess);
myDA.UpdateCommand=new
OleDbCommand(cb.GetUpdateCommand().CommandText,Form1.connAccess);
myDA.DeleteCommand=new
OleDbCommand(cb.GetDeleteCommand().CommandText,Form1.connAccess);

myDA.MissingSchemaAction=MissingSchemaAction.AddWithKey;


DataSet accessDS=new DataSet();
myDA.Fill(accessDS,"unitBase");


accessDS.Merge(dsSqlServer,true);

for(int i=0;i<accessDS.Tables["unitBase"].Rows.Count;i++)
{
accessDS.Tables["unitBase"].Rows["sequence"]=0;

}



try
{
myDA.Update(accessDS.Tables["unitBase"]);
accessDS.Tables["unitBase"].AcceptChanges();
MessageBox.Show("Synchronise Access Table Success!");
}
catch(Exception exx)
{
throw new Exception(exx.Message ,exx);
}

}
catch(Exception EX)
{


MessageBox.Show("¸üÐÂÊý¾Ý¿â³ö´í!"+EX.Message );
}
finally
{

}
Click to expand...
 
after accessDS.Merge() method ,I add three if clauses ,and datum has
successfully merged to accessDS .
acessDs has no errors and no changes , RowState still keeps Unchanged !


myDA.AcceptChangesDuringFill=false; //// New
myDA.ContinueUpdateOnError=true; ////New

myDA.Fill(accessDS,"unitBase");

accessDS.Merge(dsSqlServer,true);

if(accessDS.HasErrors)
{
MessageBox.Show("Has Error"); /////No Error !!!
}

if(accessDS.HasChanges())
{
MessageBox.Show("Changed"); ////No Changes !!!!
}

if(accessDS.Tables["unitBase"].Rows.Count>0)
{

MessageBox.Show(accessDS.Tables["unitBase"].Rows[0].RowState.ToString());
////Show Unchanged !!!!!
}




William Ryan eMVP said:
Topeak:

Where are you verifiying row state? If it's right before the Da.Udpate and
you aren't seeing any changes DataSet.HasChanges = False then the rowstate
is the problem. When you fill each of the datasets, you don't have
AcceptChangesDuringFill set to false, so the rowstate for each of the
datasets is unchanged. If that's the case, your update isn't going to work.
However, it looks like you are setting everything to 0 but that's not a
solution if you don't need to do it. Set the AcceptChangesDuringFill to
false for each dataset when you fill (it's a property of the dataadapter),
then the rowstate will be changed and your update will work. As far as the
other issue, it looks like some parameter isn't being mapped correctly. If
you have a value of nothing in the parameter, it's going to give you
problems. I'd set the ContinueUpdateOnError property of the dataadapter to
true and then see if anything is getting updated (otherwise everythign will
stop if you get an exception) ....that will help you narrow down the colum
where the problem is.
Topeak said:
I want to transfer data from sqlserver to local access database ,
and i use dataset.merger() method , but i find the accessDS's datarow state
keeps unchanged !
so i have to change some unimportant column's data,

but it throws exception "No value given for one or more required parameters.
"

The access table columns allow null ,except key column "unitcode"

Help Me!!!!

DataSet dsSqlServer=BasicConn.GetDataSet("select * from
hrmis_t_unit_base","unitBase",objConn);
OleDbCommand cmd=new OleDbCommand();


cmd.Connection=Form1.connAccess;


try
{
cmd.CommandText="delete from hrmis_t_unit_base";
cmd.ExecuteNonQuery();
OleDbDataAdapter myDA=new OleDbDataAdapter("select * from
hrmis_t_unit_base",Form1.connAccess);


OleDbCommandBuilder cb=new OleDbCommandBuilder(myDA);
// cb.QuotePrefix="[";
// cb.QuoteSuffix="]";
string strInsert=cb.GetInsertCommand().CommandText;
// strInsert="INSERT INTO hrmis_t_unit_base( unitcode , unitname ,
unitgrade , upunitcode , sequence, unitid , duty ) VALUES ( ? , ? , ?
Click to expand...
,?,
?
, ? , ? )";
myDA.InsertCommand=new OleDbCommand(strInsert,Form1.connAccess);
myDA.UpdateCommand=new
OleDbCommand(cb.GetUpdateCommand().CommandText,Form1.connAccess);
myDA.DeleteCommand=new
OleDbCommand(cb.GetDeleteCommand().CommandText,Form1.connAccess);

myDA.MissingSchemaAction=MissingSchemaAction.AddWithKey;


DataSet accessDS=new DataSet();
myDA.Fill(accessDS,"unitBase");


accessDS.Merge(dsSqlServer,true);

for(int i=0;i<accessDS.Tables["unitBase"].Rows.Count;i++)
{
accessDS.Tables["unitBase"].Rows["sequence"]=0;

}



try
{
myDA.Update(accessDS.Tables["unitBase"]);
accessDS.Tables["unitBase"].AcceptChanges();
MessageBox.Show("Synchronise Access Table Success!");
}
catch(Exception exx)
{
throw new Exception(exx.Message ,exx);
}

}
catch(Exception EX)
{


MessageBox.Show("¸üÐÂÊý¾Ý¿â³ö´í!"+EX.Message );
}
finally
{

}
Click to expand...
Click to expand...
 
Topeak:

The AcceptChangesDuringFill needs to be for both dataadaapters. I'm
perplexed as to why you aren't showing any changes....put a
accessDS.HasChanges(); statement right after the fill and see what happens.
The reason for the ContinueUpdateOnError was to monitor what's happening on
the RowUpdating event...put some code in there to see if any of the rows are
changing. The problem still is the rowstate, right before calling update,
if you have no changes, nothing is going to happen
Topeak said:
after accessDS.Merge() method ,I add three if clauses ,and datum has
successfully merged to accessDS .
acessDs has no errors and no changes , RowState still keeps Unchanged !


myDA.AcceptChangesDuringFill=false; //// New
myDA.ContinueUpdateOnError=true; ////New

myDA.Fill(accessDS,"unitBase");

accessDS.Merge(dsSqlServer,true);

if(accessDS.HasErrors)
{
MessageBox.Show("Has Error"); /////No Error !!!
}

if(accessDS.HasChanges())
{
MessageBox.Show("Changed"); ////No Changes !!!!
}

if(accessDS.Tables["unitBase"].Rows.Count>0)
{

MessageBox.Show(accessDS.Tables["unitBase"].Rows[0].RowState.ToString());
////Show Unchanged !!!!!
}




William Ryan eMVP said:
Topeak:

Where are you verifiying row state? If it's right before the Da.Udpate and
you aren't seeing any changes DataSet.HasChanges = False then the rowstate
is the problem. When you fill each of the datasets, you don't have
AcceptChangesDuringFill set to false, so the rowstate for each of the
datasets is unchanged. If that's the case, your update isn't going to work.
However, it looks like you are setting everything to 0 but that's not a
solution if you don't need to do it. Set the AcceptChangesDuringFill to
false for each dataset when you fill (it's a property of the dataadapter),
then the rowstate will be changed and your update will work. As far as the
other issue, it looks like some parameter isn't being mapped correctly. If
you have a value of nothing in the parameter, it's going to give you
problems. I'd set the ContinueUpdateOnError property of the dataadapter to
true and then see if anything is getting updated (otherwise everythign will
stop if you get an exception) ....that will help you narrow down the colum
where the problem is.
Topeak said:
I want to transfer data from sqlserver to local access database ,
and i use dataset.merger() method , but i find the accessDS's datarow state
keeps unchanged !
so i have to change some unimportant column's data,

but it throws exception "No value given for one or more required parameters.
"

The access table columns allow null ,except key column "unitcode"

Help Me!!!!

DataSet dsSqlServer=BasicConn.GetDataSet("select * from
hrmis_t_unit_base","unitBase",objConn);
OleDbCommand cmd=new OleDbCommand();


cmd.Connection=Form1.connAccess;


try
{
cmd.CommandText="delete from hrmis_t_unit_base";
cmd.ExecuteNonQuery();
OleDbDataAdapter myDA=new OleDbDataAdapter("select * from
hrmis_t_unit_base",Form1.connAccess);


OleDbCommandBuilder cb=new OleDbCommandBuilder(myDA);
// cb.QuotePrefix="[";
// cb.QuoteSuffix="]";
string strInsert=cb.GetInsertCommand().CommandText;
// strInsert="INSERT INTO hrmis_t_unit_base( unitcode , unitname ,
unitgrade , upunitcode , sequence, unitid , duty ) VALUES ( ? , ? , ?
Click to expand...
,?,
?
, ? , ? )";
myDA.InsertCommand=new OleDbCommand(strInsert,Form1.connAccess);
myDA.UpdateCommand=new
OleDbCommand(cb.GetUpdateCommand().CommandText,Form1.connAccess);
myDA.DeleteCommand=new
OleDbCommand(cb.GetDeleteCommand().CommandText,Form1.connAccess);

myDA.MissingSchemaAction=MissingSchemaAction.AddWithKey;


DataSet accessDS=new DataSet();
myDA.Fill(accessDS,"unitBase");


accessDS.Merge(dsSqlServer,true);

for(int i=0;i<accessDS.Tables["unitBase"].Rows.Count;i++)
{
accessDS.Tables["unitBase"].Rows["sequence"]=0;

}



try
{
myDA.Update(accessDS.Tables["unitBase"]);
accessDS.Tables["unitBase"].AcceptChanges();
MessageBox.Show("Synchronise Access Table Success!");
}
catch(Exception exx)
{
throw new Exception(exx.Message ,exx);
}

}
catch(Exception EX)
{


MessageBox.Show("¸üÐÂÊý¾Ý¿â³ö´í!"+EX.Message );
}
finally
{

}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top