-
Nov 18th, 2017, 01:30 PM
#1
Thread Starter
Member
[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
Last edited by Srut; Nov 19th, 2017 at 12:14 AM.
Reason: trying use format tags
-
Nov 18th, 2017, 06:52 PM
#2
Re: Information not saving to Database
Please use appropriate formatting tags when posting code for readability.
vb.net 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
-
Nov 18th, 2017, 06:54 PM
#3
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.
-
Nov 18th, 2017, 11:49 PM
#4
Thread Starter
Member
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.
-
Nov 19th, 2017, 06:11 PM
#5
Re: Information not saving to Database
Originally Posted by Srut
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.
Originally Posted by Srut
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.
Originally Posted by Srut
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.
Originally Posted by Srut
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|