TableAdapter Update problem

Question

Hello, i have problem in my application.
I create dataset with single table. In MainForm i have 2 texboxes and update button.

This is code for update button:

[CODE]
Klient klient = new Klient();
klient.ShowDialog();

KlientDataSet dataSet = new KlientDataSet();

Serwis.KlientDataSet.KlienciRow klientRow = dataSet.Klienci.NewKlienciRow();
klientRow[0] = Guid.NewGuid();
klientRow[1] = klient.Imie;
klientRow[2] = klient.Nazwisko;

dataSet.Klienci.AddKlienciRow( klientRow );

int result = this.klienciTableAdapter1.Update( dataSet.Klienci );
[/CODE]

I got 1 in result variable so i think that database was updated. No my database was not updated.


I add another form to my app with droped table from tolbox. VS created textboxes and datanavigator. When im add newdata and click savedata my database not updating!

I reinstall vs and sql but my database still not updating.

What's wrong ?

Im using Visual Studio beta2 and SQL Express. My os is Windows 2003 SP1.

Sorry for my english ;)

Answer 1

Anyone ? Plz help mi, i reinstaled Windows, VS, SQL and this still not working !

Answer 2

hi. i am working on the same problem. havent got a solution yet, but reinstall win,vs and sql is nonsense....

i keep you up to date if you want. did you find any solutions for that in the meantime?

greetz

Answer 3

Hi, I got the same problem.
The program didn't work properly even though I constructed it using the bult-in Wizards.

Answer 4

Could someone post repro steps so others can reproduce the problem and, hopefully, provide a solution?  Focusing on sample tables like Northwind.Customers might speed things up and make sure everyone's working with the same schema.  Thanks.

David Sceppa

ADO.NET Program Manager

Microsoft

Answer 5

ADO.NET Program Manager ??

I think you should be the one who can fix our prolem....
So why do you ask for repro steps?

Or am i wrong?

Greetz

Answer 6

I created a small sample application to try to reproduce the problem, but I was able to submit the new row without a problem.  After calling Update, the new row was available in the database.

So, I figured I'd ask for steps that would allow me to reproduce the problem.

David Sceppa
ADO.NET Program Manager
Microsoft

Answer 7

what the hell.......?!?!

i tried also a little sample app, with a datagridview bound to a testtable. a button with just:

TblTestTableAdapter.Update(Me.TestDataSet.Tables(0))

and it works!! LOL :-D

so, where was the problem i ask myself??

greetings from germany,
dFeNsE

Answer 8

That's why this is such an interesting issue.  There are multiple reports of the problem, yet the approach works in a simple application.

I'm hoping that someone can provide steps to reproduce the problem, so I can investigate it.

David Sceppa

ADO.NET Program Manager

Microsoft

Answer 9

I don't have a repro for your exact scenario, but I spoke to one of the VS.NET Program Managers and he offered a potential explanation.  The VS.NET team assures me that they'll discuss the behavior in an upcoming blog entry.  This may be the explanation for the behavior some are seeing.  I'd appreciate feedback from people on the thread on this response.  Does this explain the behavior you're seeing?

If you connect to a SQL Server database file in Visual Studio.NET 2005 Server Explorer and then create your Data Source using that connection, VS.NET will display a dialog asking the following question:

"The connection you selected uses a local data file that is not in the current project. Would you like to add the file to your project and modify the connection?"

If you say "Yes", when you build the application VS.NET will copy the .mdf file into the application's output directory, where the executable file lives.  The design-time connection in Server Explorer will still point to the original .mdf file rather than the copy in your output directory.  This approach simplifies the process of packaging and deploying your application.  However, the approach has some interesting side effects.

You can then run your code and successfully add/modify/delete rows in your code to change the contents of the .mdf file that resides in the output directory.  However, if you check the contents of the .mdf file through Server Explorer, you won't see the new data because you're looking at the original .mdf file. 

There's another side effect of examining the .mdf file through Server Explorer.  The next time you build the application, VS.NET will copy the original .mdf file into the output directory again.  If you look at the .mdf file in Solution Explorer, you'll see that it's marked as "Copy if newer".  VS.NET checks timestamps in the original .mdf file to determine when to perform the copy.  This way, if you modify rows or schema in the original .mdf file, those changes will be available in the .mdf file in the output directory.  According to the VS.NET team, the .mdf file is marked as "Copy always" in Solution Explorer in post-Beta 2 builds.

Say that your application creates a new row and submits it to your .mdf file using a TableAdapter, a DataAdapter, an INSERT INTO query or a stored procedure call.  You can execute that code, see that the return value indicates success, and even query the table to verify that the new row exists.  If you check the contents of the .mdf file in Server Explorer, you won't see the new row.  You'll also cause VS.NET to re-copy the .mdf file into the application's output directory, and the changes you made the last time you ran the application will be gone.

These are the behaviors associated with saying "Yes" to that earlier dialog. 

I hope this information proves helpful.

David Sceppa

ADO.NET Program Manager

Microsoft

Answer 10

Hello Mr. Sceppa.

The information you have provided is very interesting and useful. Thanks for that feedback!

According to the thread problem here, i changed some code in my programs to use a ".update" command. Everything works fine, so i cant also reconstruct the problems "ghostek" and me had.

Currently i try to find some informations, sourcecode or examples to add a "subtable" to the datagridview to make it hierarchical. But still without success...

I ve heard that this would be possible.

So, again thanks for your information.

Best regards,
Steve

Answer 11

Steve,

No problem.  Please update this thread if you have more information regarding the original issue.

Please post the question regarding the hierarchical binding in a new thread.  It helps with tracking answered/un-answered questions in the forum.  Thanks.

David Sceppa
ADO.NET Program Manager
Microsoft

Answer 12

Dear Mr. Sceppa

I have had the same update problem several times, I have always managed to fix it, but I do not know how. Could I send you some code? I can’t post the code here since it’s not mine.

Regards

Carl-Johan Larsson

Answer 13

