Solved

.NET 2.0 TableAdapterManager Does Not UPdating backend Database-I must be missing something basic

Posted on 2009-05-18
12
440 Views
Last Modified: 2013-11-26
Very similar to post:  
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23895093.html?sfQueryTermInfo=1

I'm building a Windows Application and attempting to migrate my skill-set from manually coding SQL Add/Update/Delete to using the databinding functionality in VS 2008. I have a small table of Users in SQL Server Express which I attempt to update from a form. The data changed are excepted on the screens and changes are redisplayed as I navagate away and back to the records again. Also, from debug, I can see the values in the DataSet are changing.
However, when I press the update button, and the TableAdapterManger.UpdateAll(me.DSusers) executes (no exeptions or errors) but the back end data base is unchanged. After reading several related issues I am:
Including a manual call to BindingSource.EndEdit()
If a duplicate set of data is being used in Debug, I can't find it.

Please Help me determine the problem so that I can move forward in my project.  
Try

            Me.UsersBindingSource.EndEdit()

            Me.TableAdapterManager.UpdateAll(Me.DSusers)

        Catch ex As Exception

            MsgBox("Error on UPDATE - (" & ex.Message & ")")

        End Try

 

Connection String = Data Source=CDS001\ICEXPRESS;Initial Catalog=ICExpress;Persist Security Info=True;User ID=icexpress;Password=jack08;

Open in new window

0
Comment
Question by:mpharis
  • 7
  • 5
12 Comments
 
LVL 41

Expert Comment

by:graye
ID: 24441265
Take a look at the TableAdapter's properties....   you should be able to see the automatically-generated INSERT, DELETE, and UPDATE commands.
Also, if you're just using one table, you probably don't need to use the TableAdapterManager (as that was designed to perform the Update() method on each TableAdapter in the correct order... something that a bit of overkill if you've just got one TableAdapter).   So, just for the sake of simplifying your example, let's change that line to use the TableAdapter's update method.   It would look something like this:
     me.DSTableAdapter.Update()
... just guessing on the name of the TableAdapter!
0
 

Author Comment

by:mpharis
ID: 24441865
Thanks, I'll give it a try later today and report the results. Thanks for the reply.
0
 

Author Comment

by:mpharis
ID: 24445797
ARGH!!!  This is very frustrating and I know I'm overlooking something simple.
I looked at the UPDATE commands in the properties of the TableAdapter and it looked fine. I replaced the TableAdapterManager with the TableAdapter and attempted an update. Still same results. Looks like good in the Application and Data set. I can advance to other records and them come back to the one I changed and it shows the changes. However, when I end DEBUG and the start the application again in DEBUG the changes are not there.  I just verified that when I execute the UPDATE command in the application, instead of exiting I when into the SQL Database and the backend data did not changed. I am not using a database file in the project/solution. I'm using a stand alone SQL Express database which exists outside of my project. I don't understand but is there any way that VS2008 is creating a temporary version of the database each time I enter debug and if so, how can I tell it or control it.  
Once again. Thanks in advance. Any info and/or direction you can give is greatly appreciated.  
0
 
LVL 41

Expert Comment

by:graye
ID: 24445955
Show us the generated connection string (also from the TableAdapter's properties), just to verify that VS2008 didn't somehow switch you over to a database file.
0
 

Author Comment

by:mpharis
ID: 24447328
Connection string from the tabeadapter propery:
Data Source=CDS001\ICEXPRESS;Initial Catalog=ICExpress;Integrated Security=True

I am manually resetting the connection string and loading the tableadapter in he Form Load event using the following code:
        stmnt = "SELECT userid, currentco, currentbu, admin, userDescription, createdtimestamp, " & _
            " defaultco, defaultbu, rrn, active FROM dbo.Users"
        DSusers.Clear()
        Dim DAusers As New SqlDataAdapter(stmnt, con)
        DAusers.Fill(DSusers, "Users")
        Me.UsersTableAdapter.Fill(Me.DSusers.Users)
This uses the connection string specified in the original post. It happens to be the same as the connection string property because this is the test sytem.
Looks like the only difference in the connection string is the change from integrated security to requiring credentials.





0
 
LVL 41

Expert Comment

by:graye
ID: 24447388
I think we're getting somewhere...   in the short example above, you're actually using two different techniques together...   the "traditional way" of using a DataAdapter to fill the table, but then you switch to using TableAdapters afterwards...    to fill the same table over again?
I'd suggest that  you stick to just one techique...
  • DataAdatper.Fill()
  • ... do something with the locally-cached DataSet
  • DataAdater.Update()
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mpharis
ID: 24447438
That makes sense. How do I make the connection string dynamic for deployment to different clients and databases. My current technique (obiviously not fully tested) is to use the local database for development and call a custom class in the Page_Load event which retrieves the specific client connection string and attempts to re-bind/re-fill the datasets. That was my intended purpose above. What is the best way to make the application connection portable?
0
 
LVL 41

Expert Comment

by:graye
ID: 24449736
Most folk put the connection string in the web.config file.... that way you'd just have to change it in one place and the change would appear in all of the pages.
http://peterkellner.net/2008/02/23/webconfigbestpractice/
0
 

Author Comment

by:mpharis
ID: 24450319
Thanks for all the input graye. However, this is a windows form application so there is no web.config file and unfortunately the Connection String Application setting is "ReadOnly" so I can't dynamically change it there. Your input is much appreciated in this area but I suppose I need to get back to the focus of the original Post. I've tried going back to simply using the TableAdapter to update but still have the same results. DataSet gets updated in on screen but no Error messages or any indication of anything wrong but... backend data not updated.
I don't want to upload the whole VisualStudio solution/project; what can i provide to help nail down the issue. It is almost like the DataSet is not Bound to the backend database. I have relatively good confidence that the issue is not a copy or temporary database created at debug time. I've configured the application to display the connection info to the form at time of update and all looks well at that point.

I am attempting to reset the data connection to the client database like we discussed previously but have empoyed a different approach which fills the dataset and appears to be working fine (but again, this may be the area that's causing the problem)
0
 

Author Comment

by:mpharis
ID: 24450388
I removed any additional code and simply used the connection defined in the wizard with the table adapter. Same results. DataSet appears to update but database is unchanged.
0
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 24453454
OK... let's start at the beginning.   Let's get the dang thing running, and then we'll add to it until we figure this out.
I propose that you start over with hand-coding the DataAdapter and DataSet without the use of any wizard or designer at all.    As I've said... let's get it to work first
Take a look at the following artciles on how to use ADO.Net without wizards or designers
http://www.emmet-gray.com/Articles/BeginningADO.htm
http://www.emmet-gray.com/Articles/SavingDataADO.htm
 
0
 

Author Comment

by:mpharis
ID: 24454315
I appreciate your help mr. gray.  I'm not where i can look at this right now but will work through it over the week end. Please continue to check on my progress as you have time and i'll post results over the weekend. thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now