Why SqlDataAdapter doesn't save the changes I made in a cell to DB

[Alpha]

The string test = 'Exp'. I verified in Debug mode that test has 'Exp' in the
first line and the 2nd line of code. Showing that
dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper changed
text from null string to 'Exp'. However, the numOfRows shows 0 after
execution. I exit that form and re-enter and the 'Exp' is not showing on the
form nor the database. Can someone tell me what I'm doing wrong here?
Thanks, Alpha

dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] .ToString();
dsVehicle.AcceptChanges();
conVeh.Open();
int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
conVeh.Close();

[Peter Bromberg [C# MVP]]

This line:
dsVehicle.AcceptChanges();
sets the current RowState of all the rows to unchanged. So the dataAdapter
sees no changed or new rows, or deleted rows.
make the AcceptChanges call AFTER you have performed your update.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Alpha" wrote:

The string test = 'Exp'. I verified in Debug mode that test has 'Exp' in the
first line and the 2nd line of code. Showing that
dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper changed
text from null string to 'Exp'. However, the numOfRows shows 0 after
execution. I exit that form and re-enter and the 'Exp' is not showing on the
form nor the database. Can someone tell me what I'm doing wrong here?
Thanks, Alpha

dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] .ToString();
dsVehicle.AcceptChanges();
conVeh.Open();
int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
conVeh.Close();

[Marina]

Actually, there is no need to call AcceptChanges after the update. The
Update method will do this itself after it is done updating.

"Peter Bromberg [C# MVP]" <pb*******@yahoo.nospammin.com> wrote in message
news:45**********************************@microsof t.com...

This line:
dsVehicle.AcceptChanges();
sets the current RowState of all the rows to unchanged. So the dataAdapter
sees no changed or new rows, or deleted rows.
make the AcceptChanges call AFTER you have performed your update.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Alpha" wrote:

The string test = 'Exp'. I verified in Debug mode that test has 'Exp' in
the
first line and the 2nd line of code. Showing that
dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper
changed
text from null string to 'Exp'. However, the numOfRows shows 0 after
execution. I exit that form and re-enter and the 'Exp' is not showing on
the
form nor the database. Can someone tell me what I'm doing wrong here?
Thanks, Alpha

dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] .ToString();
dsVehicle.AcceptChanges();
conVeh.Open();
int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
conVeh.Close();

[Alpha]

Thank you. That works that the dataset is showing HasChanges to be true.
However, now I'm getting the following error when code execute the Update
statement

int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");

Error:
Dynamic SQL generation is not supported against multiple base tables.

Can you see whati I"m doing wrong here? Thanks, Alpha

My code for SQLCommandBuilder:

string strSel = "SELECT [VID], [SchID], [ScheduleName], [VName], [Make],
[Model], [Year], [VIN]," +
" [DMVLicense], [DMVExpirationDate], [DOTRegistration],
[DOTExpirationDate], [LastServiceDate]," +
" [LastServiceOdometer], [LastOdometerReading], [LastOdometerDate] " +
" FROM [VMS].[dbo].[VehDetail] left outer join [VMS].dbo.Schedule " +
" on [ExtSchID] = [SchID] order by [VName]";
sdaVehicle.SelectCommand = new SqlCommand(strSel, conVeh);
sqlCB = new SqlCommandBuilder(sdaVehicle);
conVeh.Open();
sdaVehicle.Fill(dsVehicle, "VehDetail");
conVeh.Close();

dsVehicle.Tables["VehDetail"].PrimaryKey =
new DataColumn[]
{
dsVehicle.Tables["VehDetail"].Columns["VID"]
};
"Peter Bromberg [C# MVP]" wrote:
This line:
dsVehicle.AcceptChanges();
sets the current RowState of all the rows to unchanged. So the dataAdapter
sees no changed or new rows, or deleted rows.
make the AcceptChanges call AFTER you have performed your update.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Alpha" wrote:

The string test = 'Exp'. I verified in Debug mode that test has 'Exp' in the
first line and the 2nd line of code. Showing that
dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper changed
text from null string to 'Exp'. However, the numOfRows shows 0 after
execution. I exit that form and re-enter and the 'Exp' is not showing on the
form nor the database. Can someone tell me what I'm doing wrong here?
Thanks, Alpha

dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] .ToString();
dsVehicle.AcceptChanges();
conVeh.Open();
int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
conVeh.Close();

[Marina]

You can't update the table, if the query had a join in it.

You can circumvent this by either modifying the select command's sql before
doing the update, or by writing your own update/insert/delete commands.

"Alpha" <Al***@discussions.microsoft.com> wrote in message
news:1B**********************************@microsof t.com...

Thank you. That works that the dataset is showing HasChanges to be true.
However, now I'm getting the following error when code execute the Update
statement

> int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");

Error:
Dynamic SQL generation is not supported against multiple base tables.

Can you see whati I"m doing wrong here? Thanks, Alpha

My code for SQLCommandBuilder:

