How to add a new record using Access database with auto number id?
Clash Royale CLAN TAG#URR8PPPHow to add a new record using Access database with auto number id?
My VB.Net code is as follows but my code replaces the previous record instead:
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim cs As OleDbConnection = New OleDbConnection
cs.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + Application.StartupPath + "LoginDB.accdb"
cs.Open()
Dim cd As OleDbCommand = New OleDbCommand("INSERT INTO LoginTable (Name1,Name2,PNum,Age,Sex) VALUES(@name1,@name2,@num,@age,@sex)", cs)
If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Or ComboBox1.Text = "" Then
MsgBox("Enter all the details!")
Else
cd.Parameters.AddWithValue("@name1", TextBox1.Text)
cd.Parameters.AddWithValue("@name2", TextBox2.Text)
cd.Parameters.AddWithValue("@num", TextBox3.Text)
cd.Parameters.AddWithValue("@age", TextBox4.Text)
cd.Parameters.AddWithValue("@sex", ComboBox1.Text)
cd.ExecuteNonQuery()
Me.Hide()
Form3.Show()
End If
An INSERT query is unlikely to replace anything - if it does, OleDB and NET in general is broken. More likely, you are possibly starting over with a new/empty DB each time
– Plutonix
11 hours ago
@AndrewMortimer I have made the access database with an ID field with data type AutoNumber. It does add the record but replaces the previous one i.e ID-01.
– Vaibhav Modi
8 hours ago
No it doesn't replace the previous one. It can't possibly be doing that with an
INSERT statement. Please describe IN DETAIL how you think you are determining that that is happening. Most importantly, what is the Copy to Output Directory property of your data file set to and are you building your project in between adding new records?– jmcilhinney
6 hours ago
INSERT
Copy to Output Directory
On an unrelated note, change your connection string to "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|LoginDB.accdb".
– jmcilhinney
6 hours ago
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Suggest doing your validation first before opening connection etc. Also, wrap the connection and command in a using block to ensure they close. Is your insert failing? I don't see an autonumber column. It should just auto increment as you go.
– Andrew Mortimer
12 hours ago