I am attempting to create a new record from vb.net to an msaccess table, which i am able to do, but i have to add in the next consecutive ID number for it to actually save. For instance, if the next ID in the Access DB is 4, i have to type in 4 in the id textfield on my form. Code below:
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim sqlinsert As String
' We use the INSERT statement which tells our program to add the information
' from the Forms Text fields into the Databases columns.
sqlinsert = "INSERT INTO Table1(Title, YearofFilm, Description, Field1, ID)" & _
"VALUES(@Title, @YearofFilm, @Description, @Field1, @ID)"
Dim cmd As New OleDbCommand(sqlinsert, con1)
' This assigns the values for our columns in the DataBase.
' To ensure the correct values are written to the correct column
cmd.Parameters.Add(New OleDbParameter("@Title", TextBox1.Text))
cmd.Parameters.Add(New OleDbParameter("@YearofFilm", Convert.ToInt32(TextBox2.Text)))
cmd.Parameters.Add(New OleDbParameter("@Description", TextBox3.Text))
cmd.Parameters.Add(New OleDbParameter("@Field1", TextBox4.Text))
cmd.Parameters.Add(New OleDbParameter("@ID", Convert.ToInt32(TextBox5.Text)))
' This is what actually writes our changes to the DataBase.
' You have to open the connection, execute the commands and
' then close connection.
con1.Open()
cmd.ExecuteNonQuery()
con1.Close()
' This are subs in Module1, to clear all the TextBoxes on the form
' and refresh the DataGridView on the MainForm to show our new records.
ClearTextBox(Me)
RefreshDGV()
Me.Close()
End Sub
How can i tell textbox5 which is the ID field, to be the next number in the access db?
Open your Access database, show the structure of your table and change the ID field type from numeric to AutoNumber.
Now your code don't need to pass anything to Access because the number will be handled automatically from Access.
You could just add these lines to your code to get back the number assigned by Access to your field
Dim sqlinsert As String
sqlinsert = "INSERT INTO Table1(Title, YearofFilm, Description, Field1)" & _
"VALUES(@Title, @YearofFilm, @Description, @Field1)"
Dim cmd As New OleDbCommand(sqlinsert, con1)
cmd.Parameters.Add(New OleDbParameter("@Title", TextBox1.Text))
cmd.Parameters.Add(New OleDbParameter("@YearofFilm", Convert.ToInt32(TextBox2.Text)))
cmd.Parameters.Add(New OleDbParameter("@Description", TextBox3.Text))
cmd.Parameters.Add(New OleDbParameter("@Field1", TextBox4.Text))
con1.Open()
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
cmd.CommandText = "SELECT @@IDENTITY"
Dim assignedID = Convert.ToInt32(cmd.ExecuteScalar())
' Eventually
TextBox5.Text = assignedID.ToString
con1.Close()
......
See also How to retrieve last autoincremented value in MS-Access like @@Identity in Sql Server
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments