mysql - 创建自定义 ID 时出现 ExecuteScalar 错误

Question

我目前在创建自定义 ID 时遇到 executeScalar 问题。错误是

InvalidOperationException was unhandled by user code.

Message=Connection must be valid and open.

下面是我的代码:

Dim str As String = "select isnull(max(idcr_record),0)+1 from cr_record where Emplid = '" & Session("Emplid") & "'"
Dim com As New MySqlCommand()
'this line was the error
'
Dim count As Integer = Convert.ToInt32(com.ExecuteScalar())

If count = 1 Then
    'insert if no record found
    '
    Dim cc As New MySqlCommand("insert into cr_record values (" & count & "," & Session("Emplid") & ")")
    cc.ExecuteNonQuery()
Else
    'update becoz record already exist
    '
    Dim upd As String = "update cr_record set idcr_record = " & count & " where Emplid = " & Session("Emplid") & " "
    Dim cc As New MySqlCommand(upd)
    cc.ExecuteNonQuery()
End If

Dim length As Integer = 0
Dim val As String = Convert.ToString(count)
length = val.Length
If length = 1 Then
    val = "CR " + "00000" & Convert.ToString(count)
End If
If length = 2 Then
    val = "CR " + "0000" & Convert.ToString(count)
End If
If length = 3 Then
    val = "CR " + "000" & Convert.ToString(count)
End If
If length = 4 Then
    val = "CR " + "00" & Convert.ToString(count)
End If
If length = 5 Then
    val = "CR " + "0" & Convert.ToString(count)
End If
cr_id = "" & val

Answer 1

至少，您需要将 SQL 命令字符串和 SqlConnection 对象传递给 SqlCommand 对象，然后才能执行命令:

Dim str As String = "select isnull(max(idcr_record),0)+1 from cr_record where Emplid = '" & Session("Emplid") & "'"
Dim conn As New MySqlConnection
conn.ConnectionString = "....."

'pass your SQL command string and MySqlConnection object to MySqlCommand
'
Dim com As New MySqlCommand(str, conn)

conn.Open()
Dim count As Integer = Convert.ToInt32(com.ExecuteScalar())