locked
Adding new Data to SQL Database RRS feed

  • Question

  •  Hey,

    Im new to VB and am trying to add a new row of data into a database table. I can read from the database just fine but when I try to add something new the database never gets updated. Inside the program i can see the the information i added but when i restart it the information is no longer there. Here is the piece of code I have for writing to the database.

    Dim dbNewRow As DataRow = Database1DataSet.EmployInfo.NewRow

    dbNewRow("EmployNum") = "08"
    dbNewRow("Name") = txtEmployeeName.Text
    dbNewRow("Address") = txtEmployeeAddress.Text

    Database1DataSet.EmployInfo.Rows.Add(dbNewRow)
    EmployInfoTableAdapter.Update(Database1DataSet.EmployInfo)

    (Im using Microsoft Visual Studios 2010) If anyone can help me get over this hump i would greatly appreciate it.

    Monday, July 16, 2012 5:14 PM

Answers

  • There shouldn't be. I would move it out of the bin folder to another location and then re-run the app. Check the folder after re-running the app to see if the file is back again. If it is, then you need to check the Copy to Output Directory property of the database. Also, make sure that the database in Solution Explorer is pointing to the file in your project directory.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by Frank L. Smith Tuesday, July 17, 2012 10:23 PM
    • Marked as answer by Youen Zen Friday, July 27, 2012 3:33 AM
    Tuesday, July 17, 2012 9:56 PM

All replies

  • If the Copy to Output Directory property for the database is set to Copy always then change it to Do not copy. Otherwise, you will be overwriting your changes whenever you restart the app.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, July 16, 2012 6:11 PM
  • If you are having Embedded SQL Server then set "Copy to Output Directory" property of database file to "Copy if newer".


    Gaurav Khanna | Microsoft VB.NET MVP


    Monday, July 16, 2012 6:11 PM
  • The Property was set to Do Not Copy. I also tried selecting Copy if Newer with the same results. It seems as if the table i am working with is never actually updated before I close the program.
    Monday, July 16, 2012 6:41 PM
  • "EmployInfoTableAdapter.Update" is a function. Which value does it return?

    Do you call somewhere call Database1DataSet.EmployInfo.AcceptChanges? (you shouldn't)


    Armin

    Monday, July 16, 2012 7:10 PM
  • Are you absolutely certain that the database that the EmployInfoTableAdapter is writing to is the same as the one you are reading from or viewing?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, July 16, 2012 7:15 PM
  • I would say im about 95% certain i am writing to the same one. I dont see how I could be writing to something else. But then again Visual Studios has set up much of the connection work so i dont know everything that went on there.
    Monday, July 16, 2012 7:20 PM
  • Is there any way I can verify which Table I am attempting to write to?
    Monday, July 16, 2012 10:43 PM
  • Is there any way I can verify which Table I am attempting to write to?

    I don't think it's that. I posted a somewhat long thread a while back here - you can skip over the first few sections about setting up the database and all that; the last two are what you're interested in. I have an example in the code there about adding, deleting, modifying, all that.

    See if that helps?


    Please call me Frank :)

    Monday, July 16, 2012 10:52 PM
  • I'll hone it down a bit. In the last of those sections, you'll one that shows this:

     Dim nr As ContactsDataSet.FriendsRow = ContactsDataSet.Friends.NewFriendsRow

     

                   With nr

                       .FirstName = anc.ContactToBeAdded.FirstName

                       .LastName = anc.ContactToBeAdded.LastName

                       .PhoneNumber = anc.ContactToBeAdded.PhoneNumber

                   End With

     

                   ContactsDataSet.Friends.Rows.Add(nr)

     

                   FriendsBindingSource.EndEdit()

     

                   If ContactsDataSet.Friends.GetChanges IsNot Nothing Then

                       Me.FriendsTableAdapter.Update(Me.ContactsDataSet.Friends)

                       Me.FriendsTableAdapter.Fill(Me.ContactsDataSet.Friends)

                   End If

    The first thing to notice is how I'm declaring the new row - this is telling dotNet what the variable (in my case, "nr") is and when you do, it knows the field names which is the next part where I'm telling it what the values of those fields are.

    Following that, add this new row to the rows in MyDataSet.MyDataTable (pseudo) and then the next part is critical:

    Call the ".EndEdit" method of the BindingSource. You're effectively saying "ok, I'm done now" and until you do, the next part will fail. The next part is asking MyDataSet.MyDataTable "are there any differences between what I have in memory and what's stored in the file?" by using the ".GetChanges". This will return false if you didn't tell the binding source that you're done (.EndEdit)!

    Finally then, if there are changes, update it as shown. Calling the fill method following that is up to you. When you go to delete records though, it's a must!


    Please call me Frank :)

    Monday, July 16, 2012 11:55 PM
  • All of that information would have been set up (including your connection) when you created the TableAdapter in the Data Designer. Can we assume that your EmployInfoTableAdapter has at least a Fill command (SELECT query)? Is the TableAdapter's GenerateDbDirectMethods property set to True?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 1:46 AM
  • Yes, I have the Fill command and I dont see a GeneratedDbDirect Methods property.

    Thanks for the code Frank, I will attempt to utilize that in my code and see if I can get a working product.

    Tuesday, July 17, 2012 2:24 AM
  • "EmployInfoTableAdapter.Update" is a function. Which value does it return?

    Do you call somewhere call Database1DataSet.EmployInfo.AcceptChanges? (you shouldn't)

    ?

    Armin

    Tuesday, July 17, 2012 6:54 AM
  • Open the DataSet/TableAdapter in the Data Designer. Right click on the TableAdapter and select Configure... from the menu. You should see your SQL SELECT statement. Click on the Advanced Options button and make sure the first option, Generate Insert, Update and Delete statements is selected. After closing that dialog if you click on the Finish button it will generate those statements.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 12:05 PM
  • @ Paul... That was already selected when i went through and checked.

    @ Frank... I tried doing it your way as well but still had the same results the programs version of the table had the new information but when I closed down the program and checked the database it wasn't there. I stepped through the program and it definitely stepped into the Update command.

    @ Armin... to my knowledge the Update function is supposed to copy the programs copy of a table to the original copy, and thats all that I know about that.

    Tuesday, July 17, 2012 1:36 PM
  • Are you using the same TableAdapter (EmployInfoTableAdapter) to both view and update the data?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 1:46 PM
  • im using it in different forms, but I assume that its all the same. Is my thinking wrong there?
    Tuesday, July 17, 2012 1:56 PM
  • im using it in different forms, but I assume that its all the same. Is my thinking wrong there?

    Open your second form in design view then look in the components tray at the bottom. Do you see the components in the second form the same as they are on the first form?

    Please call me Frank :)

    Tuesday, July 17, 2012 2:13 PM
  • im using it in different forms, but I assume that its all the same. Is my thinking wrong there?

    I just wanted to make sure that both the SELECT and update queries are using the same TableAdapter, which means the connection string is the same.

    BTW, is the database SQL Server, SQL Server Express (.mdf) or SQL Server Compact Edition (.sdf)?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 2:37 PM
  • The .sdf database and yes Frank they are the Same on both forms.

    Tuesday, July 17, 2012 3:36 PM
  • The .sdf database and yes Frank they are the Same on both forms.

    Paul is an expert (the real deal) with databases - I'll fall back and let him help, but it seems to me that even if you have two tableadapters and two connection strings, it's still referencing the same database file, so when you restart the program (and even before then if you call the fill method), it should be there.

    I just plain don't know! Paul will figure it out though - there's hope yet. :)


    Please call me Frank :)

    Tuesday, July 17, 2012 3:49 PM
  • The .sdf database and yes Frank they are the Same on both forms.


    Where is the .sdf file located?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 4:08 PM
  • Its located in the same folder with all of my form files
    Tuesday, July 17, 2012 6:42 PM
  • Its located in the same folder with all of my form files

    OK so just to verify, is that the only copy? There isn't a copy in the bin folder or bin subfolders of your project?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 6:50 PM
  • Now since I have looked there is a copy in the bin folder

    Tuesday, July 17, 2012 8:10 PM
  • There shouldn't be. I would move it out of the bin folder to another location and then re-run the app. Check the folder after re-running the app to see if the file is back again. If it is, then you need to check the Copy to Output Directory property of the database. Also, make sure that the database in Solution Explorer is pointing to the file in your project directory.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by Frank L. Smith Tuesday, July 17, 2012 10:23 PM
    • Marked as answer by Youen Zen Friday, July 27, 2012 3:33 AM
    Tuesday, July 17, 2012 9:56 PM
  • Alright that seems to be the problem. It copies all the files I use into the Debug folder inside the Bin folder(I'm assuming because i've been debugging the program). So I guess the question now is when I turn this into an execution file will it be directed to the proper data base.

    All the files are pointing to the project directory. And the data base that was in the debug folder had all the data that I added.


    • Edited by dlopks Wednesday, July 18, 2012 1:46 AM
    Wednesday, July 18, 2012 1:31 AM
  • If the files are being copied to the bin Debug or Release subfolder when run through the VS IDE, then this is design-time behavior. The same behavior will not occur after deployment, however; you need to decide where you want to put the database file for deployment so you can set up your connection strings correctly.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 18, 2012 2:16 AM