vb.net - 数据库应用程序连接错误

Question

我不断收到“未建立连接”错误。尽管我以为，用sqlLink.Open和sqlCheck.connection = sqlLink打开Connection不会出现此错误。

Imports System.Data.OleDb
Public Class StockAdd
    Dim path = System.Windows.Forms.Application.StartupPath
    Dim sqlLink As OleDbConnection
    Private Sub StockAdd_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sqlLink = New OleDbConnection
        sqlLink.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\will\Documents\Computing\ComputingProjectDatabase.accdb';")

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sqlCheck As New OleDbCommand
        Dim oleRdr As OleDbDataReader
        sqlLink.Open()
        Try
            Using sqlOrder As New OleDbCommand

                sqlCheck.Connection = sqlLink
                sqlCheck.CommandText = "SELECT count(*) FROM StockSystem WHERE [Stock ID] = @stockID"
                sqlCheck.Parameters.AddWithValue("@stockID", TextBox5.Text)

                oleRdr = sqlCheck.ExecuteReader()
                If oleRdr.HasRows = True Then
                    oleRdr.Read()
                    If oleRdr.Item(0) = 0 Then
                        sqlOrder.CommandText = "INSERT INTO StockSystem ([Stock ID], [Stock Price], [Stock Size], [Stock Quantity], [Stock Category]) VALUES ('" & TextBox5.Text & "','" & TextBox7.Text & "','" & TextBox6.Text & "','" & TextBox8.Text & "','" & TextBox9.Text & "')"
                        sqlOrder.ExecuteNonQuery()
                        MsgBox("Stock Details Stored.")
                    Else
                        MsgBox("Please check you have entered the correct data.")
                    End If
                End If
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        sqlLink.Close()
        Me.Close()
        Stock.Show()

    End Sub
End class

Answer 1

如果我的眼睛没有让我失望，则无需设置sqlOrder命令的连接。因此它无法执行您的sql文本。

请注意，您不能使用sqlLink连接，因为它由数据读取器使用。您需要一个新的连接对象(当然具有相同的连接字符串)

附带说明一下，您的代码很容易受到Sql Injection的攻击。如果您的一个或多个TextBoxes包含一个单引号字符，则可能会遇到严重的问题。

始终使用参数化查询作为用于COUNT()语句的查询