从数据库中删除时，SQL 逻辑错误或缺少数据库没有这样的表 [英] SQL logic error or missing database no such table when deleting from database

问题描述

我的连接:

Public con As SQLiteConnection = New SQLiteConnection("Data Source=trancebeats.s3db")

我在表单加载时打开连接

and I open connection when form load

插入新值:

Public Sub InsertNewCatSub()
        prUpdate.Value = 0
        prUpdate.Maximum = 1
        lblUpdateResults.Text = prUpdate.Value & " of " & prUpdate.Maximum & " Checked"
        lblUpdateResults.ForeColor = Color.Red
        TotUpd = 0
        TotNew = 0
        Dim filename1 = Path.GetFileNameWithoutExtension(NewCat)
        Dim filename2 = filename1.Replace("[", "")
        Dim filename3 = filename2.Replace("]", "")
        Dim filename4 = Path.GetDirectoryName(NewCat)
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        Dim Querytxt As String = "Select * from trancebeats Where Cat = '" & filename3 & "' Group By Cat"
        Dim Adp As SQLiteDataAdapter = New SQLiteDataAdapter
        Dim Cmd As SQLiteCommand = New SQLiteCommand
        Dim TableNewCat As New DataTable
        TableNewCat.Rows.Clear()
        Dim i As Integer

        With Cmd
            .CommandText = Querytxt
            .Connection = con
        End With

        With Adp
            .SelectCommand = Cmd
            .Fill(TableNewCat)
        End With
        If TableNewCat.Rows.Count > 0 Then
            For i = 0 To TableNewCat.Rows.Count - 1
                If filename3 = TableNewCat.Rows(i)("Cat") Then
                    MsgBox("Cat. Already Exists!!!")
                Else
                    GoTo write_new_cat
                End If
            Next
        Else
write_new_cat:
            Try
                Dim file As String = NewCat
                Dim filepath As String = file.Substring(0, file.LastIndexOf("\"))
                Dim lines As New List(Of String)
                lines.AddRange(System.IO.File.ReadAllLines(NewCat))

                For co As Integer = 0 To lines.Count - 1
                    If lines(co).Length > 0 Then
                        Try
                            Dim a1() As String = lines(co).Split("|")

                            Dim Cat As String = a1(0)
                            Dim Title As String = a1(1)
                            Dim Artist As String = a1(2)
                            Dim Album As String = a1(3)
                            Dim Year As String = a1(4)
                            Dim Genre As String = a1(5)
                            Dim Publisher As String = a1(6)
                            Dim Site As String = a1(7)
                            Dim MP3File As String = filepath & "\" & a1(8)
                            Dim Cover As String = filepath & "\[" & Cat & "].jpg"
                            Dim CmdwrtN As SQLiteCommand = New SQLiteCommand
                            With CmdwrtN
                                .CommandText = "Insert Into trancebeats Values('" _
                                & Cat & "', '" & Title & "', '" & Artist & "', '" & Album & "', " & Year & ", '" & Genre & "', '" & Publisher & "', '" & Site & "', '" & filepath.Replace("'", "''") & "', '" & Cover.Replace("'", "''") & "', '" & MP3File.Replace("'", "''") & "')"
                                .Connection = con
                                .ExecuteScalar()
                            End With
                        Catch ex As Exception
                            MsgBox(ex.Message & NewCat)
                        End Try
                    End If
                Next
                TotNew += 1
            Catch ex As Exception
                MessageBox.Show(ex.ToString, "Error Reading File " & NewCat)
            End Try
            btnClear.Enabled = True
        End If
        con.Close()
        prUpdate.Value += 1
        lblUpdateResults.Text = prUpdate.Value & " of " & prUpdate.Maximum & " Checked"
    End Sub

从数据库中删除所有内容:

Delete everything from database:

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
    If con.State = ConnectionState.Closed Then
        con.Open()
    End If

    Dim Querytxt As String = "Delete from trancebeats"
    Dim Cmd As SQLiteCommand = New SQLiteCommand
    Try
        With Cmd
            .CommandText = Querytxt
            .Connection = con
            .ExecuteScalar()
            MsgBox("Data Cleared")
            Dim pn As Panel
            pn = Me.Controls.Item("pnNew")
            Me.Controls.Remove(pn)
            flResults.Controls.Clear()
            btnClear.Enabled = False
        End With
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

当我清除数据库时我从文件中插入一个新行然后当我尝试再次清除数据库时它给了我一个错误:

when I clear the database I insert a new row from file then when I try to clear database agail it gives me an error:

SQL 逻辑错误或缺少数据库没有这样的表:trancebeats

SQL logic error or missing database no such table: trancebeats

推荐答案

不确定这是否对您有帮助，但我遇到了类似的问题:'没有这样的表:***'

Not sure if this will help you, but I ran into a similar problem: 'no such table: ***'

就我而言，事实证明我的连接字符串会将数据库指定为相对路径:

In my case it turned out to be the fact that my connection string would specify database as a relative path:

'URI=file: mydatabase.sqlite'

一切正常，直到我在我的应用程序中使用OpenFileDialog".这会更改当前目录，并抛出连接字符串.一个快速修复，是在连接字符串中使用数据库的绝对路径:

It all worked fine, until I would use an 'OpenFileDialog' inside my application. That changes current directory, and throws off the connection string. A quick fix, is using absolute path for your database inside the connection string:

'URI=file: C:\Test\mydatabase.sqlite'

这篇关于从数据库中删除时，SQL 逻辑错误或缺少数据库没有这样的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！