SqlDataAdapter Update table with AutoIncrement column

[Guest]

I have the following code

iAdapter = new SqlDataAdapter();
iUpdater = new SqlCommandBuilder(iAdapter);
dsROCData = new DataSet("ROC");
SetConnection("XXXXX"); --> set the connection
oCommROC = new SqlCommand();
oCommROC.Connection = oConn;
oCommROC.CommandType = CommandType.StoredProcedure;
oCommROC.CommandText = "GetROCFromIFMS";

iAdapter.SelectCommand = oCommROC;
ifmsAdapter.MissingSchemaAction = MissingSchemaAction.Add;
int I = iAdapter.Fill(dsROCData, "ROCTemplatetable");
DataRow[] dr = ds.Tables["ROC"].Select();
foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

if (dsROCData.HasChanges()
{
int j = iAdapter.Update(dsROCData,"ROCTemplatetable");
}
iAdapter.Dispose();

I keep getting an error:
"Concurrency violation: the UpdateCommand affected 0 records"
The Table I am trying to update has an autoincrement key so could this be
the issue?
Am I using this line of code correctly?

" ifmsAdapter.MissingSchemaAction = MissingSchemaAction.Add; "
Any help is appreciated
-Jawahar

 

[Guest]

Jawahar,

I cant answer your question but is it not as simple as configuring a dragged
down dataAdaptor for insert, update & delete based on the select statement
you give the adaptor and then just writing

SqlDataAdapter1.Update(DataSet11)

Apologies I couldn't be or more help.

 

[Guest]

I am not using a dragged down Adapter.
I am generating everything in code including new Adapter and setting is
source etc in code.
After I get the Dataset I then add some more rows then want push those rows
back to the table.

 

[Guest]

Again I apologise that this thread won't help with your issue but is your
understanding of the dragged down components that you write only the update
code as so:

Me.BindingContext(DataSet11).EndCurrentEdit()
SqlDataAdapter1.Update(DataSet11)
DataSet11.AcceptChanges()

 

[Cor Ligthert [MVP]]

Jawahar,

The Table I am trying to update has an autoincrement key so could this be
the issue?

I cannot see what is the rowstate of the row that you import.
..
http://msdn2.microsoft.com/en-us/library/system.data.datarowstate.aspx

It has of course to be the state added.

If you use datatable.loaddatarow than you can manage that better.

By the way why are you using
ifmsAdapter

I don't even see it declared.

All of your code can in my idea shorter but has sense, but that disposing
of the dataadapter is a little bit withouth sense.

I hope this helps,

Cor

 

[Guest]

Cor
Thanks for your suggestions this is my code in full, the ifmsAdapter is just
my Varaiable reference.
Do I need to check the rowstate. I am able to execute the code as fas as the
Update line then i get the Error:
"Concurrency violation: the UpdateCommand affected 0 records"

Code ----
ifmsAdapter = new SqlDataAdapter();
ifmsUpdater = new SqlCommandBuilder(ifmsAdapter);
dsROCData = new DataSet("ROC");

SetConnection("ConnectStringIFMS");
oCommROC = new SqlCommand();
try
{
oCommROC.Connection = oConn;
oCommROC.CommandType = CommandType.StoredProcedure;
oCommROC.CommandText = "GetROCFromIFMS";

TParams[] tParams = new TParams[1];
tParams[0].strParmName = "@PID";
tParams[0].objValue = strIFMSPID;
tParams[0].objPrmDir = ParameterDirection.Input;

PrepCommParams(ref oCommROC,ref tParams);
ifmsAdapter.SelectCommand = oCommROC;
ifmsAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
int I = ifmsAdapter.Fill(dsROCData, "ROCTemplatetable");

DataRow[] dr = ds.Tables["ROC"].Select();

foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

if (dsROCData.HasChanges())
{
int j = ifmsAdapter.Update(dsROCData,"ROCTemplatetable");
}
}
catch (Exception ex)
{
string strMsg = ex.Message.ToString();
}
finally
{
ifmsAdapter.Dispose();
oComm.Dispose();
oConn.Dispose();
}

 

[Cor Ligthert [MVP]]

DataRow[] dr = ds.Tables["ROC"].Select();

foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

I see coming from the air a table ds.Tables["ROC"].Select
which is set in a datarowcollection
Every row from that is placed in the dsROCDATA.Tables

There must be something in that table
The rows must have in my opinion the rowstate "added"

Cor

Cor
Thanks for your suggestions this is my code in full, the ifmsAdapter is
just
my Varaiable reference.
Do I need to check the rowstate. I am able to execute the code as fas as
the
Update line then i get the Error:
"Concurrency violation: the UpdateCommand affected 0 records"

Code ----
ifmsAdapter = new SqlDataAdapter();
ifmsUpdater = new SqlCommandBuilder(ifmsAdapter);
dsROCData = new DataSet("ROC");

SetConnection("ConnectStringIFMS");
oCommROC = new SqlCommand();
try
{
oCommROC.Connection = oConn;
oCommROC.CommandType = CommandType.StoredProcedure;
oCommROC.CommandText = "GetROCFromIFMS";

TParams[] tParams = new TParams[1];
tParams[0].strParmName = "@PID";
tParams[0].objValue = strIFMSPID;
tParams[0].objPrmDir = ParameterDirection.Input;

PrepCommParams(ref oCommROC,ref tParams);
ifmsAdapter.SelectCommand = oCommROC;
ifmsAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
int I = ifmsAdapter.Fill(dsROCData, "ROCTemplatetable");

DataRow[] dr = ds.Tables["ROC"].Select();

foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

if (dsROCData.HasChanges())
{
int j = ifmsAdapter.Update(dsROCData,"ROCTemplatetable");
}
}
catch (Exception ex)
{
string strMsg = ex.Message.ToString();
}
finally
{
ifmsAdapter.Dispose();
oComm.Dispose();
oConn.Dispose();
}

Jawahar,

I cannot see what is the rowstate of the row that you import.
..
http://msdn2.microsoft.com/en-us/library/system.data.datarowstate.aspx

It has of course to be the state added.

If you use datatable.loaddatarow than you can manage that better.

By the way why are you using
ifmsAdapter

I don't even see it declared.

All of your code can in my idea shorter but has sense, but that
disposing
of the dataadapter is a little bit withouth sense.

I hope this helps,

Cor

 

[Guest]

The ds is a data set that I pass into the Method I am working with. I am add
the rows from the ds to the new dataset dsROCData that i want to update to
the Database. I tried with
DataRow[] dr = ds.Tables["ROC"].Select("PID ='" + strIFMSPID +
"'","",System.Data.DataViewRowState.Added);

but this gave me the same "Concurrency violation: the UpdateCommand
affected 0 records" error.
I also added Table maping and still I get the error.

DataRow[] dr = ds.Tables["ROC"].Select();

foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

I see coming from the air a table ds.Tables["ROC"].Select
which is set in a datarowcollection
Every row from that is placed in the dsROCDATA.Tables

There must be something in that table
The rows must have in my opinion the rowstate "added"

Cor

Cor
Thanks for your suggestions this is my code in full, the ifmsAdapter is
just
my Varaiable reference.
Do I need to check the rowstate. I am able to execute the code as fas as
the
Update line then i get the Error:
"Concurrency violation: the UpdateCommand affected 0 records"

Code ----
ifmsAdapter = new SqlDataAdapter();
ifmsUpdater = new SqlCommandBuilder(ifmsAdapter);
dsROCData = new DataSet("ROC");

SetConnection("ConnectStringIFMS");
oCommROC = new SqlCommand();
try
{
oCommROC.Connection = oConn;
oCommROC.CommandType = CommandType.StoredProcedure;
oCommROC.CommandText = "GetROCFromIFMS";

TParams[] tParams = new TParams[1];
tParams[0].strParmName = "@PID";
tParams[0].objValue = strIFMSPID;
tParams[0].objPrmDir = ParameterDirection.Input;

PrepCommParams(ref oCommROC,ref tParams);
ifmsAdapter.SelectCommand = oCommROC;
ifmsAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
int I = ifmsAdapter.Fill(dsROCData, "ROCTemplatetable");

DataRow[] dr = ds.Tables["ROC"].Select();

foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

if (dsROCData.HasChanges())
{
int j = ifmsAdapter.Update(dsROCData,"ROCTemplatetable");
}
}
catch (Exception ex)
{
string strMsg = ex.Message.ToString();
}
finally
{
ifmsAdapter.Dispose();
oComm.Dispose();
oConn.Dispose();
}

Jawahar,
The Table I am trying to update has an autoincrement key so could this
be
the issue?

I cannot see what is the rowstate of the row that you import.
..
http://msdn2.microsoft.com/en-us/library/system.data.datarowstate.aspx

It has of course to be the state added.

If you use datatable.loaddatarow than you can manage that better.

By the way why are you using
ifmsAdapter

I don't even see it declared.

All of your code can in my idea shorter but has sense, but that
disposing
of the dataadapter is a little bit withouth sense.

I hope this helps,

Cor

 

[Cor Ligthert [MVP]]

Jawahar,

I see you create it new inside your procedure.
dsROCData = new DataSet("ROC");

SetConnection("ConnectStringIFMS");
oCommROC = new SqlCommand();
try

Cor

The ds is a data set that I pass into the Method I am working with. I am
add
the rows from the ds to the new dataset dsROCData that i want to update to
the Database. I tried with
DataRow[] dr = ds.Tables["ROC"].Select("PID ='" + strIFMSPID +
"'","",System.Data.DataViewRowState.Added);

but this gave me the same "Concurrency violation: the UpdateCommand
affected 0 records" error.
I also added Table maping and still I get the error.

DataRow[] dr = ds.Tables["ROC"].Select();

foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

I see coming from the air a table ds.Tables["ROC"].Select
which is set in a datarowcollection
Every row from that is placed in the dsROCDATA.Tables

There must be something in that table
The rows must have in my opinion the rowstate "added"

Cor

Cor
Thanks for your suggestions this is my code in full, the ifmsAdapter is
just
my Varaiable reference.
Do I need to check the rowstate. I am able to execute the code as fas
as
the
Update line then i get the Error:
"Concurrency violation: the UpdateCommand affected 0 records"

Code ----
ifmsAdapter = new SqlDataAdapter();
ifmsUpdater = new SqlCommandBuilder(ifmsAdapter);
dsROCData = new DataSet("ROC");

SetConnection("ConnectStringIFMS");
oCommROC = new SqlCommand();
try
{
oCommROC.Connection = oConn;
oCommROC.CommandType = CommandType.StoredProcedure;
oCommROC.CommandText = "GetROCFromIFMS";

TParams[] tParams = new TParams[1];
tParams[0].strParmName = "@PID";
tParams[0].objValue = strIFMSPID;
tParams[0].objPrmDir = ParameterDirection.Input;

PrepCommParams(ref oCommROC,ref tParams);
ifmsAdapter.SelectCommand = oCommROC;
ifmsAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
int I = ifmsAdapter.Fill(dsROCData, "ROCTemplatetable");

DataRow[] dr = ds.Tables["ROC"].Select();

foreach (DataRow d in dr)
{
dsROCData.Tables["ROCTemplatetable"].ImportRow(d);
}

if (dsROCData.HasChanges())
{
int j = ifmsAdapter.Update(dsROCData,"ROCTemplatetable");
}
}
catch (Exception ex)
{
string strMsg = ex.Message.ToString();
}
finally
{
ifmsAdapter.Dispose();
oComm.Dispose();
oConn.Dispose();
}

:

Jawahar,
The Table I am trying to update has an autoincrement key so could
this
be
the issue?

I cannot see what is the rowstate of the row that you import.
..
http://msdn2.microsoft.com/en-us/library/system.data.datarowstate.aspx

It has of course to be the state added.

If you use datatable.loaddatarow than you can manage that better.

By the way why are you using
ifmsAdapter

I don't even see it declared.

All of your code can in my idea shorter but has sense, but that
disposing
of the dataadapter is a little bit withouth sense.

I hope this helps,

Cor