mysql - 添加到数据库时出现问题

Question

我的程序有一种通过文本文件将字典添加到数据库的方法。所述文本文件由一个单词、它是什么形式的单词(即名词、动词等)以及相关图像的文件名组成，全部格式为:

word#type#filename
word2#type#filename2

等等。为了避免重复单词条目，我将 MySqlDataReader 与查询结合使用来遍历所有行，然后添加尚未添加的任何行。代码如下所示:

Private Sub btnCreateBank_Click(sender As Object, e As EventArgs) Handles btnCreateBank.Click
    Dim newOpenDialog As New OpenFileDialog
    Dim newFileStream As FileStream = Nothing
    newOpenDialog.InitialDirectory = GetFolderPath(SpecialFolder.MyDocuments)
    newOpenDialog.Filter = "Text Files (*.txt)|*.txt|All Files (*.*)|*.*"
    newOpenDialog.FilterIndex = 1
    newOpenDialog.ShowDialog()
    Try
        newFileStream = newOpenDialog.OpenFile()
        If (newFileStream IsNot Nothing) Then
            Dim sr As New StreamReader(newFileStream)
            Dim fileContents As String = sr.ReadToEnd()
            Dim fileContentsArray() As String = Split(fileContents, vbNewLine)
            Dim fullSplitArray As New List(Of String)
            For i As Integer = 0 To fileContentsArray.Length - 1
                fullSplitArray.AddRange(Split(fileContentsArray(i).ToString, "#"))
            Next
            For j As Integer = 0 To fullSplitArray.Count - 1 Step 3
                Dim connString As String = "server=localhost;user=root;database=jakub_project;port=3306;password=password;"
                Try
                    Using conn As New MySqlConnection(connString)
                        conn.Open()
                        Dim command As New MySqlCommand("SELECT COUNT(word) FROM words WHERE word=@inputWord", conn)
                        command.Prepare()
                        command.Parameters.AddWithValue("@inputWord", fullSplitArray.Item(j))
                        Dim dataReader As MySqlDataReader = command.ExecuteReader()
                        While dataReader.Read()
                            If dataReader.Item(0) = 1 Then
                                MsgBox(fullSplitArray.Item(j) & " added to system.")
                            Else
                                Using conn2 As New MySqlConnection(connString)
                                    conn2.Open()
                                    Dim addCmd As New MySqlCommand("INSERT INTO words(word, wordType, wordFilename) VALUES(@inputWord, @inputType, @inputFilename);", conn2)
                                    addCmd.Prepare()
                                    addCmd.Parameters.AddWithValue("@inputWord", fullSplitArray.Item(j))
                                    addCmd.Parameters.AddWithValue("@inputType", fullSplitArray.Item(j + 1))
                                    addCmd.Parameters.AddWithValue("@inputFilename", fullSplitArray.Item(j + 2))
                                    addCmd.ExecuteNonQuery()
                                    MsgBox(fullSplitArray.Item(j) & " added to system.")
                                    conn2.Close()
                                End Using
                            End If
                        End While
                        dataReader.Close()
                        conn.Close()
                    End Using
                Catch ex As Exception
                    MsgBox("Error: " & ex.ToString())
                End Try
            Next
        End If
    Catch ex As Exception
        MsgBox("Error: " & ex.ToString())
    End Try
End Sub

我没有包含第二个连接的先前变体第一次就给了我正确的结果，添加了八行。然而，所有连续的尝试都给出了错误，这些错误源于据称缺乏第二个连接。但现在，使用此子例程的所有尝试都会输出这样的表。

+--------+----------------------+----------------------+--------------------------+
| wordID | word                 | wordType             | wordFilename             |
+--------+----------------------+----------------------+--------------------------+
|      1 | acorn                | noun                 | acorn.jpg                |
|      2 | beach                | noun                 | beach.jpg                |
|      3 | chicken              | noun                 | chicken.jpg              |
|      4 | dance                | verb                 | dance.jpg                |
|      5 | elbow                | noun                 | elbow.gif                |
|      6 | fight                | verb                 | fight.gif                |
|      7 | grow                 | verb                 | grow.jpg                 |
|      8 | hat                  | noun                 | hat.jpg                  |
|     11 | acorn noun acorn.jpg | beach noun beach.jpg | chicken noun chicken.jpg |
|     12 | dance verb dance.jpg | elbow noun elbow.jpg | fight verb fight.gif     |
+--------+----------------------+----------------------+--------------------------+

我想要的是加载第一组行中看到的数据，而不是我现在在底部两行中收到的数据。但我不确定问题的根源在哪里。

错误消息也没有真正告诉我发生了什么。出现的第一条错误消息告诉我数据对于尝试将其插入的列来说太长。第二个错误告诉我，For 循环中的整数 j 超出范围，现在发生这种情况是因为系统似乎读取整个字符串而不是三个子字符串.

Answer 1

如果我正确理解你的话，在我看来，您的文本文件分割序列可能存在缺陷。在您发布的代码中哪一个不明显，因为您可能在找到它后纠正了错误？

至于错误...第一个错误告诉您相关字段的数据(您没有提及它是哪个字段)太大，无法字段到分配的空间中。这也可以量化为由不正确的 split 引起的在你的分割序列中。

至于第二个错误尚不清楚，但我认为在纠正拆分问题后不会出现该错误？

最后，建议移动您的 Command.Prepare，以便它在您添加所有命令参数之后出现。

额外信息后的结论

在阅读了您下面的评论后，我仍然认为问题的最初原因在于您的拆分例程。

