DataSet Not Updating

[tomp]

Hi,

I am trying to insert a row into the Categories Table in
Nothwinds database in MSDE. I am able to manually insert
it using a query tool.

Using the Debugger, it appears the DataSet loads
correctly, the Data Row is populated correctly, and the
new row is added to the DataSet. But the database is not
updated. What am I missing?

Thanks in advance,

Tom

======

Sub Main()
Dim nwindConn As SqlConnection = New SqlConnection
("Data Source=COL-TP5170-LT1\VSdotNET;Integrated
Security=SSPI;Initial Catalog=Northwind;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
nwindConn.Open()

Dim SqlInsertCommand1 As SqlCommand = New SqlCommand()

SqlInsertCommand1.CommandText = "INSERT INTO Categories
(CategoryName, Description) VALUES (@CategoryName,
@Description)"
SqlInsertCommand1.Connection = nwindConn
SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CategoryName",
System.Data.SqlDbType.NVarChar, 15, "CategoryName"))
SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Description",
System.Data.SqlDbType.NVarChar, 1073741823, "Description"))

Dim nwindDS As DataSet = New DataSet()
nwindDA.Fill(nwindDS, "Categories")

Dim dtTable As DataTable
Dim drRow As DataRow

dtTable = nwindDS.Tables("Categories")
drRow = dtTable.NewRow()
drRow("CategoryName") = "New Category Name"
drRow("Description") = "New Description"
dtTable.Rows.Add(drRow)

nwindDS.AcceptChanges()
nwindDA.Update(nwindDS, "Categories")
nwindConn.Close()
End Sub

 

[Cor]

Hi Tom

Try this to change it as this (I did not see all of your code but this is
defenatly wrong)

nwindDA.Update(nwindDS, "Categories")
nwindDS.AcceptChanges()

The "accept changes" is one of the most misunderstanded methods I think.
It set all the rowstates in the dataset to updated.

I hope this helps,

cor

 

[Jay B. Harlow [MVP - Outlook]]

Tomp,

dtTable.Rows.Add(drRow)
nwindDS.AcceptChanges()
nwindDA.Update(nwindDS, "Categories")

In addition to the Cor's comments.

Calling "dtTable.Rows.Add(drRow)" marks the row as DataRowState.Added.
Calling DataSet, DataTable, DataRow AcceptChanges marks the respective rows
as processed (DataRowState.UnChanged).

DataAdapter.Update only processes rows that have DataRow.RowState of
DataRowState.Added, DataRowState.Deleted, or DataRowState.Modified, as that
is how it knows to use the InsertCommand, DeleteCommand, or UpdateCommand
respectively.

Hence calling Rows.Add followed by AcceptChanges causes nothing to
occur on your database.

Note: DataAdapter.Update will itself call DataRow.AcceptChanges on each row
that it processes, so you don't need to call AcceptChanges after calling
DataAdapter.Update either.

David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
covers this plus a plethora of other items about ADO.NET. I highly recommend
it as a good tutorial for ADO.NET & a good desk reference once you know
ADO.NET.

Hope this helps
Jay

 

[Cor]

Hi Jay B,

Of course I can be wrong, but I think you mix some thing up here.

Note: DataAdapter.Update will itself call DataRow.AcceptChanges on each row
that it processes, so you don't need to call AcceptChanges after calling
DataAdapter.Update either.

My expirience is else I and think you are mixing it up with the concurrency
states.

You know that I did tried it three times before I wrote this, but still I
can be wrong.

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,
See:
http://msdn.microsoft.com/library/d...emdatacommondbdataadapterclassupdatetopic.asp

The section close to the middle that starts "When using Update, the order of
execution is as follows".

Sceppa's book explains it more detail then the above statement.

Hope this helps
Jay

 

[Cor]

Hi Jay B,

I could not find that text. (I even have searched for "using" on that page)

Did you send maybe the wrong link?

Here is another page
http://msdn.microsoft.com/library/d...conupdatingdatabasewithdataadapterdataset.asp
Does not write what you say, but what I think you mix up.

But still I can be wrong.

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,
Sorry wrong URL, I changed pages in the browser, but the url did not change,
try this page (the page immediately after the one I sent before).

http://msdn.microsoft.com/library/d...mDataCommonDbDataAdapterClassUpdateTopic1.asp


Correct that page does not explicitly state that Update calls AcceptChanges,
nor do I see where it states (on that page) that you need to call
AcceptChanges. The examples do not include AcceptChanges.

The only reference to AcceptChanges is in the note about what happens when
AcceptChanges is called.

