OleDbDataAdapter.Update and DBase IV

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

I have written my own DataAdapter for my DBase IV file(s). The
Insert, Update and Delete calls all report success when I call
Update(...) on the DataAdapter. Unfortunately, when I recall the
data, only the Inserts and Deletes actually worked. For some reason,
the UpdateCommand is returning the correct number of records that were
affected according to the Update call:

nUpdates=daATBD.Update(workTable.Select(null,null,DataViewRowState.ModifiedCurrent));

but the changes are just not there...

I've tried everything that I can think of, including doing an combo
(Delete/Insert) for every Update, but that isn't going to work
either...

What gives?

Thanks in advance,
Mark Feferman
(e-mail address removed)
 
Hi mark,

How does your Update commad look like?
How does your workTable look like?
 
Hi Miha,
Thanks for the response...I was beginning to worry. <g>

Anyway, below is my UpdateCommand:


daATBD.UpdateCommand = new OleDbCommand("UPDATE ATBD SET
SCHOOLNUM=@SCHOOLNUM, TABLETYPE=@TABLETYPE, TABLEDESC=@TABLEDESC,
CODEHEAD=@CODEHEAD, DESCHEAD=@DESCHEAD, CODELENGTH=@CODELENGTH,
DESCLENGTH=@DESCLENGTH, SECURITY=@SECURITY,
FIELDTYPE=@FIELDTYPE,TABLEGRP=@TABLEGRP WHERE
TABLETYPE=@TABLETYPE",cn);

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@SCHOOLNUM", OleDbType.VarChar));
Param.SourceColumn = "SCHOOLNUM";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLETYPE", OleDbType.VarChar));
Param.SourceColumn = "TABLETYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEDESC", OleDbType.VarChar));
Param.SourceColumn = "TABLEDESC";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODEHEAD", OleDbType.VarChar));
Param.SourceColumn = "CODEHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCHEAD", OleDbType.VarChar));
Param.SourceColumn = "DESCHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODELENGTH", OleDbType.Numeric));
Param.SourceColumn = "CODELENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCLENGTH", OleDbType.Numeric));
Param.SourceColumn = "DESCLENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@security", OleDbType.VarChar));
Param.SourceColumn = "SECURITY";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@FIELDTYPE", OleDbType.Numeric));
Param.SourceColumn = "FIELDTYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEGRP", OleDbType.VarChar));
Param.SourceColumn = "TABLEGRP";
Param.SourceVersion = DataRowVersion.Original;

// My work table
DataTable workTable=myDataSet.Tables[MyTable];

// doing the following actually shows me that I have modified rows...
DataRow[] drs = workTable.Select(null,null,DataViewRowState.ModifiedCurrent);
for(int i=0;i<foundRows.Length;++i)
{
DataRow dr=(DataRow)foundRows;
string original=dr["TABLEDESC",DataRowVersion.Original].ToString();
string current=dr["TABLEDESC",DataRowVersion.Current].ToString();
}






Miha Markic said:
Hi mark,

How does your Update commad look like?
How does your workTable look like?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

mark said:
I have written my own DataAdapter for my DBase IV file(s). The
Insert, Update and Delete calls all report success when I call
Update(...) on the DataAdapter. Unfortunately, when I recall the
data, only the Inserts and Deletes actually worked. For some reason,
the UpdateCommand is returning the correct number of records that were
affected according to the Update call:

nUpdates=daATBD.Update(workTable.Select(null,null,DataViewRowState.ModifiedC
urrent));

but the changes are just not there...

I've tried everything that I can think of, including doing an combo
(Delete/Insert) for every Update, but that isn't going to work
either...

What gives?

Thanks in advance,
Mark Feferman
(e-mail address removed)
Click to expand...
Click to expand...
 
On 27 Feb 2004 13:34:16 -0800, (e-mail address removed) (mark) wrote:

