473,883 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DataSet Not Updating

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;In tegrated
Security=SSPI;I nitial Catalog=Northwi nd;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
nwindConn.Open( )

Dim SqlInsertComman d1 As SqlCommand = New SqlCommand()

SqlInsertComman d1.CommandText = "INSERT INTO Categories
(CategoryName, Description) VALUES (@CategoryName,
@Description)"
SqlInsertComman d1.Connection = nwindConn
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Category Name",
System.Data.Sql DbType.NVarChar , 15, "CategoryName") )
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Descript ion",
System.Data.Sql DbType.NVarChar , 1073741823, "Descriptio n"))

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

Dim dtTable As DataTable
Dim drRow As DataRow

dtTable = nwindDS.Tables( "Categories ")
drRow = dtTable.NewRow( )
drRow("Category Name") = "New Category Name"
drRow("Descript ion") = "New Description"
dtTable.Rows.Ad d(drRow)

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

Nov 20 '05 #1
11 8420
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.AcceptC hanges()


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


Nov 20 '05 #2
Tomp,
dtTable.Rows.Ad d(drRow)
nwindDS.AcceptC hanges()
nwindDA.Update( nwindDS, "Categories ")
In addition to the Cor's comments.

Calling "dtTable.Rows.A dd(drRow)" marks the row as DataRowState.Ad ded.
Calling DataSet, DataTable, DataRow AcceptChanges marks the respective rows
as processed (DataRowState.U nChanged).

DataAdapter.Upd ate only processes rows that have DataRow.RowStat e of
DataRowState.Ad ded, DataRowState.De leted, or DataRowState.Mo dified, 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.Upd ate will itself call DataRow.AcceptC hanges on each row
that it processes, so you don't need to call AcceptChanges after calling
DataAdapter.Upd ate 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

"tomp" <an*******@disc ussions.microso ft.com> wrote in message
news:00******** *************** *****@phx.gbl.. . 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;In tegrated
Security=SSPI;I nitial Catalog=Northwi nd;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
nwindConn.Open( )

Dim SqlInsertComman d1 As SqlCommand = New SqlCommand()

SqlInsertComman d1.CommandText = "INSERT INTO Categories
(CategoryName, Description) VALUES (@CategoryName,
@Description)"
SqlInsertComman d1.Connection = nwindConn
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Category Name",
System.Data.Sql DbType.NVarChar , 15, "CategoryName") )
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Descript ion",
System.Data.Sql DbType.NVarChar , 1073741823, "Descriptio n"))

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

Dim dtTable As DataTable
Dim drRow As DataRow

dtTable = nwindDS.Tables( "Categories ")
drRow = dtTable.NewRow( )
drRow("Category Name") = "New Category Name"
drRow("Descript ion") = "New Description"
dtTable.Rows.Ad d(drRow)

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

Nov 20 '05 #3
Cor
Hi Jay B,

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

Note: DataAdapter.Upd ate will itself call DataRow.AcceptC hanges on each row that it processes, so you don't need to call AcceptChanges after calling
DataAdapter.Upd ate 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
Nov 20 '05 #4
Cor,
See:
http://msdn.microsoft.com/library/de...pdatetopic.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" <no*@non.com> wrote in message
news:eQ******** *****@TK2MSFTNG P10.phx.gbl...
Hi Jay B,

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

Note: DataAdapter.Upd ate will itself call DataRow.AcceptC hanges on each row
that it processes, so you don't need to call AcceptChanges after calling
DataAdapter.Upd ate 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

Nov 20 '05 #5
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/de...terdataset.asp
Does not write what you say, but what I think you mix up.

But still I can be wrong.

Cor
Cor,
See:
http://msdn.microsoft.com/library/de...pdatetopic.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

Nov 20 '05 #6
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/de...dateTopic1.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" <no*@non.com> wrote in message
news:eV******** ********@tk2msf tngp13.phx.gbl. ..
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/de...terdataset.asp Does not write what you say, but what I think you mix up.

But still I can be wrong.

Cor
Cor,
See:

http://msdn.microsoft.com/library/de...pdatetopic.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


Nov 20 '05 #7
Cor
Hi Jay B,

Works exactly as described there.

:-)

Cor
Nov 20 '05 #8

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.Invalid OperationExcept ion' 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;In tegrated
Security=SSPI;I nitial Catalog=Northwi nd;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
nwindConn.Open( )

Dim SqlInsertComman d1 As SqlCommand = New
SqlCommand()
SqlInsertComman d1.CommandText = "INSERT Categories
(CategoryName, Description) VALUES (@CategoryName, " & _
" @Description); SELECT CategoryID, CategoryName,
Description, " & _
"FROM Categories WHERE (CategoryID = @@IDENTITY)"
SqlInsertComman d1.Connection = nwindConn
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Category Name",
System.Data.Sql DbType.NVarChar , 15, "CategoryName") )
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Descript ion",
System.Data.Sql DbType.NVarChar , 1073741823, "Descriptio n"))
'
Dim nwindDS As DataSet = New DataSet()
nwindDA.Fill(nw indDS, "Categories ")

Dim dtTable As DataTable
Dim drRow As DataRow
dtTable = nwindDS.Tables( "Categories ")
drRow = dtTable.NewRow( )
drRow("Category Name") = "New Category Name"
drRow("Descript ion") = "New Description"
dtTable.Rows.Ad d(drRow)

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