Also I cannot think of a case where I would NOT want AcceptChanges called
when I called Update, there may be a case, however I cannot think of one...

Hope this helps
Jay

 

[Cor]

Hi Jay B,

Works exactly as described there.



Cor

 

[Tomp]

Jay and Cor,

Thanks for the help! I think I have made some progress by
removing the AcceptChanges command altogether (I was
confused in your messages if I should remove or change the
order of the AcceptChanges or the Update. I concluded
that removing it seemed the most logical). Now it appears
that it is at least trying to process an Insert.

But, I am getting the following error:

"An unhandled exception of
type 'System.InvalidOperationException' occurred in
system.data.dll Additional information: Update requires a
valid InsertCommand when passed DataRow collection with
new rows."

Do you see anything wrong with the Insert? I basically
copy and pasted generated code for this fragment. Thanks
again!

Tom

====


Sub Main()
Dim nwindConn As SqlConnection = New SqlConnection
("Data Source=COL-TP5170-LT1\VSdotNET;Integrated
Security=SSPI;Initial Catalog=Northwind;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
nwindConn.Open()

Dim SqlInsertCommand1 As SqlCommand = New
SqlCommand()
SqlInsertCommand1.CommandText = "INSERT Categories
(CategoryName, Description) VALUES (@CategoryName," & _
" @Description); SELECT CategoryID, CategoryName,
Description, " & _
"FROM Categories WHERE (CategoryID = @@IDENTITY)"
SqlInsertCommand1.Connection = nwindConn
SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CategoryName",
System.Data.SqlDbType.NVarChar, 15, "CategoryName"))
SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Description",
System.Data.SqlDbType.NVarChar, 1073741823, "Description"))
'
Dim nwindDS As DataSet = New DataSet()
nwindDA.Fill(nwindDS, "Categories")

Dim dtTable As DataTable
Dim drRow As DataRow
dtTable = nwindDS.Tables("Categories")
drRow = dtTable.NewRow()
drRow("CategoryName") = "New Category Name"
drRow("Description") = "New Description"
dtTable.Rows.Add(drRow)

nwindDA.Update(nwindDS, "Categories")
nwindConn.Close()
End Sub

 

[Cor]

Hi Tom,

Read it to the end before you become afraid

If you do it nice you have to make an Insert, an Update, and a Delete
command.

That you can do using the designer that makes it and then copy and paste it.

I never do that.

I was changing little things, but after all I have changed all your code,
not because it was fault, but that was easier to show, look to it as an
alternative.

I typed it here not in the IDE so there can be little errors but basicly it
is OK
\\\
Sub Main()
Dim nwindConn As SqlConnection = New SqlConnection
("Data Source=COL-TP5170-LT1\VSdotNET;Integrated
Security=SSPI;Initial Catalog=Northwind;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
Dim nwindDS As DataSet = New DataSet()
nwindDA.Fill(nwindDS, "Categories")
dim drRow as datarow = nwindDS.tables("Categories").NewRow()
drRow("CategoryName") = "New Category Name"
drRow("Description") = "New Description"
nwindDS.tables("Categories").Add(drRow)
dim cmd as new SQLcommandbuilder(nwindDA)
if nwindDS.haschanges then
nwindDA.Update(nwindDS, "Categories")
end if
nwindCon.Close
end sub
///

I deleted the nwind.open (does the dataadapter) and changed some things to
add a row. Also added the commandbuilder, which makes all the commands from
the select string (but forget never the primary key column to have in the
select).

I have read in the newsgroups that people told that the commanbuilder is not
able to do real very complex select commands. (But I never use that, I bring
it into VB.net if it is complex, I hate SQL)

I added also the test for if the datase has changes (here always of course),
than it skips the update if that is not, little bit nicer.

I hope this helps?

Cor

 

[tomp]

Cor,

Thanks so much for taking the time to show me how to make
this work. I do appreciate the effort on your part. I
copied/pasted your code into my ide, made a few minor
syntax fixes and got the following error :

Unhandled Exception: System.Data.SqlClient.SqlException:
String or binary data would be truncated.
at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet, String srcTable)
at WindowsApplication1.Module1.Main() in C:\Documents
and Settings\TPhalen\My Documents\Visual Studio
Projects\WindowsApplication1\Module1.vb:line 44The
program '[1816] WindowsApplication1.exe' has exited with
code 0 (0x0).

Any ideas?

Tom

 

[tomp]

Cor,

Nevermind. I overlooked the obvious. The data I was
trying to put in a column was more charaters than allowed
in that column.

Tom