SqlDataAdapter Update table with AutoIncrement column

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.
 
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()
 
Jawahar,
The Table I am trying to update has an autoincrement key so could this be
the issue?
Click to expand...

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
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();
}
 
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

Jawahar said:
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 said:
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
Click to expand...
Click to expand...
 
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.

Cor Ligthert said:
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

Jawahar said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Jawahar,

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

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

Cor

Jawahar said:
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.

Cor Ligthert said:
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

Jawahar said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top