Carl,

In a previous post in this same thread, I've described a scenario involving connecting to a SQL Server database file (.mdf) in Server Explorer that could cause this behavior.

If you're working with a different scenario and can reproduce it in a small application, please provide steps so that someone can help.

David Sceppa
ADO.NET Program Manager
Microsoft

Answer 14

David Sceppa wrote:

If you connect to a SQL Server database file in Visual Studio.NET 2005 Server Explorer and then create your Data Source using that connection, VS.NET will display a dialog asking the following question: "The connection you selected uses a local data file that is not in the current project. Would you like to add the file to your project and modify the connection?" If you say "Yes", when you build the application VS.NET will copy the .mdf file into the application's output directory, where the executable file lives.  The design-time connection in Server Explorer will still point to the original .mdf file rather than the copy in your output directory.  This approach simplifies the process of packaging and deploying your application.  However, the approach has some interesting side effects.

Hi David,
Can you please highlight some advantages of saying "Yes" to the above dialog. Why would i want to give a local .mdf file to each user of my software ?

Answer 15

Some developers may want to distribute a SQL Server database with their application.  The application may need to perform off-line data cachine or may want to write to a local data store and rely on replication to communicate with the main server.

David Sceppa
ADO.NET Program Manager
Microsoft

Answer 16

David,

Thanks so much for this post, I have been struggling with this exact problem for days without a clue on how to take care of it - it isn't addressed anywhere else I could find.

I understand why it is done this way, but it leads to a seriously nasty problem if you don't know the behavior.  Given your explanation, I don't see why there aren't more people running into it.  Perhaps others are just better informed.

In any case, thanks again.  Your post was extremely helpful.

Answer 17

Thanks for the feedback.  I tried to provide a detailed explanation of the issue because of the complexity of the issue and because the cause of the problem is not intuitive.

Issues like these are tricky because all of the components are trying to help in their own way and no single component is completely responsible for the resulting outcome.  Even with better explanations in the documentation for each component, this type of issue would stump a lot of people.

Providing full steps to help others reproduce your problem may give you the best chance to get an explanation, or at the very least a sanity check.  The key step in this issue was how the connection was created in Server Explorer.  Had I reported the problem, I may not have provided that level of detail in repro steps.

The Internet is a powerful tool for researching issues like these.  Maybe people are running into the problem but are finding the cause of the problem through the MSDN Forums.

Enjoy the forums, lend a hand where you can, and happy coding!

Answer 18

Thanks again, David,

In the spirit you mentioned, here is some more data that might help others and add to the on-line documentation that exists.

Simply saying 'no' to the copy the database option does not solve this problem.  If you  hit 'no,' and specify the directory for the database, say 'DataDirectory' (as automatically defined), you get a compile-time error telling you:

An attempt to attach an auto-named database for file C:\Documents and Settings\...\Visual Studio 2005\Projects\DatBaseTest4\bin\Debug\DataBaseTest4.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

ellipses are added.

Even though the app.config file states that the connectionstring = |DataDirectory|\DataBaseTest4.mdf.

When you place the database in the /bin/debug directory upon creation, the error is the same, but this time, once again, assuming it's in a \bin\debug suddirectoy of \bin\debug:

An attempt to attach an auto-named database for file C:\Documents and Settings\...\Visual Studio 2005\Projects\DatBaseTest4\bin\Debug\bin\Debug\DataBaseTest4.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

ellipses are added.

It appears that the only way to get this to work is to manually change the connectionstring setting in the project during initialization.  Of course, the connectionstring would have to be changed for ALL data adapters.