¤ I have written my own DataAdapter for my DBase IV file(s). The
¤ Insert, Update and Delete calls all report success when I call
¤ Update(...) on the DataAdapter. Unfortunately, when I recall the
¤ data, only the Inserts and Deletes actually worked. For some reason,
¤ the UpdateCommand is returning the correct number of records that were
¤ affected according to the Update call:
¤
¤ nUpdates=daATBD.Update(workTable.Select(null,null,DataViewRowState.ModifiedCurrent));
¤
¤ but the changes are just not there...
¤
¤ I've tried everything that I can think of, including doing an combo
¤ (Delete/Insert) for every Update, but that isn't going to work
¤ either...
¤
¤ What gives?

Might help to know what dBase driver you are using. Also, can we assume that your dBase files
(tables) have primary key indexes?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hi mark,

I think that your problem is that you have @tabletype defined only once, so
the second one is always null.
BTW you might avoid changing TABLETYPE as it is unique?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


mark said:
Hi Miha,
Thanks for the response...I was beginning to worry. <g>

Anyway, below is my UpdateCommand:


daATBD.UpdateCommand = new OleDbCommand("UPDATE ATBD SET
SCHOOLNUM=@SCHOOLNUM, TABLETYPE=@TABLETYPE, TABLEDESC=@TABLEDESC,
CODEHEAD=@CODEHEAD, DESCHEAD=@DESCHEAD, CODELENGTH=@CODELENGTH,
DESCLENGTH=@DESCLENGTH, SECURITY=@SECURITY,
FIELDTYPE=@FIELDTYPE,TABLEGRP=@TABLEGRP WHERE
TABLETYPE=@TABLETYPE",cn);

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@SCHOOLNUM", OleDbType.VarChar));
Param.SourceColumn = "SCHOOLNUM";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLETYPE", OleDbType.VarChar));
Param.SourceColumn = "TABLETYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEDESC", OleDbType.VarChar));
Param.SourceColumn = "TABLEDESC";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODEHEAD", OleDbType.VarChar));
Param.SourceColumn = "CODEHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCHEAD", OleDbType.VarChar));
Param.SourceColumn = "DESCHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODELENGTH", OleDbType.Numeric));
Param.SourceColumn = "CODELENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCLENGTH", OleDbType.Numeric));
Param.SourceColumn = "DESCLENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@SECURITY", OleDbType.VarChar));
Param.SourceColumn = "SECURITY";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@FIELDTYPE", OleDbType.Numeric));
Param.SourceColumn = "FIELDTYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEGRP", OleDbType.VarChar));
Param.SourceColumn = "TABLEGRP";
Param.SourceVersion = DataRowVersion.Original;

// My work table
DataTable workTable=myDataSet.Tables[MyTable];

// doing the following actually shows me that I have modified rows...
DataRow[] drs = workTable.Select(null,null,DataViewRowState.ModifiedCurrent);
for(int i=0;i<foundRows.Length;++i)
{
DataRow dr=(DataRow)foundRows;
string original=dr["TABLEDESC",DataRowVersion.Original].ToString();
string current=dr["TABLEDESC",DataRowVersion.Current].ToString();
}






"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi mark,

How does your Update commad look like?
How does your workTable look like?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

mark said:
I have written my own DataAdapter for my DBase IV file(s). The
Insert, Update and Delete calls all report success when I call
Update(...) on the DataAdapter. Unfortunately, when I recall the
data, only the Inserts and Deletes actually worked. For some reason,
the UpdateCommand is returning the correct number of records that were
affected according to the Update call:
Click to expand...
nUpdates=daATBD.Update(workTable.Select(null,null,DataViewRowState.ModifiedC
urrent));
but the changes are just not there...

I've tried everything that I can think of, including doing an combo
(Delete/Insert) for every Update, but that isn't going to work
either...

What gives?

Thanks in advance,
Mark Feferman
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure what you mean by "@TABLETYPE" defined only once...
I have it defined for each command where it is used(Insert, Update, Delete).
Please explain.

BTW, I really appreciate your help.

Sincerely,
Mark


Miha Markic said:
Hi mark,

I think that your problem is that you have @tabletype defined only once, so
the second one is always null.
BTW you might avoid changing TABLETYPE as it is unique?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


