Results 1 to 5 of 5

Thread: [RESOLVED] Information not saving to Database

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2017
    Posts
    40

    Resolved [RESOLVED] Information not saving to Database

    Hi, I am a student and i am trying to create a Library Management system for my project. I have created a local database in visual studio 2015 to save information from the tables i have created. the database is (my sql server database file..MDF). I have created a connection string and a command string to save and execute queries on my database. I am having a trouble saving information to my customer Table. i have managed to get the information to save into my Booktable but can't get it to save in my customer table. For some odd reason the auto increment won't work for the user ID and i get a error stating the customer table does not accept null values for the column User ID. The user ID is a primary key and should not accept null values but the auto incrementer should generate a new number when adding a new record/row but it does not work.

    So can you help me save the information into my customer table and help me solve the problem of getting my auto incrementer to work for the USER ID column.

    Here is the code for my customer table
    vb.net code:
    Code:
    Imports System.Data.Sql
    Imports System.Data.SqlClient
    Public Class CustomerTable
        Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='C:\Users\Eugen\Downloads\Library management System (lastestone)\Library management System (1)\Library management System\Library management System\Database1.mdf';Integrated Security=True")
        Dim cm As New SqlCommand
        Private Sub CustomerTableBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles CustomerTableBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.CustomerTableBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.BookTableSQL)
    
        End Sub
    
        Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'BookTableSQL.CustomerTable' table. You can move, or remove it, as needed.
            Me.CustomerTableTableAdapter.Fill(Me.BookTableSQL.CustomerTable)
    
        End Sub
    
        Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
            cn.Open()
            cm.Connection = cn
            Me.Validate()
            Me.CustomerTableBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.BookTableSQL)
            Try
                CustomerTableBindingSource.AddNew()
                User_NameTextBox.Select()
                cm.CommandText = " insert into [dbo].[CustomerTable] INSERT INTO [dbo].[CustomerTable] ([Book ID], [User Name], [Check in date], [Chexk out date], [Copies], [Transactions]) VALUES ( '" & Book_IDTextBox.Text & "' , '" & User_NameTextBox.Text & "' , '" & Check_in_dateDateTimePicker.Value.Date & "' , '" & Chexk_out_dateDateTimePicker.Value.Date & "', '" & CopiesTextBox.Text & "', '" & TransactionsTextBox.Text & "'  )"
                cm.ExecuteNonQuery()
                cn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
    
            End Try
        End Sub
    couldn't find the tags for VB can you post it for me please
    Attached Images Attached Images   
    Last edited by Srut; Nov 19th, 2017 at 12:14 AM. Reason: trying use format tags

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Information not saving to Database

    Please use appropriate formatting tags when posting code for readability.
    vb.net Code:
    1. Imports System.Data.Sql
    2. Imports System.Data.SqlClient
    3. Public Class CustomerTable
    4.     Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='C:\Users\Eugen\Downloads\Library management System (lastestone)\Library management System (1)\Library management System\Library management System\Database1.mdf';Integrated Security=True")
    5.     Dim cm As New SqlCommand
    6.     Private Sub CustomerTableBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles CustomerTableBindingNavigatorSaveItem.Click
    7.         Me.Validate()
    8.         Me.CustomerTableBindingSource.EndEdit()
    9.         Me.TableAdapterManager.UpdateAll(Me.BookTableSQL)
    10.  
    11.     End Sub
    12.  
    13.     Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    14.         'TODO: This line of code loads data into the 'BookTableSQL.CustomerTable' table. You can move, or remove it, as needed.
    15.         Me.CustomerTableTableAdapter.Fill(Me.BookTableSQL.CustomerTable)
    16.  
    17.     End Sub
    18.  
    19.     Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
    20.         cn.Open()
    21.         cm.Connection = cn
    22.         Me.Validate()
    23.         Me.CustomerTableBindingSource.EndEdit()
    24.         Me.TableAdapterManager.UpdateAll(Me.BookTableSQL)
    25.         Try
    26.             CustomerTableBindingSource.AddNew()
    27.             User_NameTextBox.Select()
    28.             cm.CommandText = " insert into [dbo].[CustomerTable] INSERT INTO [dbo].[CustomerTable] ([Book ID], [User Name], [Check in date], [Chexk out date], [Copies], [Transactions]) VALUES ( '" & Book_IDTextBox.Text & "' , '" & User_NameTextBox.Text & "' , '" & Check_in_dateDateTimePicker.Value.Date & "' , '" & Chexk_out_dateDateTimePicker.Value.Date & "', '" & CopiesTextBox.Text & "', '" & TransactionsTextBox.Text & "'  )"
    29.             cm.ExecuteNonQuery()
    30.             cn.Close()
    31.         Catch ex As Exception
    32.             MessageBox.Show(ex.Message)
    33.  
    34.         End Try
    35.     End Sub

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Information not saving to Database

    When people think that data is not being saved to their database, it almost always is and the reason they think it's not is almost always the same. Follow the first link in my signature below to learn how to manage local data files. Post back if that doesn't address the problem.

    By the way, one simple way to confirm that data is being saved is to test the value returned by UpdateAll. If no exception is thrown and that value is not zero then data was saved.

  4. #4

    Thread Starter
    Member
    Join Date
    Nov 2017
    Posts
    40

    Re: Information not saving to Database

    Sorry about the code format and thank you for that bit of information once i changed the setting to "copy if newer" the data was saved to the data table. I also had to delete the data source and all the information binded to it in my forms and remake my forms in order to get the auto increment to work correctly. Now i am having another problem with my foreign key. i have made a relationship between my two tables but i continue to get a error in my program it say

    "An unhandled exception of type 'System.Data.InvalidConstraintException' occurred in System.Data.dll
    Additional information: ForeignKeyConstraint FK_BookTable_ReservationTable requires the child key values (1) to exist in the parent table."

    but in my parent table which is the Book table i have an entry saved as 1 for the Book iD. Do you have any idea as to why i can't get my reservation table to see the Book ID as the foreign key from my Book Table. I have an entry 1 for the Book ID in my reservation table which is the foreign key for that table and also have a record for Book ID in my Book Table as 1 nut i keep getting a error? Any suggestion would really be appreciated.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Information not saving to Database

    Quote Originally Posted by Srut View Post
    Sorry about the code format and thank you for that bit of information
    Most people don't realise how to use the forum because they don't look around first. I probably didn't when I first joined either. Code formatting is important though, because it makes it so much easier to read.
    Quote Originally Posted by Srut View Post
    once i changed the setting to "copy if newer" the data was saved to the data table.
    It already was being saved. You just didn't realise it because the database that it was saved to was overwritten with a fresh copy of the source file each time you made a code changed and built again. If you had run the project twice without changing any code then you'd have seen the database changes retained even before.
    Quote Originally Posted by Srut View Post
    I also had to delete the data source and all the information binded to it in my forms and remake my forms in order to get the auto increment to work correctly.
    You probably didn't need to do that. You can re-run the configuration wizard on an existing data source to refresh it from the database.
    Quote Originally Posted by Srut View Post
    Now i am having another problem with my foreign key.
    If the question that is the topic of this thread has been answered then you should use the Thread Tools menu to mark the thread Resolved. If you have a new question on a different topic then you should start a new thread for it. One topic per thread and one thread per topic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width