I must be missing something here.  Given the default behavior (copying the database to the working directory), data entered in the application would never be saved (it would actually be saved, but then overwritten by the next database file copy).  This makes it very hard to test some applications.  If you chose to not have the database copied to the working directory, one would expect to be able to have a single copy of the database that holds all the data written to it.  This, however, does not appear to work since something (I can't find any reference to it) is making the .fill method of the data adapter look in the /bin/debug subdirectory of whatever directory is specified for the database. 

I might be wrong, but this latter behavior feels like a bug to me.  If it isn't, I can't imagine why anyone would want this type of behavior that they don't specify.

Hope that makes some sense.

Answer 19

I had this same problem... Found out that you need to go into SQL Manager, Detach the Db you want to use, configure it in VB2005, then go back into SQL Manager and Attach your Db again.

For some reason VB2005 can't connect to the file at the same time SQL Server has ahold of it... although it can access it just fine after the connection is set up and SQL Server is running.

Answer 20

Here you go:

string stConnection = "server=(local);integrated security=sspi;database=northwind"; System.Data.SqlClient.SqlConnection cnNorthwind = new SqlConnection(stConnection); string stSelect = "select EmployeeID,FirstName,LastName from Employees"; System.Data.SqlClient.SqlCommand cmSelect = new SqlCommand(stSelect,cnNorthwind); string stInsert; stInsert = "Insert into employees (firstName,LastName) values(@FirstName,@LastName);select EmployeeID,FirstName,LastName from Employees where EmployeeID = @@identity "; System.Data.SqlClient.SqlCommand cmInsert  = new SqlCommand(stInsert,cnNorthwind); cmInsert.Parameters.Add(new SqlParameter("@firstName",System.Data.SqlDbType.VarChar,25,"FirstName")); cmInsert.Parameters.Add(new SqlParameter("@LastName", System.Data.SqlDbType.VarChar,25,"LastName")); System.Data.SqlClient.SqlDataAdapter daNorthwind = new SqlDataAdapter(cmSelect); daNorthwind.InsertCommand = cmInsert; System.Data.DataSet dsNorthwind = new DataSet(); daNorthwind.Fill(dsNorthwind,"Employees"); System.Data.DataRow dr; dr = dsNorthwind.Tables["Employees"].NewRow(); dr[1] = "John"; dr[2] = "Doe"; dsNorthwind.Tables["Employees"].Rows.Add(dr); daNorthwind.Update(dsNorthwind,"Employees"); dsNorthwind.AcceptChanges();

But it never worked for me because I am trying with Oledb. according to a blog in msdn JET not support GUID for this case.
See Below please;

Managing an @@IDENTITY Crisis 

William Vaughn
Beta V Corporation
April 2003

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
 
Keep in mind "They will never tell what cant it do BUT they will say what can it do"

Read under "What Are Identity Columns?" please.

Answer 21

David,
I am having problems with SqlXmlAdapter.Update.  I am running Visual Basic Express 2005 and SQL Server Express 2005.

Here is the code that does not work...  I hope this is enough to work with...  2 days of trouble shooting and I am stumped!!!!!

____________________________________________________________________________________________
Dim mvarConnection as String = "Provider=SQLOLEDB;Server=" & sqlServer & ";database=" & sqlDatabase & ";user id=" & sqlLoginName & ";pwd=" & sqlPassword & ";"

Dim xmlDataset As Data.DataSet = New Data.DataSet()

Dim xmlCommand As SqlXmlCommand = New SqlXmlCommand(mvarConnection)

xmlCommand.RootTag = "Root"

xmlCommand.ClientSideXml = False

xmlCommand.CommandType = SqlXmlCommandType.XPath

xmlCommand.CommandText = "Account[@pkAccountID = '1']"

xmlCommand.SchemaPath = mvarXmlPath & "Account.xsd"

Dim xmlAdapter as SqlXmlAdapter = New SqlXmlAdapter(xmlCommand)

xmlAdapter.Fill(xmlDataset)

xmlDataset.Tables("Account").Rows(0).Item("fldAssignedTo") = "0"

xmlAdapter.Update(xmlDataset)
____________________________________________________________________
Here is the Schema:

<?xml version="1.0" encoding="windows-1252" ?>

<Schema id="Accounts" xmlns="urn:schemas-microsoft-com:xml-data"

xmlns:dt="urn:schemas-microsoft-com:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql"

xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

<ElementType name="Account" content="mixed" order="many" sql:relation="tblAccount" >

<AttributeType name="pkAccountID" dt:type="string" ></AttributeType>

<AttributeType name="fldManagerID" dt:type="string" ></AttributeType>

<AttributeType name="fldAssignedTo" dt:type="string" ></AttributeType>

<AttributeType name="fldName" dt:type="string" ></AttributeType>

<AttributeType name="pkClassificationID" dt:type="string" ></AttributeType>

<AttributeType name="fldAddress1" dt:type="string" ></AttributeType>

<AttributeType name="fldAddress2" dt:type="string" ></AttributeType>

<AttributeType name="fldCity" dt:type="string" ></AttributeType>

<AttributeType name="fldState" dt:type="string" ></AttributeType>

<AttributeType name="fldZip" dt:type="string" ></AttributeType>

<AttributeType name="fldCountry" dt:type="string" ></AttributeType>

<AttributeType name="fldWebsite" dt:type="string" ></AttributeType>

<AttributeType name="fldExistingCustomer" dt:type="int" ></AttributeType>

<AttributeType name="fldPhone" dt:type="string" ></AttributeType>

<AttributeType name="fldComments" dt:type="string" ></AttributeType>

<AttributeType name="fldAddedDate" dt:type="date" ></AttributeType>

<AttributeType name="fldAddedByID" dt:type="string" ></AttributeType>

<AttributeType name="fldUpdatedDate" dt:type="date" ></AttributeType>

<AttributeType name="fldUpdatedByID" dt:type="string" ></AttributeType>

<attribute type="pkAccountID" required="no" ></attribute>

<attribute type="fldManagerID" required="no" ></attribute>

<attribute type="fldAssignedTo" required="no" ></attribute>

<attribute type="fldName" required="no" ></attribute>

<attribute type="pkClassificationID" required="no"></attribute>

<attribute type="fldAddress1" required="no" ></attribute>

<attribute type="fldAddress2" required="no" ></attribute>

<attribute type="fldCity" required="no" ></attribute>

<attribute type="fldState" required="no" ></attribute>

<attribute type="fldZip" required="no" ></attribute>

<attribute type="fldCountry" required="no" ></attribute>

<attribute type="fldWebsite" required="no" ></attribute>

<attribute type="fldExistingCustomer" required="no" ></attribute>

<attribute type="fldPhone" required="no" ></attribute>

<attribute type="fldComments" required="no" ></attribute>

<attribute type="fldAddedDate" required="no" ></attribute>

<attribute type="fldAddedByID" required="no" ></attribute>

<attribute type="fldUpdatedDate" required="no" ></attribute>

<attribute type="fldUpdatedByID" required="no" ></attribute>

</ElementType>

</Schema>

Here is the error message:
Event Type: Error
Event Source: RainMakerComponents
Event Category: None
Event ID: 0
Date:  12/4/2005
Time:  10:00:54 PM
User:  N/A
Computer: BLUESTON-L6PYWZ
Description:
Microsoft.Data.SqlXml.SqlXmlException: HRESULT="0x80040e14" Description="The data types text and nvarchar are incompatible in the equal to operator."
 ---> System.Runtime.InteropServices.COMException (0x80040E21): HRESULT="0x80040e14" Description="The data types text and nvarchar are incompatible in the equal to operator."

   at Microsoft.Data.SqlXml.Common.UnsafeNativeMethods.ISQLXMLCommandManagedInterface.ExecuteToOutputStream()
   at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(Stream strm)
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlXml.SqlXmlCommand.ExecuteStream()
   at Microsoft.Data.SqlXml.SqlXmlCommand.ExecuteNonQuery()
   at Microsoft.Data.SqlXml.SqlXmlAdapter.Update(DataSet ds)
   at RainMakerDataAccess.DataAccess.ConnectionManager.Dataset(String ProfileSchema, String XPathQuery, Parameters Properties)

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

Answer 22

I'm having this exact same problem, and simply can't get it to work no matter what I do.  I'm fairly new to VB.NET, but do consider myself a pretty intelligent developer.  I've been trying for three days now to get nothing more than a simple INSERT to work, and it doesn't.  It's basically impossible to get me irritated when it comes to development, but I think I'm pretty much there.

I've tried basically every method there is to insert a row, and they all work fine.  The data gets inserted into the database, but once the application exits, it always disappears.  Anyway, I just tested again, using the easiest and simplest method there is, and nope.  Here's exactly what I did.  I'm using VB.NET Express and SQL Server Express:

1. File->New Project.  Project name is DBTest

2. Project->Add New Item.  Select "SQL Database", and leave the name as the default, Database1.mdf

3. Dialog box opens, and leave the name of the Data Source as the default, "Database1DataSet", and leave all four boxes unchecked.

4. In the Database Explorer, open Database1.mdf, right click on Tables and select "Add New Table"

5. Create a simple table with two columns:
       id INT NOT NULL PRIMARY KEY
       name NVARCHAR(50) NOT NULL

6. In the top tabs, right click on the active tab, and select "Save Table".  Leave the name at its default, "Table1"

7. In the Data Sources window, right click on Database1DataSet, and select "Configure DataSet with Wizard".  Check the Tables, and ensure the Table1 node is checked.

8. Goto Form1 design, and from the Data Sources drag Table1 onto the form.  This adds the BindingNavigator, DataGridView three components to the form, and a small bit of code.

9. Press F5 to run the application.  Add some new rows, and click Save Data button.  When I exit the application, those new rows are always gone.


I've also tried using basically every method of code I can find in the documentation.  For example, I deleted all controls from Form1 in the same application, and removed the code.  I added four buttons, and code as follows.  The first three buttons insert a record into Table1 using three different methods, and the fourth button selects all rows from Table1 and diplays a MsgBox for each row.  Same results as always.  The info gets inserted into the database just fine, and is displayed in the MsgBoxs fine, but when I exit the application the new rows disappear.

For easier reading, you can also view this code at:
      http://www.rdframe.com/vb.txt

Imports System.Data.SqlClient Public Class Form1     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click         Dim testAdapter As New Database1DataSetTableAdapters.Table1TableAdapter         testAdapter.Insert(1, "Matt")     End Sub     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click         'Connect to the database         Dim TestConnection As New SqlConnection(My.Settings.Database1ConnectionString)         TestConnection.Open()         'Initialize variables         Dim testAdapter As New Database1DataSetTableAdapters.Table1TableAdapter         Dim testTable As DataTable = New Database1DataSet.Table1DataTable         'Define new row         Dim newRow As DataRow = testTable.NewRow()         newRow("id") = 2         newRow("name") = "Gary"         'Add new row to DataTable         testTable.Rows.Add(newRow)         'Update the table adapter         testAdapter.Update(testTable)         'Accept changes in dataset         testTable.AcceptChanges()         'Close database         TestConnection.Close()     End Sub     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click         'Connect to the database         Dim TestConnection As New SqlConnection(My.Settings.Database1ConnectionString)         TestConnection.Open()         'Initialize variables         Dim testSql As New SqlCommand("INSERT Table1 (id,name) VALUES (3, 'David')", TestConnection)         testSql.ExecuteNonQuery()         'Close database         TestConnection.Close()     End Sub     Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click         'Connect to the database         Dim TestConnection As New SqlConnection(My.Settings.Database1ConnectionString)         TestConnection.Open()         'Initialize variables         Dim testSql As New SqlCommand("SELECT * FROM [Table1]", TestConnection)         Dim testReader As SqlDataReader = testSql.ExecuteReader()         'Display contents of Table1         If testReader.HasRows Then             While testReader.Read                 MsgBox("ROW: " & testReader.GetInt32(0) & " - " & testReader.GetString(1))             End While         Else             MsgBox("Sorry, no rows")         End If     End Sub End Class

Please help!

Matt

 

Answer 23

I think we have established that  there is a major flaw in the table adapter update method ... How about someone from Microsoft admitting this so we can all stop wasting our time until a fix is applied?

Answer 24

Actually, from reading through this thread more closely I managed to somewhat figure out the problem.  Not completely though.  In the Solution Explorer, select your database file, and in the Properties there will be a "Copy to Output Directory" field.  By default, this is set to "Copy always", but make sure it is set to "Copy if newer" instead.  You can't set this to "Never copy" because then you get some error about not being able to find / access the database file.

So now I can insert records into the database, and the data actually stays there.  The only problem I'm now having is I literally can not touch the Database Explorer.  I can't view the table structures or data, add new tables, modify / add data sources, or anything else.  Any time I do so, the database file ends up getting overwritten again, and I end up with no data in my database.  I don't really get it, because unless I'm doing something wrong, I thought this is one of the first things that would have been fixed.  I mean, what I'm trying to do is very standard, and even explained in the Microsoft walk throughs and video tutorials, so I'm sure there's quite a few others having the exact same problems.

Any advice on that one would be greatly appreciated.  Not being able to look at or modify the structure of my database as I'm developing the program makes development nearly impossible.

Thanks,
Matt

PS.  My apoligies if I sound pissed off, because I'm actually a really nice guy.  I guess I'm just a little irritated that it took me three days to do a simple INSERT, so sorry if I came off like an ass.

Answer 25

Matt

I was just about to post my "fix" which was exactly as you described and on my last test ( previous 5 worked ) I saw that my data had once again been rubbed out. I had opened the database explorer. The Copy if newer does work as long as you stay away from the database explorer.

Ok - i think we can replicate this bug now.

Answer 26

Great!  So there's two of us doing the exact same standard thing, and having the exact same problem!  Perfect, so this is (hopefully) not our faults, and something Microsoft will look into now. :)

Can't wait for either, to find out I was an idiot and did something wrong, or get an upgrade released so I can get developing my app.  I don't care either way, I just want to continue with my development.

Matt

Answer 27

I now what you mean .. customer is waiting and its taking a lot longer than it should! Dont know about you but Im having other inexplicable problems ... like data sometimes saving and sometimes not... not a whole record but parts of a record (columns) .. I am seriously considering backing out of .NET 2.0 before I land up losing my customers confidence (and his business). 

Good luck

Greg 

Answer 28

There is an advice;

Convert your AutoNumber field to Number or Text typed and make it unique( set its Indexed Property to "Yes (No Duplicates)" ). This way Auto-Increament thing gets out of the sceen. Because u cant do auto-Increament with GUIDs but u have to create a new unique GUID which JET ( or simply say BindingSource Object) can not Insures.

Gerg!
By the way, its not with .NET 2.0 but its the JET engine which still have same the responce to such assignemnts.
 
This is what I wanted to do with my Primary Key. But right now I am busy in a DirectX Project and I have its presentations for next two day.

My secound advice is; Convert your project DataBase to SQL.

But If u need to have .mdb views for client also then there is no other way out using my 1st advice.


Regards,

Answer 29

Omer

Thanks for the advice but the database I am using is SQL 2005 express , not Access

Answer 30

I had the same problem and my fix is to let the copy always alone and simply add another connection to the copied database in my /bin/debug folder this new connection will be named sql1 etc, then you can simply open the worked on table in the second DB and see the results.

HOWEVER, I did find one fatal problem which I would like an explanation; in the old days (2 weeks ago) I used an access DB and I would open the access program and a macro would simply read from a text file and load 3 tables and stop once I got out of the Microsoft access program the 3 tables in access would have the new data, I say it took about 35 seconds to do. Now I switch over and created an SQL DB and my new program in VS 2005 reads from the same 3 text files and writes each row into the virtual tables waiting for the update to take place, now like I said 3 tables 2 small with 20 or 30 columns and the 3rd with 190 columns and 15000 records, same stuff I did with Access, But now it takes almost 5 MINUTES to accomplish the same, WHAT DA F*** is going on here, if any one wants to see my code just ask, an answer would be appreciated

Answer 31

Thanks   again.

Answer 32

Ou812

Could you tell me from your experiences, which database is being updated, and which database is being shown in the database explorer?

Thanks

Answer 33

Hey ou812,

Thanks for that, and I could see that working to view the data.  But...  correct me if I'm wrong, but even with that method, I still wouldn't be able to update the database structure, right?  I'd be able to view the data in the second DB connection, but any changes down to the structure will just be overwritten once I run the app again since the main database file would be copied into the /bin/debug directory, right?

Thanks,
Matt

Answer 34

Geeee wrote:

Could you tell me from your experiences, which database is being updated, and which database is being shown in the database explorer?

I could be wrong about this, but I think the way it's supposed to work is the .mdf file in the main project directory is the one that's always supposed to be always used.  Then when you run the app, VB copies the main database file into the /bug/debug directory, and uses that.  Then I guess what's happening is that database in the /bin/debug directory is updated just fine by your app, but the main database file never gets updated.

That's why if you change that setting to "Copy if newer", when you run your app again, the main database file is older so it doesn't get copied into the /bin/debug directory, and you're still running off the /bin/debug database which contains the data.  But when you open up the Database Explorer, it probably adds a log (or something) to the main database making it newer, so the main database file which is empty, ends up getting copied into the /bin/debug directory.

So when you're developing, either in the Database Explorer or running your app, VB is using the database in the /bin/debug directory.  When you open the Database Explorer, it overwrites the /bin/debug database with the empty one in the main project directory.  Since "Copy if newer" is set, when you run your app the main database isn't copied, because it's older than the one in the /bin/debug directory.  Too bad "Never copy" didn't work.

Or, I honestly have no clue, and could be completely wrong about that.  Makes sense though, eh? :)

