在asp.net中插入后获取主键（visual basic）

I'm adding a record like this:

我正在添加这样的记录:

    Dim pathString As String = HttpContext.Current.Request.MapPath("Banking.mdb")
    Dim odbconBanking As New OleDbConnection _
             ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" + pathString)
    Dim sql As String
    sql = "INSERT INTO tblUsers ( FirstName, LastName, Address, City, Province, Zip, Phone, UserName, [Password])" & _
              " VALUES ('" & firstName & "', '" & lastName & "', '" & address & _
            "', '" & city & "', '" & province & "', '" & zip & "', '" & phone & "', '" & username & "', '" & password & "');"
    odbconBanking.Open()
    Dim cmd As New OleDbCommand(sql, odbconBanking)
    cmd.ExecuteNonQuery()
    odbconBanking.Close()

The primary key is an autonumber field called UserID. So, how do I get the primary key of the record I just inserted?

主键是名为UserID的自动编号字段。那么,我如何获得刚插入的记录的主键?

Thanks.

5 个解决方案

#1

I believe a parameterized query would look something like this:

我相信参数化查询看起来像这样:

Dim pathString As String = HttpContext.Current.Request.MapPath("Banking.mdb")
Dim odbconBanking As New OleDbConnection _
     ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" + pathString)
Dim sql As String
sql = "INSERT INTO tblUsers ( FirstName, LastName, Address, City, Province, Zip, Phone, UserName, [Password])" & _
      " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
odbconBanking.Open()
Dim cmd As New OleDbCommand(sql, odbconBanking)

//Add Params here
cmd.Parameters.Add(new OdbcParameter("@FirstName", firstName))
cmd.Parameters.Add(new OdbcParameter("@LastName", lastName))
//..etc

//End add Params here

cmd.ExecuteNonQuery()
Dim newcmd As New OleDbCommand("SELECT @@IDENTITY", odbconBanking)
uid = newcmd.ExecuteScalar

odbconBanking.Close()

My syntax might be a bit off as I am more accustomed to using the Sql Server library and not the Odbc library, but that should get you started.

我的语法可能有点偏,因为我更习惯使用Sql Server库而不是Odbc库,但这应该让你开始。

#2

"SELECT @@IDENTITY" just after the insertion should work with Microsoft.Jet Provider.

插入后的“SELECT @@ IDENTITY”应与Microsoft.Jet Provider一起使用。

#3

You'll want to add

你想要添加

;select scope_identity();

after your query, and run executeScalar to get the value back.

在查询之后,运行executeScalar以获取值。

Also, on a completely unrelated note, you really should change your code to use parameterized queries so you don't get caught with SQL injection attacks, or crashes due to inproperly escaped strings concatenated into your sql statement.

另外,在一个完全不相关的注释中,你真的应该改变你的代码以使用参数化查询,这样你就不会被SQL注入攻击或由于不正确转义的字符串连接到你的sql语句而崩溃。

#4

Got it. Here's how the above code looks now:

得到它了。以下是上面的代码现在的样子:

    Dim pathString As String = HttpContext.Current.Request.MapPath("Banking.mdb")
    Dim odbconBanking As New OleDbConnection _
             ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" + pathString)
    Dim sql As String
    sql = "INSERT INTO tblUsers ( FirstName, LastName, Address, City, Province, Zip, Phone, UserName, [Password])" & _
              " VALUES ('" & firstName & "', '" & lastName & "', '" & address & _
            "', '" & city & "', '" & province & "', '" & zip & "', '" & phone & "', '" & username & "', '" & password & "');"
    odbconBanking.Open()
    Dim cmd As New OleDbCommand(sql, odbconBanking)
    cmd.ExecuteNonQuery()
    Dim newcmd As New OleDbCommand("SELECT @@IDENTITY", odbconBanking)
    uid = newcmd.ExecuteScalar

    odbconBanking.Close()

#5

The simplest way would be to generate your uniqueIdentifier value at the code level, add it to your string and send it to the server.

最简单的方法是在代码级别生成uniqueIdentifier值,将其添加到字符串并将其发送到服务器。

Dim sql As String, _
    myNewUserId as variant

myNewUserId = stGuidGen    'this function will generate a new GUID value)'
sql = "INSERT INTO tblUsers ( userId, LastName)" & _
      " VALUES ('" & myNewUserId & "','" & LastName);"

You can check a proposal for the stGuidGen guid generating code here.

您可以在此处查看stGuidGen guid生成代码的提案。

This client-side technique is clear, clean and usefull

这种客户端技术清晰,干净且有用

#1