使用watch工具查看 split 结果后，很明显发生了什么。您可以看到下面的监 window 口

此外，使用两个单独的例程来检查单词是否存在并在不存在时添加新单词似乎更有意义。

通过将它们从主处理 block 中分离出来，可以更轻松地查看正在发生的情况......

所以我已经这样做并测试了它，它似乎工作得很好。主要区别在于，我跳过了读取数据文件的步骤，并模拟了字符串中的内容......

Private Sub btnCreateBank_Click(sender As Object, e As EventArgs) Handles btnCreateBank.Click
'
Dim counta As Integer = 0
Dim fullSplitArray As New List(Of String)
Dim fileContents As String = ""
Dim fileContentsArray() As String
Dim tmpWord As String = Nothing
Dim tmpType As String = Nothing
Dim tmpFile As String = Nothing
Dim Test As Boolean = False
'
Try
  fileContents = "1#acorn#noun#acorn.jpg" & vbNewLine & "2#beach#noun#beach.jpg" & vbNewLine & "3#chicken#noun#chicken.jpg" & vbCrLf & "4#dance#verb#dance.jpg" & vbNewLine & "5#elbow#noun#elbow.gif" & vbNewLine & "6#fight#verb#fight.gif" & vbNewLine & "7#grow#verb#grow.jpg" & vbNewLine & "8#hat#noun#hat.jpg"
  fileContentsArray = Split(fileContents, vbNewLine)
  For i As Integer = 0 To fileContentsArray.Length - 1
    fullSplitArray.AddRange(Split(fileContentsArray(i).ToString, "#"))
  Next
  For j As Integer = 0 To fullSplitArray.Count - 1 Step 4
    Test = False
    Try
      Test = IsWordAlreadyListed(fullSplitArray(j + 1), tmpWord, tmpType, tmpFile)
      If Test Then
        MsgBox(fullSplitArray.Item(j + 1) & " already listed in system (" & Trim(tmpWord) & ", " & Trim(tmpType) & ", " & Trim(tmpFile) & ")")
      Else
        Test = AddNewRow(fullSplitArray(j + 1), fullSplitArray(j + 2), fullSplitArray(j + 3))
      End If
    Catch ex As Exception
      MsgBox("Error: " & ex.ToString())
    End Try
  Next
Catch ex As Exception
  MsgBox("Error: " & ex.ToString())
End Try
Test = Nothing
'
End Sub


Function IsWordAlreadyListed(ByVal ParamWord As String, ByRef pRtnWord As String, ByRef pRtnType As String, ByRef pRtnFile As String) As Boolean
'
'-> Locals
Dim iwalConn As Data.SqlClient.SqlConnection = Nothing
Dim iwalCmd As Data.SqlClient.SqlCommand = Nothing
Dim iwalAdapter As Data.SqlClient.SqlDataReader = Nothing
Dim iwalQuery As String = Nothing
'-> Init
IsWordAlreadyListed = False
iwalConn = New System.Data.SqlClient.SqlConnection()
iwalConn.ConnectionString = "YOUR-CONNECTION-STRING-HERE"
'-> Process Request
If Trim(paramword) <> "" Then
  iwalQuery = "SELECT * FROM words WHERE word='" & Trim(ParamWord) & "'"
  '-> Query Databanks
  iwalCmd = New Data.SqlClient.SqlCommand(iwalQuery, iwalConn)
  If iwalCmd.Connection.State = Data.ConnectionState.Closed Then iwalCmd.Connection.Open()
  iwalAdapter = iwalCmd.ExecuteReader(Data.CommandBehavior.CloseConnection)
  If iwalAdapter.HasRows Then
    iwalAdapter.Read()
    pRtnWord = iwalAdapter.GetValue(iwalAdapter.GetOrdinal("word"))
    pRtnType = iwalAdapter.GetValue(iwalAdapter.GetOrdinal("wordType"))
    pRtnFile = iwalAdapter.GetValue(iwalAdapter.GetOrdinal("wordFilename"))
    IsWordAlreadyListed = True
  End If
  If iwalCmd.Connection.State = Data.ConnectionState.Open Then iwalCmd.Connection.Close()
Else
  MsgBox("Error: Invalid or missing word parameter!")
End If
'-> tidy up
iwalCmd = Nothing
iwalAdapter = Nothing
iwalQuery = Nothing
iwalConn = Nothing
'
End Function

Function AddNewRow(ByVal ParamWord As String, ByVal ParamType As String, ParamFile As String) As Boolean
'
AddNewRow = False
Dim arnConn As System.Data.SqlClient.SqlConnection = Nothing
Dim arnConnString As String = "YOUR CONNECTION STRING HERE"
arnConn = New System.Data.SqlClient.SqlConnection(arnConnString)
arnConn.Open()
Try
  Using arnConn
    Dim addCmd As New System.Data.SqlClient.SqlCommand("INSERT INTO words(word, wordType, wordFilename) VALUES(@inputWord, @inputType, @inputFilename);", arnConn)
    addCmd.Parameters.AddWithValue("@inputWord", ParamWord)
    addCmd.Parameters.AddWithValue("@inputType", ParamType)
    addCmd.Parameters.AddWithValue("@inputFilename", ParamFile)
    'addCmd.Prepare()
    addCmd.ExecuteNonQuery()
    MsgBox(ParamWord & " added to system.")
    AddNewRow = True
  End Using
Catch ex As Exception
  MsgBox("Error: " & ex.ToString())
Finally
  arnConn.Close()
End Try

End Function