Matt

Answer 35

Hey, guess I was right. :)  Ok, here's a workaround that works.  Bit of a pain in the ***, but hey, lets us continue with our development.

Leave that setting to "Copy if newer", then before every time you go into the Database Explorer, copy the two database files "dbname.mdf" and "dbname_log.LDF" from the /bin/debug directory into your main project directory.  This way your main database contains the updated information, so when you enter the Database Explorer, the main database ends up getting copied over to the /bin/debug directory, and you still have your data. :)

Cool, eh?  Hell, I should be working for Microsoft. :)

Matt

Answer 36

RDframe
Yes you are correct, I guess if you need to update your DB several times my solution wouldn't work, in my case I only need to load the Db once then I just display records and never have to reload any more.I see the problem , it looks like a major bug.

I still need answers to my performance question anyone???

Answer 37

Geeee
the Db in the Bin/Debug is been updated, now I don't know if an instance of the main DB is been updated then copied to the Bin/Debug but it looks like this is happening since I removed the Bin/Debug before update and after Update there was a new one there.
Is a stupid concept to do that I don't know why Microsoft give us that option I can't think of any use for it!!

Answer 38

I can read the SQL table fine, populate the DataSet, update a column in the dataset, see the change via the .XML property fine.

But when I issue the Update command, I now get this error:

