从 MS Access 数据库中检索最后插入的 ID [英] Retrieving last inserted ID from MS Access database
问题描述
我有以下一组用于子程序的代码.它使用包含表单中提供的数据将一行插入到 MSAccess 数据库中.我想要做的是获取此添加记录的 ID 号,以便可以为成功添加时调用的窗口的属性设置它.我试着查了一下,但我得到了一些关于 @@IDENTITY
的信息,但它使用了一种完全不同的连接方式.
I have the following set of code for a Sub program. It's inserting a row into a MSAccess Database using data provided in the containing form. What I would like to do is grab the ID number of this added record so that it can be set for a property of a window that is invoked when successfully added. I tried looking this up but I get something about @@IDENTITY
but it's using an entirely different way of connecting.
Private Sub CreateTournament_Click(sender As System.Object, e As System.EventArgs) Handles CreateTournament.Click
' TODO: Check the form for errors, or blank values.
' Create the tournament in the database, add the values where needed. Close the form when done.
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Master.mdb'")
cn.Open()
str = "insert into Tournaments (SanctioningID,TournamentName,TournamentVenue,TournamentDateTime,TournamentFirstTable,Game,Format,OrganizerID) values(" _
& CInt(SanctioningIDTxt.Text) & ",'" & Trim(TournamentNameTxt.Text) & "','" & _
"1" & "','" & EventDateTimePck.Value & "','" & TableFirstNumberNo.Value & "','" & GameList.SelectedIndex & "','" & FormatList.SelectedIndex & "','" & Convert.ToInt32(ToIDTxt.Text) & "')"
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(Str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
'displays number of records inserted
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
Me.Close()
Dim n As New TournamentWindow ' Open a new Tournament window if everything is successful
n.TournID = Counter '<< This should be set to the ID of the most recently inserted row
n.Show(HomeForm)'Invoke the form and assign "HomeForm" as it's parent.
End Sub
推荐答案
假设您在 Tournaments 表中有一个自动递增列,您可以执行SELECT @@IDENTITY"来获取最后插入的记录的 id.
Assuming you have an auto increment column in the Tournaments table, you can do a "SELECT @@IDENTITY" to get the id of the last inserted record.
顺便说一句,SanctioningIDTxt.Text 是唯一的吗?如果是这样,你不能使用它吗?
BTW, is the SanctioningIDTxt.Text unique? If so, can't you use that?
这篇关于从 MS Access 数据库中检索最后插入的 ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!