[RESOLVED] Database not updating

[GizSho]

Hello, I am working with an MSSQL database, but I have a problem ( which previously, I've not encountered ).

Basically, whenever I try to update a row in my database, it works, but as soon as I restart the application, the changed information resets.

Example:

       Dim UN As String = DirectCast(Items(1), String)
                Dim R As LeafDataSet1.UsersRow = DB.Users.FindByUsername(UN)
                If Not R Is Nothing Then
                    R.Username = "Test"
                End If

                Users.Update(DB)

Items(1) is the username. R basically does a select statement, and then if R is found, it changes the username to 'Test'. This works, but when I restart my server and try the new username, it goes back to my previous username (I.E it doesn't work).

Any more information I'll be happy to provide. Thank you.

[jmcilhinney]

Follow the first link in my signature to learn how local data files are managed.

[GizSho]

Follow the first link in my signature to learn how local data files are managed.

Hi, I've read through the entire article and I've come across a problem:

The only one that seems to do what I want to do is 'Copy if Newer' but the article specifically says that it is not recommended ( and for a good reason, that has happened to me before ). Regardless of the article warnings, I've switched my project to 'Copy if Newer' regardless, and the problems still occur. Strangely enough though, it worked before so I'm not sure.

I went as far as compiling my project ( not just debugging it ) and whenever I restart it, the data still isn't saved. As you can imagine, this is extremely frustrating because the server will have to be opened multiple times ( And it needs to save the data!! ).

What I am dumbfounded about is why it saves newly added rows, but not changes to it.

Also, please excuse my ignorance. I'm very new to this. If there is any way I could contact you over some type of instant chat such as MSN, I'd love to do that, just so you could get a closer look at this. Thank you.

[jmcilhinney]

It's important to understand what the three options (Always, If Newer and Never) actually mean.

Always means that every time your project is built, the database will be copied from the source folder to the output folder, thus over-writing any changes you made in previous runs. If you run your project twice without making any changes to the source files then the project will not be built the second time, so your changes will survive. As soon as you make any changes to any of the source files, your project will be rebuilt the next time you run it so the working database will be overwritten. If you want your database changes to survive from run to run, that is obviously not the option you want. If you don't care about the changes made during previous runs then this option is for you.

Never means that the database will not be copied from the source folder to the output folder and if there is a database already in the output folder when you build then it will be deleted. This option should only be used if you want to use a database in some other location or you will place the database in output folder by some other means.

If Newer means that the database will be copied from the source folder to the output folder if and only if the last modified date of the source file is later than that of the output file. This is going to happen if you make any changes to the schema or data of the source file. That is obviously a good thing because you would almost always want those changes to show up the next time you run the project. the reason that Microsoft caution against using that option is that you don't actually have to make changes yourself for the last modified date to change. Simply opening the file is enough. Even so, the fact that you will lose your changes sometimes is still better than the knowledge that you'll lose them every time using Always. All you have to do is make sure that you do not open any connections to the source database between runs and you can be sure that the last modified date will not change and your changes will be preserved.

It is also important to note that these options have absolutely ZERO effect on the behaviour of your app once it has been deployed. They are relevant only to you as you develop the app in the IDE.

Having said all that, you obviously have to first ensure that the data is actually being saved from the app to the working database. When you call Update on your TableAdapter, it will return the number of database records that were affected by the operation. You should check that number to make sure it is what you expect it to be. If it is then the changes were absolutely, definitely saved to the working database. If you then restart the app and the changes are gone, the only way that could have happened is if the working database was overwritten by the source database.

[GizSho]

Thanks for the indepth reply! I've narrowed the problem down to your last paragraph.

http://gyazo.com/a59fb41b842476cf7b6e397c578bc5e1.png

States that zero rows are updated. I'm not sure where to go from here.

Here is the code that is ran:

Dim UN As String = DirectCast(Items(1), String)
                Dim R As LeafDataSet1.UsersRow = DB.Users.FindByUsername(Items(1).ToString)
                Console.WriteLine(UN)
                If R IsNot Nothing Then
                    R.Leaves += 10
                    R.AcceptChanges()
                    Console.WriteLine(Users.Update(DB) & " rows were updated")
                End If

Also, with the copy if newer setting. Is there a work around for that? I plan to commit updates to the server in the future and I cannot lose all of the data in it.

[jmcilhinney]

The issue with that last code snippet is the fact that you are calling AcceptChanges, which you weren't in any of the previous code snippets you posted. You will almost never have to call AcceptChanges explicitly.

By calling AcceptChanges you are saying that all the changes in your DataRow have been saved, so obviously it should only be called after you have actually saved the changes to the database. The thing is, the Update method of a DataAdapter or TableAdapter will call AcceptChanges implicitly, so you will not have to call it yourself in most cases.

[GizSho]

The issue with that last code snippet is the fact that you are calling AcceptChanges, which you weren't in any of the previous code snippets you posted. You will almost never have to call AcceptChanges explicitly.

By calling AcceptChanges you are saying that all the changes in your DataRow have been saved, so obviously it should only be called after you have actually saved the changes to the database. The thing is, the Update method of a DataAdapter or TableAdapter will call AcceptChanges implicitly, so you will not have to call it yourself in most cases.

I didn't have to call it previously, but I experienced a problem which wouldn't allow me to update ANY data in the database ( regardless of whether or not I restarted the application ) and calling AcceptChanges seemed to fix it.

I'll remove AcceptChanges and post results.

I experience the same problem

   Case Events.Debug

                Console.WriteLine("Debug")

                Dim UN As String = DirectCast(Items(1), String)
                Dim R As LeafDataSet1.UsersRow = DB.Users.FindByUsername(UN)
                If R IsNot Nothing Then
                    R.Leaves += 10
                    Console.WriteLine("{0} rows were updated", Users.Update(R))
                End If

It doesn't seem to run anything past the Console.Writeline without the AcceptChanges method being called.

Also, if I try to do it again, it doesn't do anything ( Doesn't rewrite Console.Writeline("Debug") ) - this is strange.

[jmcilhinney]

It doesn't sound strange at all. It sounds like the Update call is failing altogether, i.e. throwing an exception. Basically, when you call Update on a DataAdapter or TableAdapter, there are only three possible outcomes:

1. The call fails, i.e. throws an exception, indicating that something is seriously wrong.
2. The call succeeds and returns zero, indicating that there were no changes to save. That's what would have happened when you were calling AcceptChanges.
3. The call succeeds and returns a non-zero value, which means that there were changes and they were saved.

If you are not seeing anything written to the Console then it must be the first case, so you need to use the type of the exception and/or the error message to determine what the issue is and fix it. Unless your code is in the Load event handler, the IDE should be notifying you of the exception already. Regardless, you can always use a Try...Catch block to catch the exception and examine it as closely as you need to. Probably you have an issue with your SQL code, although the root cause of that may differ from case to case. The exception will tell us exactly what the problem is.

[GizSho]

http://gyazo.com/779b7bd7912fe2e14a227ed004b67131.png

I assume it's throwing that error because the data 'Leaves' I am changing is not in correct format? Leaves is set as an integer in the schema so that can't be right. I don't have 14 columns in the table either so I'm not sure

[jmcilhinney]

First up, please just attach your images to your posts. That way we don't have to muck around to see them. Secondly, why worry about an image at all when you could have just typed that in? The easier you make it for us, the more likely you'll get the help you want.

I do wonder why you weren't able to see that an exception was being thrown before now. Regardless, you're obviously passing the wrong data to one of your parameters. You need to work out which column and what value.

[GizSho]

First up, please just attach your images to your posts. That way we don't have to muck around to see them. Secondly, why worry about an image at all when you could have just typed that in? The easier you make it for us, the more likely you'll get the help you want.

I do wonder why you weren't able to see that an exception was being thrown before now. Regardless, you're obviously passing the wrong data to one of your parameters. You need to work out which column and what value.

First up, please just attach your images to your posts. That way we don't have to muck around to see them. Secondly, why worry about an image at all when you could have just typed that in? The easier you make it for us, the more likely you'll get the help you want.

Sorry, in the future I'll do just that, now that you mention it, it was pretty stupid of me to just attach an image anyhow.

I do wonder why you weren't able to see that an exception was being thrown before now. Regardless, you're obviously passing the wrong data to one of your parameters. You need to work out which column and what value.

I'm not sure if the first sentence is something directed towards me as "Why couldn't you figure this out before?", if it was, I do apologise, I am still very new to this.

I deeply appreciate all of the help you've given me, so consider this thread resolved.