Microsoft.Data.SqlXml.SqlXmlException: HRESULT="0x80040e14"
Description="The data types text and nvarchar are incompatible in the equal to operator."

---> System.Runtime.InteropServices.COMException (0x80040E21): HRESULT="0x80040e14" Description="The data types text and nvarchar are incompatible in the equal to operator." at Microsoft.Data.SqlXml.Common.UnsafeNativeMethods.ISQLXMLCommandManagedInterface.ExecuteToOutputStream() at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(Stream strm)

--- End of inner exception stack trace ---

at Microsoft.Data.SqlXml.SqlXmlCommand.ExecuteStream()
at Microsoft.Data.SqlXml.SqlXmlCommand.ExecuteNonQuery()
at Microsoft.Data.SqlXml.SqlXmlAdapter.Update(DataSet ds)
at RainMakerDataAccess.DataAccess.ConnectionManager.Save(String ProfileSchema, String XPathQuery, Parameters Properties) in C:\Inetpub\wwwroot\Clients\RainMaker 2005\RainMakerDataAccess\ConnectionManager.vb:line 224

Can someone review my schema and tell me what the heck is going on!!!!!!

All of this worked in VB.NET 2003!!!!!!!!

Answer 39

All

The silence from MS is deafening... sure sign that that dont have answers yet! Anyway, we have a few impracticle work arounds that could tide us over if we knew MS was rectifying this flaw but we dont. Has anyone tried to figure out the error thats thrown when you set the "NEVER COPY" property. I might be wrong but my gut feeling is that is where the solution lies. If you look at the connection string in the app.config,it seems the application tries to attach a database at runtime.....  |AttatchDB|.  I think that is when the timestemp is checked and the descision is made whether or not to overwrite the project database with the original. If the NEVER COPY option worked , this problem would be solved. 