mark said:
Hi Miha,
Thanks for the response...I was beginning to worry. <g>

Anyway, below is my UpdateCommand:


daATBD.UpdateCommand = new OleDbCommand("UPDATE ATBD SET
SCHOOLNUM=@SCHOOLNUM, TABLETYPE=@TABLETYPE, TABLEDESC=@TABLEDESC,
CODEHEAD=@CODEHEAD, DESCHEAD=@DESCHEAD, CODELENGTH=@CODELENGTH,
DESCLENGTH=@DESCLENGTH, SECURITY=@SECURITY,
FIELDTYPE=@FIELDTYPE,TABLEGRP=@TABLEGRP WHERE
TABLETYPE=@TABLETYPE",cn);

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@SCHOOLNUM", OleDbType.VarChar));
Param.SourceColumn = "SCHOOLNUM";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLETYPE", OleDbType.VarChar));
Param.SourceColumn = "TABLETYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEDESC", OleDbType.VarChar));
Param.SourceColumn = "TABLEDESC";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODEHEAD", OleDbType.VarChar));
Param.SourceColumn = "CODEHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCHEAD", OleDbType.VarChar));
Param.SourceColumn = "DESCHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODELENGTH", OleDbType.Numeric));
Param.SourceColumn = "CODELENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCLENGTH", OleDbType.Numeric));
Param.SourceColumn = "DESCLENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@SECURITY", OleDbType.VarChar));
Param.SourceColumn = "SECURITY";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@FIELDTYPE", OleDbType.Numeric));
Param.SourceColumn = "FIELDTYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEGRP", OleDbType.VarChar));
Param.SourceColumn = "TABLEGRP";
Param.SourceVersion = DataRowVersion.Original;

// My work table
DataTable workTable=myDataSet.Tables[MyTable];

// doing the following actually shows me that I have modified rows...
DataRow[] drs = workTable.Select(null,null,DataViewRowState.ModifiedCurrent);
for(int i=0;i<foundRows.Length;++i)
{
DataRow dr=(DataRow)foundRows;
string original=dr["TABLEDESC",DataRowVersion.Original].ToString();
string current=dr["TABLEDESC",DataRowVersion.Current].ToString();
}






"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi mark,

How does your Update commad look like?
How does your workTable look like?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I have written my own DataAdapter for my DBase IV file(s). The
Insert, Update and Delete calls all report success when I call
Update(...) on the DataAdapter. Unfortunately, when I recall the
data, only the Inserts and Deletes actually worked. For some reason,
the UpdateCommand is returning the correct number of records that were
affected according to the Update call:


nUpdates=daATBD.Update(workTable.Select(null,null,DataViewRowState.ModifiedC
urrent));

but the changes are just not there...

I've tried everything that I can think of, including doing an combo
(Delete/Insert) for every Update, but that isn't going to work
either...

What gives?

Thanks in advance,
Mark Feferman
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Hi mark,

I did mean that @tabletype appears both in set and where part, so you have
two instances of it.
Thus, you have to add it twice to collection.
You should also name them differently: @tabletype for set part and
@original_tabletype for where part.

HTH
--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

mark said:
I'm not sure what you mean by "@TABLETYPE" defined only once...
I have it defined for each command where it is used(Insert, Update, Delete).
Please explain.

BTW, I really appreciate your help.

Sincerely,
Mark


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi mark,

I think that your problem is that you have @tabletype defined only once, so
the second one is always null.
BTW you might avoid changing TABLETYPE as it is unique?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


mark said:
Hi Miha,
Thanks for the response...I was beginning to worry. <g>

Anyway, below is my UpdateCommand:


daATBD.UpdateCommand = new OleDbCommand("UPDATE ATBD SET
SCHOOLNUM=@SCHOOLNUM, TABLETYPE=@TABLETYPE, TABLEDESC=@TABLEDESC,
CODEHEAD=@CODEHEAD, DESCHEAD=@DESCHEAD, CODELENGTH=@CODELENGTH,
DESCLENGTH=@DESCLENGTH, SECURITY=@SECURITY,
FIELDTYPE=@FIELDTYPE,TABLEGRP=@TABLEGRP WHERE
TABLETYPE=@TABLETYPE",cn);

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@SCHOOLNUM", OleDbType.VarChar));
Param.SourceColumn = "SCHOOLNUM";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLETYPE", OleDbType.VarChar));
Param.SourceColumn = "TABLETYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEDESC", OleDbType.VarChar));
Param.SourceColumn = "TABLEDESC";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODEHEAD", OleDbType.VarChar));
Param.SourceColumn = "CODEHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCHEAD", OleDbType.VarChar));
Param.SourceColumn = "DESCHEAD";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@CODELENGTH", OleDbType.Numeric));
Param.SourceColumn = "CODELENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@DESCLENGTH", OleDbType.Numeric));
Param.SourceColumn = "DESCLENGTH";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@SECURITY", OleDbType.VarChar));
Param.SourceColumn = "SECURITY";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@FIELDTYPE", OleDbType.Numeric));
Param.SourceColumn = "FIELDTYPE";
Param.SourceVersion = DataRowVersion.Original;

Param = daATBD.UpdateCommand.Parameters.Add(new
OleDbParameter("@TABLEGRP", OleDbType.VarChar));
Param.SourceColumn = "TABLEGRP";
Param.SourceVersion = DataRowVersion.Original;

// My work table
DataTable workTable=myDataSet.Tables[MyTable];

// doing the following actually shows me that I have modified rows...
DataRow[] drs = workTable.Select(null,null,DataViewRowState.ModifiedCurrent);
for(int i=0;i<foundRows.Length;++i)
{
DataRow dr=(DataRow)foundRows;
string original=dr["TABLEDESC",DataRowVersion.Original].ToString();
string current=dr["TABLEDESC",DataRowVersion.Current].ToString();
}






"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Click to expand...

Hi mark,

How does your Update commad look like?
How does your workTable look like?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I have written my own DataAdapter for my DBase IV file(s). The
Insert, Update and Delete calls all report success when I call
Update(...) on the DataAdapter. Unfortunately, when I recall the
data, only the Inserts and Deletes actually worked. For some reason,
the UpdateCommand is returning the correct number of records that were
affected according to the Update call:
Click to expand...
nUpdates=daATBD.Update(workTable.Select(null,null,DataViewRowState.ModifiedC
urrent));

but the changes are just not there...

I've tried everything that I can think of, including doing an combo
(Delete/Insert) for every Update, but that isn't going to work
either...

What gives?

Thanks in advance,
Mark Feferman
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
On 1 Mar 2004 18:29:41 -0800, (e-mail address removed) (mark) wrote:

¤ I'm not sure what you mean by "@TABLETYPE" defined only once...
¤ I have it defined for each command where it is used(Insert, Update, Delete).
¤ Please explain.
¤

¤ > > daATBD.UpdateCommand = new OleDbCommand("UPDATE ATBD SET
¤ > > SCHOOLNUM=@SCHOOLNUM, TABLETYPE=@TABLETYPE, TABLEDESC=@TABLEDESC,
¤ > > CODEHEAD=@CODEHEAD, DESCHEAD=@DESCHEAD, CODELENGTH=@CODELENGTH,
¤ > > DESCLENGTH=@DESCLENGTH, SECURITY=@SECURITY,
¤ > > FIELDTYPE=@FIELDTYPE,TABLEGRP=@TABLEGRP WHERE
¤ > > TABLETYPE=@TABLETYPE",cn);
¤ > >

I think Miha may be saying the same thing here, but I don't see any reason to include the TABLETYPE
column in your Update. You're updating rows by a specified TABLETYPE (in your criteria) so why would
you then want to change the value of TABLETYPE for each row (unless there is something in your logic
I'm missing)?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Just wanted to let you all know that I fixed the problem. I was being
an idiot.
If you notice in the code I sent originally, I use the 'Original'
column as the source for the parameters....it should have been
Current. Totally my mistake and a big waste of time. Thanks for all
your input, however.

Sincerely,
Mark Feferman
 
Back
Top