Nov 20 '05 #9
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;In tegrated
Security=SSPI;I nitial Catalog=Northwi nd;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
Dim nwindDS As DataSet = New DataSet()
nwindDA.Fill(nw indDS, "Categories ")
dim drRow as datarow = nwindDS.tables( "Categories").N ewRow()
drRow("Category Name") = "New Category Name"
drRow("Descript ion") = "New Description"
nwindDS.tables( "Categories").A dd(drRow)
dim cmd as new SQLcommandbuild er(nwindDA)
if nwindDS.haschan ges 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
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.Invalid OperationExcept ion' 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;In tegrated
Security=SSPI;I nitial Catalog=Northwi nd;")
Dim nwindDA As SqlDataAdapter = New SqlDataAdapter
("SELECT CategoryID, CategoryName, Description FROM
Categories", nwindConn)
nwindConn.Open( )

Dim SqlInsertComman d1 As SqlCommand = New
SqlCommand()
SqlInsertComman d1.CommandText = "INSERT Categories
(CategoryName, Description) VALUES (@CategoryName, " & _
" @Description); SELECT CategoryID, CategoryName,
Description, " & _
"FROM Categories WHERE (CategoryID = @@IDENTITY)"
SqlInsertComman d1.Connection = nwindConn
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Category Name",
System.Data.Sql DbType.NVarChar , 15, "CategoryName") )
SqlInsertComman d1.Parameters.A dd(New
System.Data.Sql Client.SqlParam eter("@Descript ion",
System.Data.Sql DbType.NVarChar , 1073741823, "Descriptio n"))
'
Dim nwindDS As DataSet = New DataSet()
nwindDA.Fill(nw indDS, "Categories ")

Dim dtTable As DataTable
Dim drRow As DataRow
dtTable = nwindDS.Tables( "Categories ")
drRow = dtTable.NewRow( )
drRow("Category Name") = "New Category Name"
drRow("Descript ion") = "New Description"
dtTable.Rows.Ad d(drRow)

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

Nov 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2630
by: Frnak McKenney | last post by:
I'm using an in-core DataSet as an image of my application's 'database' (a multi-table Access97 mdb file). Updates are made to the DataTables within the DataSet via forms with bound TextBoxes, then written to the database... or at least that's what's supposed to be happening. Unfortunately, I've discovered that while it appears that when I create a new record/row I'm successfully updating the Access database, once the Update is...
5
2046
by: junglist | last post by:
Hi guys, I've been trying to implement an editable datagrid and i have been succesful up to the point where i can update my datagrid row by row. However what used to happen was that once i updated one row, all of them were updated so i immediatelly figured out that i have to include the id of every entry in the update statement. This is where the problem is raised. My database is an Access database. The table i am updating contains a Date...
0
4922
by: Vijay Balki | last post by:
I am fetching data in DataSet - myDataSet, from a remote database using a Web Service in my VB.NET client..Once I fetch it I store the data in XML file (myXMLFile) using the WriteXML method of the myDataSet. The connection to the Web Service is closed at this point. I update this XML file in my application, and when I am ready to send the data back, I load the XML file back to a DataSet - mySendDataSet, using ReadXML method. Now I...
10
5699
by: jaYPee | last post by:
does anyone experienced slowness when updating a dataset using AcceptChanges? when calling this code it takes many seconds to update the database SqlDataAdapter1.Update(DsStudentCourse1) DsStudentCourse1.AcceptChanges() i'm also wondering because w/ out AcceptChanges the data is still save into the database and it is now faster.
4
2382
by: Geoff | last post by:
Hi I'm hoping somebody can help me with the following problem that has occurred to me. Suppose I have two tables in an SQL Server database. Let's call these tables A and B. Assume that A has two fields: a primary key and another holding a string. In table B there are three fields: a primary key, a foreign key (which links to the primary key in A) and other field holding a string.
2
13975
by: susan.f.barrett | last post by:
Hi, Despite me being able to type the following in to SQL Server and it updating 1 row: > updatestockcategory 1093, 839 In my code, it is not updating any rows. dataSet = new DataSet();
0
1710
by: OldStd | last post by:
Updating data using 2 data sets I am having some problems in updating the database using two datasets as suggested by someone. 1. Data is displayed in a data grid from a dataset generated using the IDE that pulls data from two tables using an . The idea is to display the associated with the field. Because an is used, the IDE couldn’t auto-generate the Insert command, update command, or Delete command. 2. As a result, it was suggested...
2
1523
by: Sheikko | last post by:
ADO.NET - Working with DataSet ========================= HI, I want to create an application that interact with a DB. I wont to use SQL statement, but I want to use DataSet. - I have created my application - From SERVER EXPLORER panel, I have added a connection to a database (MyDB) and the name of the dataset is "MyDBDataSet" . - From DATA SOURCE panel, I have added Tables that I want to use (Setting).
1
2378
by: jonbartlam | last post by:
Hi There I'm not sure what exactly is going wrong here. I'm writing an application that retreives a table from a database (tbl_internalfaults) and updates it. (Actually, just the status column will need updating with 'CLOSED' where necessary.) I have been trying to use the OLEDB command builder to build the statement to update the database however it is returning an error whenever I try to do this. I already have a dataset full of data, 1...
3
1580
by: Gun Slinger | last post by:
Hi guys, I have run into a little problem once again in my program. I have struggled with this for a while looking around for answers, and have made a make shift method to do a work around. But this will consume a lot of resources and is unnecessary i believe. My problem is this. I have a dataset which has several relations, i then also have enough datagridviews to display the levels of data. Now i have certain columns in the data that...
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11129
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10838
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9569
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7967
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7119
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5988
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4213
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.