Answer 40

 

Hey Geee,

Well, Microsoft isn't completely silent I guess.  I filed a bug report, and got this as a response this morning:

"Thank you for your comments -- I'm assigning this issue to the data team so they can explain why it works this way (or offer other work-arounds if there are any)."

From that, I'm guessing they're not willing to recognize it as a bug.  I'm going to be a bit dumbfounded if they say, "Well, there's nothing wrong.  That's just how it's supposed to work".  It's going to leave quite a few people out there lost.  Oh well, I scrapped the SQL Database from my project anyway, and am going to use XML files instead, so this doesn't really matter to me any more.

Matt

 

Answer 41

Here's the blog post that David was talking about: http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx

This was a very tricky scenario to get right and was heavily debated within our team on the best behavior we should ship with. 

Hopefully this sheds some light on the overall feature,

Thanks

Answer 42

Steve,

I have a couple of postings in this thread.  I am really confused as there seems to be 2 different problems addressed in this thread.  Connectivity and updates to a database when in Debug mode versus Release mode.

I am not sure my problem is related to this scenraio.  Can you please look at my postings and offer insight to whether this is my problem as well.

It's been 2 weeks and I am stumped!!!!

Thanks in advance!

Ron "Doctor30306"

Answer 43

ok the problem is caused by saying "Yes".

 I changed the always copy to output directory to "never" and now the form cannot find the dataset to work with so I suspect that I have the problem described.

What is the best way to control the location that is being updated. I have seen in another example the use of "manifeststring" or something like that to refer to the manifest directory. Is that the preferred location for databases which are required as part of a deployment and build-test strategy? How do I control what is being updated and/or change the location the data is found. And I wonder why can the dataset not be found?

Thanks for the answer on the adapter update problem.

Answer 44

Hello David,

I have no more problems with update (add and change records in a datagridview)when I use the solution you gave here. But there is a problem when I delete a record in a datagridview en then use the DataAdapter.Update method. Then I get an error on that with the message : Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.

Do you have a solution for this too?

Thanks

Hans

Answer 45

Steve Lasker -MS wrote:

Here's the blog post that David was talking about: http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx This was a very tricky scenario to get right and was heavily debated within our team on the best behavior we should ship with.  Hopefully this sheds some light on the overall feature, Thanks

I have followed this reply and it works to a point!

If you have a primary key in the DataAdapter (when configured) creates the Insert,Delete,Update,Select commands. However if in the database (SQLExpress) you have one of your columns marked as the 'identify' column making it auto-inc then when you carry-out the same configuration on this DataAdapter it only creates the Select and Insert commands (As if you had not created a primary key - which you have).

Does the DataAdapter automation not work this way?? what is the point of the identify param if it breaks the DataAdapter confiuration - we might as well code thing manually!!

Thanks in advance

Steve 

Answer 46

msdn has a project for their beginners lesson 09 which comes complete with the textboxes etc. that the lesson shows you how to create.  I downloaded the project and checked it against my code and they match exactly.  Upon changing the data in the fields and clicking the button [which connects to an Update routine] the data base in the project, MyCompany.dbf, does not update, it does not save the modified data.  Upon running the program again, the old data remains.

I've tried everything to find out why I can't update the data, to no avail.  I kept thinking that somewhere there was a command which limited the storage to 'read only', but I couldn't find any such command or parameter.

I duplicated [actually triplicated] the problem by writing the code three times under different filenames.  No change.  I've tried the SQL server surface config. routine, nothing.  It should be simple.  It probably is, but I can't find it.

Any help would be appreciated.  I may have to create my own arrayed dBs.

 

 

 

 

Answer 47

Hans,

This exception occurs when you're using a DataAdapter that does not contain updating logic.

If you created the DataAdapter using your own code, you'll need to supply logic so the DataAdapter can submit the pending changes.

If you created this DataAdapter using Visual Studio .NET, this scenario occurs if Visual Studio .NET could not find a primary key on the table for your query, or if the query referenced more than one table.  Make sure your query references only one table, that the table has a primary key defined, and that the primary key column (or columns) appear in the results of your query.

I hope this information proves helpful.

Answer 48

Hi,

   I am connected to SQL Server 2005 and I am seeing the same problem. I check the datarow counts etc.. in the dataset and issue the TableAdapter.Update. The rows are not inserted in the table. I also was running SQLProfiler to monitor the activity (not much I am the primary user) and there was no SQL executed on my behalf.

Just as a side note I populated the dataset using a DataTableReader and the Load method because I am copying data from one dataset to another.

Fred Veasley

Answer 49

I've been working a VB2005 project and found this thread.   I'm seeing similar behavior during development and understand the statement listed in the above blog link.  

It took me back that I could see the data during debugging but the data information was not visible in the server explorer. 

I think this behavior should should have better visiblity so others do not experience a delay in developing their applications.   I think an option should be available to write and keep the data in the database without having to swap files outside of the IDE (an option besides keeping your database outside of the project directory).   I can see the logic of the VB2005 development team to not have the data stored but on the other side a person would typically clear the table before releasing the project to the wild.

One thought, is it possible to use a dataset and dataadapter to write the database information to a file?   One could view the file and verify that that data is being manipulated as desired.  Time to dive in to datasets.

Thanks for the efforts on this thread.

Rick

Answer 50

Rick,

Thanks for the suggestion.  I'll pass it along to the VB team.

In the meantime, you can use the TableAdapter (or DataAdapter) to verify that the changes were submitted successfully in a couple ways.

The Update method returns the number of rows affected by the update operation.

You can also re-execute the query and examine the contents of the row(s) in the database using either a TableAdapter or a DataAdapter after submitting changes.

The Visual Studio TableAdapter (and DataAdapter) Configuration Wizard automatically adds a "refresh" query to the InsertCommand and UpdateCommand by default when working with SQL Server so the contents of the DataRows match the corresponding rows in the database after submitting pending changes.

I hope this information proves helpful.

Answer 51

David,

Thanks for the response and help in carrying comments from this thread back to the developers.

The Update method is what I've been using to keep track of my program logic.

I've also posted a comment on this page:
http://discuss.joelonsoftware.com/default.asp?dotnet.12.297698.0

Thanks!

Rick

Answer 52

I knew it! I'm just one of the many who have experienced this quirk. I posted about this yesterday here. I thought it was a GUI problem. This may be the reason why no one has referred me to this thread.

Anyways, thank you so much Mr Sceppa. I'm looking forward to reading your new ADO book. I have the last one. It still rocks!

 

Answer 53

Thanks for the feedback on the thread and on the book.  I posted a link to Steve Lasker's blog post in the thread you referenced.

Answer 54

I also fought with the tableadapter problem first referenced at the beginning of this post.
The code below ultimately worked for me, updating the Northwind database (SQL).

Try
' This uses a SQL connection string - created using the datasource wizard
Dim ds As NorthwindDataSet
ds = New NorthwindDataSet
Dim DA As NorthwindDataSetTableAdapters.CustomersTableAdapter = New NorthwindDataSetTableAdapters.CustomersTableAdapter
Dim rwNew As NorthwindDataSet.CustomersRow
rwNew = ds.Customers.NewCustomersRow
rwNew.CompanyName = "A Company Name"
rwNew.ContactName = "A Contact Name"
' Note for debugging that the value for CustomerID must be unique
rwNew.CustomerID = "ABCDE"
ds.Customers.Rows.Add(rwNew)
Dim intResult As Integer
intResult = DA.Update(ds.Customers)
' MsgBox for debugging
MsgBox(intResult.ToString)
Catch ex As Exception
' MsgBox for debugging
MsgBox(ex.ToString)
End Try

Answer 55

I'm perhaps a bit late posting on this thread, but I am having a almost excactly the same issue, with one significant difference.

True enough, answering 'yes' to that dialogue will create a copy of the file, and can cause confusion when trying to verify data.  Early in my experience with this issue, I recognized that, and began a sample project selecting 'no' to the dialogue.

The sample project is outlined here...

http://msdn2.microsoft.com/en-us/library/0f92s97z.aspx

For good measure, i'm using Visual Studio 2005 Version 8.0.50727.42

By selecting 'no' to the dialogue, the database file is left in its original location, and changes should be reflected.

Execution of the application delivers desired results until I attempt to save the data.  The first save attempt generates an exception, and subsequent save attempts are reported successful.

However, inspection of the database file does not reflect any changes whatsoever.

Any help or information would be extremely helpful.

Answer 56

Ron,

What does the exception say?  Try setting a breakpoint of changing the code to MessageBox.Show(ex.ToString())

 

Answer 57

David Sceppa wrote:

Ron, What does the exception say?  Try setting a breakpoint of changing the code to MessageBox.Show(ex.ToString())

David,

After, the initial exception I was recieving as related to the max field size of the CustomerID field being set to 5.  An oversight on my part.

Using the sample outlined, I still cannot reflect changes back to the database, though everything appears to be working properly.

I can successfully perform a WriteXML and verify that in memory the data exists as it should.

If there is anything I can provide here, let me know.

Answer 58

 

Hi David,

 

after reading a few of your replies on different threads at MSDN Forum, I thought you are certainly the right person to ask for advice how to solve the problem that i am encountering and which is  related to Data Binding and retrieving data from database.

 

I am building a three-tier (data, business object, and user interface)Windows App with three child Forms embedded into the MDI Parent form. The  three Forms are related to three distinct data inputs, whilst the Form1 has performs calculations using coded algorithms that are based on data saved in Form1, Form2 and Form3.

 

 There is only one DataSet consisted of three tables . I have used DetailView for all three Forms, so as to bind data to textboxes and labels, which in turn receive data from the Web through parsing csv.

 

1) One way how i tried to retrieve the data saved in Form1 to the Form2 was:

 

Me .lblCurrent.Text = Convert.ToString(Current(Me.OptionsDataSet.Tables("VIX").Rows, 1))

         

with public function

Error- reads there is no row at the position (-1)

Note: this function and call works perfectly right for datatable within the Form1 but not if I am calling datatable from Form2 and Form3, albeit there is only one DataSet - Data source.

Public Function Current(ByRef myDataPoints As DataRowCollection, ByVal intCol As Integer) As Double

Dim dblCurrent As Double

Dim x As Integer

x = myDataPoints.Count - 1

dblCurrent =

CDbl(myDataPoints(x).Item(intCol))

Return dblCurrent

End Function

2) The second way that I have tried to pull data from Form2 and Form3 is much more straightforward, but also unsuccessful:

Dim obj1 As New fclsTrade()

Me.lblChange.Text = obj1.lblPosit.Text

Note: there is no error but also no value shown in the textbox, if I tried obj1.lblposit.toString then as one would expect i get info about System

3) The third way was to create a view in database related to the last field in the column and then in buton click event:

'TODO: This line of code loads data into the 'OptionsDataSet1.Position' table. You can move, or remove it, as needed.

Me.PositionTableAdapter.Fill(Me.OptionsDataSet1.Position)