string strSel = "SELECT [VID], [SchID], [ScheduleName], [VName], [Make],
[Model], [Year], [VIN]," +
" [DMVLicense], [DMVExpirationDate], [DOTRegistration],
[DOTExpirationDate], [LastServiceDate]," +
" [LastServiceOdometer], [LastOdometerReading], [LastOdometerDate] " +
" FROM [VMS].[dbo].[VehDetail] left outer join [VMS].dbo.Schedule " +
" on [ExtSchID] = [SchID] order by [VName]";
sdaVehicle.SelectCommand = new SqlCommand(strSel, conVeh);
sqlCB = new SqlCommandBuilder(sdaVehicle);
conVeh.Open();
sdaVehicle.Fill(dsVehicle, "VehDetail");
conVeh.Close();

dsVehicle.Tables["VehDetail"].PrimaryKey =
new DataColumn[]
{
dsVehicle.Tables["VehDetail"].Columns["VID"]
};
"Peter Bromberg [C# MVP]" wrote:

This line:
dsVehicle.AcceptChanges();
sets the current RowState of all the rows to unchanged. So the
dataAdapter
sees no changed or new rows, or deleted rows.
make the AcceptChanges call AFTER you have performed your update.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Alpha" wrote:

> The string test = 'Exp'. I verified in Debug mode that test has 'Exp'
> in the
> first line and the 2nd line of code. Showing that
> dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper
> changed
> text from null string to 'Exp'. However, the numOfRows shows 0 after
> execution. I exit that form and re-enter and the 'Exp' is not showing
> on the
> form nor the database. Can someone tell me what I'm doing wrong here?
> Thanks, Alpha
>
> dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
> test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"]
> .ToString();
> dsVehicle.AcceptChanges();
> conVeh.Open();
> int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
> conVeh.Close();

[Alpha]

Thank you very much. That makes a lot of sense. I will work on a update
statement for this. Thanks a lot, Alpha

"Marina" wrote:

You can't update the table, if the query had a join in it.

You can circumvent this by either modifying the select command's sql before
doing the update, or by writing your own update/insert/delete commands.

"Alpha" <Al***@discussions.microsoft.com> wrote in message
news:1B**********************************@microsof t.com...

Thank you. That works that the dataset is showing HasChanges to be true.
However, now I'm getting the following error when code execute the Update
statement

> int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");

Error:
Dynamic SQL generation is not supported against multiple base tables.

Can you see whati I"m doing wrong here? Thanks, Alpha

My code for SQLCommandBuilder:

string strSel = "SELECT [VID], [SchID], [ScheduleName], [VName], [Make],
[Model], [Year], [VIN]," +
" [DMVLicense], [DMVExpirationDate], [DOTRegistration],
[DOTExpirationDate], [LastServiceDate]," +
" [LastServiceOdometer], [LastOdometerReading], [LastOdometerDate] " +
" FROM [VMS].[dbo].[VehDetail] left outer join [VMS].dbo.Schedule " +
" on [ExtSchID] = [SchID] order by [VName]";
sdaVehicle.SelectCommand = new SqlCommand(strSel, conVeh);
sqlCB = new SqlCommandBuilder(sdaVehicle);
conVeh.Open();
sdaVehicle.Fill(dsVehicle, "VehDetail");
conVeh.Close();

dsVehicle.Tables["VehDetail"].PrimaryKey =
new DataColumn[]
{
dsVehicle.Tables["VehDetail"].Columns["VID"]
};
"Peter Bromberg [C# MVP]" wrote:

This line:
dsVehicle.AcceptChanges();
sets the current RowState of all the rows to unchanged. So the
dataAdapter
sees no changed or new rows, or deleted rows.
make the AcceptChanges call AFTER you have performed your update.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Alpha" wrote:

> The string test = 'Exp'. I verified in Debug mode that test has 'Exp'
> in the
> first line and the 2nd line of code. Showing that
> dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper
> changed
> text from null string to 'Exp'. However, the numOfRows shows 0 after
> execution. I exit that form and re-enter and the 'Exp' is not showing
> on the
> form nor the database. Can someone tell me what I'm doing wrong here?
> Thanks, Alpha
>
> dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
> test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"]
> .ToString();
> dsVehicle.AcceptChanges();
> conVeh.Open();
> int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
> conVeh.Close();

[Ron]

You can't use a CommandBuilder when the Select query has table joins. It only
works for single tables, and quite frankly it is more efficient to write the
update command yourself even if you work with a single table.

"Marina" wrote:

You can't update the table, if the query had a join in it.

You can circumvent this by either modifying the select command's sql before
doing the update, or by writing your own update/insert/delete commands.

"Alpha" <Al***@discussions.microsoft.com> wrote in message
news:1B**********************************@microsof t.com...

Thank you. That works that the dataset is showing HasChanges to be true.
However, now I'm getting the following error when code execute the Update
statement

> int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");

Error:
Dynamic SQL generation is not supported against multiple base tables.

