使用 Visual Basic 在文本框中显示来自 MySql 的自动编号

Question

这是针对我正在创建的帮助台票务程序：

当用户单击开始票证时，我希望能够显示来自 mysql 的自动编号，该编号与当前填写的票证相对应，以便用户稍后可以跟踪他们的票证在。我已完成所有设置，但不太确定如何在文本框中显示票证 ID。

现在我已经有了它，以便在表单加载时运行查询以获取下一个票证 ID 号，但我不确定如何将其显示为文本框中的文本。

    'QUERY FOR NEW ID TICKET NUMBER   
Private Sub Suggestions_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    Dim idQuery As String = "SELECT MAX(itID)+1 AS Expr1 FROM(TableName)"

    With cmd
        .Connection = conn
        .CommandText = idQuery
        .CommandType = CommandType.Text
    End With

    Try
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
        Me.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
        conn.Close()
    End Try

现在我设法将其更改为这样，但我仍然挂断，因为我不确定它是否正确：

Dim conn As New MySqlConnection(connStr)
Dim cmd As New MySqlCommand()
Dim Reader As MySqlDataReader
Dim submitteddate As String

'QUERY FOR NEW ID TICKET NUMBER   
Private Sub Suggestions_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    Try
        Dim idQ As String = "INSERT INTO tblit (itID, ersBugIssue, ersSubject, ersBugCategory, ersBugDate, ersBugSubmittedby, ersBugOrHelp )" & _
                                "VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL);"
        Dim cmd As new SQLCommand()
        With cmd
            .Connection = conn
            .CommandText = idQ
        End With

        conn.Open()
        cmd.ExecuteNonQuery()
        cmd.CommandText = "SELECT LAST_INSERT_ID() as NewTicketID"
        Dim Reader As SqlDataReader = cmd.ExecuteReader()
        Reader.Read()
        MsgBox(Reader("NewTicketID"))
        Reader.Close()

        conn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
End Sub

Answer 1

“SELECT MAX(itID)+1 AS Expr1 FROM(TableName)”

这是一个坏主意，您很快就会遇到并发问题。
您应该运行以下 SQL 语句。

INSERT INTO tablename (ID, morefields) 
  VALUES (NULL, real_values_or_place_holders);
SELECT LAST_INSERT_ID() as NewTicketID;
-- After the ticket is complete
UPDATE tablename SET morefields = <thedata> WHERE id = NewTicketID 

-- If the ticket gets cancelled:
DELETE FROM tablename WHERE id = NewTicketID

如果您这样做，您将永远不会遇到重复或错误 TicketID 发出的问题。

如果您执行select，则不能说它是非查询。

使用以下代码

Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "SELECT LAST_INSERT_ID() AS NewTicketID"
Dim reader As SqlDataReader = cmd.ExecuteReader()
reader.Read
MsgBox(reader("NewTicketID"))