The problem with last is that it is a static one -time view, and though i am calling this with button click event it does not update after the button is clicked, though there were new saved entries into particular table.

I am starting to think that i've got it wrong about the interoperability and interconnectivity between forms and that only feasable solution is to have only one form, though it reduces the flexibility in  using the trading platform and seeing only those data that are needed at one particular moment.

 

Cheers, Dan

Answer 59

I have had that same problem, I found a way to jimmy rig it.  All you have to do is use the basic save and add functions

bindingsource.addnew()

and

Try

Me.Validate()

Me.StressBindingSource.EndEdit()

Me.StressTableAdapter.Update(Me.PBIFULLINFODataSet.Stress)

Catch ex As Exception

End Try

use the try to catch the exception that it pulls if you try to save a null row or use the null function

but the stupid part is it will not work until you publish it, after it is published it works fine.

Answer 60

WOW !!!  I have spent several hours reading all of this thread and others, plus newer posts where Lisa answered (in the ExpressVB Forum), and experimenting and I do think I have finally "got it".  But   - should each and everyone of us taking a "beginners" learning video series have to go through this? (Afterall, this thread has already gotten over 10,000 views)  The answer and workarounds are not all in one reply, you have to work your way through all this! 

Dave - Can all of this be summarized and, perhaps, put in a FAQ, and/or hopefully referenced in some way as an addendum to Lesson 09?

Answer 61

I'm having some of the same problem as posted in this thread. But I do not seem to be able to fix it even if I've read through all of it, I'm not a experienced programmer...

Here is my problem, and if anyone can give me a "dummies" answer I'll be greatful!

-I have a Access database which I want to be able to read and save from/to.

-I add this to my project, by add existing item, and finishes the data source wizard that appears.

-I change the Copy to Output Directory to Copy if newer

-I fill my datasets with; Me.KundeTableAdapter.Fill(Me.Dataset.Kunde)

-When I do my changes to this dataset

-Updates with;Me.KundeTableAdapter.Update(Me.Dataset.Kunde)

but the it does not seem to update at all!!

I've tried so many different solutions now, that my hole program is starting to look like one big mess...

 

Hege

Answer 62

Hello for all

I just use  VC#  .NET Today  for my database , basic project

It's all work for Retrieve Data

but not work all , for update , insert

 

I try a long time , read from net 

 

but my solution is work only when  i just

delete  DateTime Picker from my application 

 

and cold this in button click

=======================

int n = 1;

try

{

//this.Validate();

bindCD.MoveFirst();

this.bindCD.EndEdit();

int row = cDTableAdapter.Update(dataSetAll1.CD);

MessageBox.Show(dataSetAll1.Tables["CD"].Rows.Count.ToString());

MessageBox.Show("updatede " + row.ToString() + " row " + n.ToString());

}

catch (Exception ex) {

MessageBox.Show(ex.Message);

}

============================

 

and it ' s all ok 

 

in my case , i think because  i  incorrect set propery of data binding of

datetime picker  , and when save data with null value it may cause some

exception in code that generated by vs.net , or may exception in some code in

standard object like  DataAdapter   but these acception not raise  any  error to debugger

and then it just not send sql to SQL Server Expres  , without notice  .

 

this is problem on only my app .  and may not for all other app

 and now it's all ok  , with new config datetime picker property .

 

Answer 63

Okay here is the "fix" to the DataAdapter problem.  You must make an updatecommand for the tableadapter you create.  for above

Dim ObjCommandBuilder As New SqlClient.SqlCommandBuilder(xmlAdapter )

xmlAdapter .InsertCommand = ObjCommandBuilder.GetUpdateCommand()

 

For doing updates.... 

xmlAdapter .UpdateCommand = ObjCommandBuilder.GetUpdateCommand()

 

These lines need to go above the update method and will automatically generate SQL statements for you.  The SqlCommandBuilder will only work with single table updates.

Of course this would have all been alot easier if VB.net was really VB and not C#.

Answer 64

David,

I desparately need help at this point.  No one else is using the SqlXmlAdapter with SQL Server 2005.  Is there something special that has to be done in order to Update and Insert?

 

Ron "doctor30306"

 

Answer 65

Ron,

I've used the SqlXmlAdapter with SQL Server 2005 and was able to submit inserted and modified rows without a problem following the same patterns that worked with SQL Server 2000.

You might want to review this thread to see if the problem you're experiencing is related.

Answer 66

i changed some code in my programs to use a ".update" command

I am having the same problem with ".update".  I am a newbie so please explain what code did you change so you could use the "TableAdapter.update" method?

Best regards,

Tom

Answer 67

The Update method returns the number of rows affected by the update operation.

How do you get the ".update" to work. Every time I enter it I get a message that it is not a member of my data base. My data base is Access 2003 and I am using VSTO 2005.

thanks for any help

Tom

Answer 68

David,

dhank's for help.

My application was using the mdf file at ..\bin\Debug\   where the TableAdapter.Update() method was working correctly. Though once I run the my application again the updated data dissapeared. It seems, the mdf file at the location ..\bin\Debug\  was overwriten by the one from the original location at the start of my application.

sorry for my english

 

 

Answer 69

David Sceppa - Microsoft wrote:

Could someone post repro steps so others can reproduce the problem and, hopefully, provide a solution?  Focusing on sample tables like Northwind.Customers might speed things up and make sure everyone's working with the same schema.  Thanks. David Sceppa ADO.NET Program Manager Microsoft

Answer 70

I had the same problem, thought I was going crazy; but after reading here I copied my access database file to a different location, then deleted the database in VS and also deleted the .xsd file and reloaded the database selecting "no" to the copy option. Problem solved; deleting is probably not something you want to do if you already put a lot of work into it, but it works; I hope there will be a patch. Thanks for all of your help!

Answer 71

Thank You Thank You Thank You Thank You!   I changed the copy mode and my code worked first time, finally.  I hope a change to this default behaviour is considered for future versions, or a change in the MSDN documentation tutorial so we newbies to this can see why our code doesn't work.