Can you see whati I"m doing wrong here? Thanks, Alpha

My code for SQLCommandBuilder:

string strSel = "SELECT [VID], [SchID], [ScheduleName], [VName], [Make],
[Model], [Year], [VIN]," +
" [DMVLicense], [DMVExpirationDate], [DOTRegistration],
[DOTExpirationDate], [LastServiceDate]," +
" [LastServiceOdometer], [LastOdometerReading], [LastOdometerDate] " +
" FROM [VMS].[dbo].[VehDetail] left outer join [VMS].dbo.Schedule " +
" on [ExtSchID] = [SchID] order by [VName]";
sdaVehicle.SelectCommand = new SqlCommand(strSel, conVeh);
sqlCB = new SqlCommandBuilder(sdaVehicle);
conVeh.Open();
sdaVehicle.Fill(dsVehicle, "VehDetail");
conVeh.Close();

dsVehicle.Tables["VehDetail"].PrimaryKey =
new DataColumn[]
{
dsVehicle.Tables["VehDetail"].Columns["VID"]
};
"Peter Bromberg [C# MVP]" wrote:

This line:
dsVehicle.AcceptChanges();
sets the current RowState of all the rows to unchanged. So the
dataAdapter
sees no changed or new rows, or deleted rows.
make the AcceptChanges call AFTER you have performed your update.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Alpha" wrote:

> The string test = 'Exp'. I verified in Debug mode that test has 'Exp'
> in the
> first line and the 2nd line of code. Showing that
> dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper
> changed
> text from null string to 'Exp'. However, the numOfRows shows 0 after
> execution. I exit that form and re-enter and the 'Exp' is not showing
> on the
> form nor the database. Can someone tell me what I'm doing wrong here?
> Thanks, Alpha
>
> dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
> test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"]
> .ToString();
> dsVehicle.AcceptChanges();
> conVeh.Open();
> int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
> conVeh.Close();

[Alpha]

Thank you.

"Ron" wrote:

You can't use a CommandBuilder when the Select query has table joins. It only
works for single tables, and quite frankly it is more efficient to write the
update command yourself even if you work with a single table.

"Marina" wrote:

You can't update the table, if the query had a join in it.

You can circumvent this by either modifying the select command's sql before
doing the update, or by writing your own update/insert/delete commands.

"Alpha" <Al***@discussions.microsoft.com> wrote in message
news:1B**********************************@microsof t.com...

Thank you. That works that the dataset is showing HasChanges to be true.
However, now I'm getting the following error when code execute the Update
statement
> > int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
Error:
Dynamic SQL generation is not supported against multiple base tables.

Can you see whati I"m doing wrong here? Thanks, Alpha

My code for SQLCommandBuilder:

string strSel = "SELECT [VID], [SchID], [ScheduleName], [VName], [Make],
[Model], [Year], [VIN]," +
" [DMVLicense], [DMVExpirationDate], [DOTRegistration],
[DOTExpirationDate], [LastServiceDate]," +
" [LastServiceOdometer], [LastOdometerReading], [LastOdometerDate] " +
" FROM [VMS].[dbo].[VehDetail] left outer join [VMS].dbo.Schedule " +
" on [ExtSchID] = [SchID] order by [VName]";
sdaVehicle.SelectCommand = new SqlCommand(strSel, conVeh);
sqlCB = new SqlCommandBuilder(sdaVehicle);
conVeh.Open();
sdaVehicle.Fill(dsVehicle, "VehDetail");
conVeh.Close();

dsVehicle.Tables["VehDetail"].PrimaryKey =
new DataColumn[]
{
dsVehicle.Tables["VehDetail"].Columns["VID"]
};
"Peter Bromberg [C# MVP]" wrote:

> This line:
> dsVehicle.AcceptChanges();
> sets the current RowState of all the rows to unchanged. So the
> dataAdapter
> sees no changed or new rows, or deleted rows.
> make the AcceptChanges call AFTER you have performed your update.
> Peter
> --
> Co-founder, Eggheadcafe.com developer portal:
> http://www.eggheadcafe.com
> UnBlog:
> http://petesbloggerama.blogspot.com
>
>
>
>
> "Alpha" wrote:
>
> > The string test = 'Exp'. I verified in Debug mode that test has 'Exp'
> > in the
> > first line and the 2nd line of code. Showing that
> > dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] has the proper
> > changed
> > text from null string to 'Exp'. However, the numOfRows shows 0 after
> > execution. I exit that form and re-enter and the 'Exp' is not showing
> > on the
> > form nor the database. Can someone tell me what I'm doing wrong here?
> > Thanks, Alpha
> >
> > dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"] = test;
> > test = dsVehicle.Tables["VehDetail"].Rows[prevRow]["Model"]
> > .ToString();
> > dsVehicle.AcceptChanges();
> > conVeh.Open();
> > int numOfRows = sdaVehicle.Update(dsVehicle, "VehDetail");
> > conVeh.Close();