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

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

Guest

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();
 
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
 
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 said:
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 said:
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();
Click to expand...
Click to expand...
 
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
statementError:
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 said:
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 said:
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();
Click to expand...
Click to expand...
 
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 said:
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
statementError:
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 said:
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 said:
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();
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much. That makes a lot of sense. I will work on a update
statement for this. Thanks a lot, Alpha

Marina said:
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 said:
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");
Click to expand...
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 said:
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




:

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();
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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");
Click to expand...
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 said:
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




:

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();
Click to expand...
Click to expand...
Click to expand...
 
Thank you.

Ron said:
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 said:
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 said:
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"]
};


:

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




:

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();
Click to expand...
Click to expand...
Click to expand...
 
Back
Top