OleDbException 是来自子句的未处理的语法错误 [英] OleDbException was unhandled syntax error from clause

问题描述

Imports System.Data.OleDb
Imports System.Data


Public Class Form1
    Dim connection As New OleDbConnection(My.Settings.dataConnectionString)
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If TextBox1.Text = Nothing Or TextBox2.Text = Nothing Then
            MsgBox("Enter credentials ", MsgBoxStyle.Exclamation)
        Else
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim cmd As New OleDbCommand("select count (*) from log in where last name=? and ID=?", connection)
            cmd.Parameters.AddWithValue("@1", OleDbType.VarChar).Value = TextBox1.Text
            cmd.Parameters.AddWithValue("@2", OleDbType.VarChar).Value = TextBox2.Text
            Dim count = Convert.ToInt64(cmd.ExecuteScalar())


            If (count > 0) Then
                MsgBox("log in succeed", MsgBoxStyle.Information)
            Else
                MsgBox("account not found check credentials", MsgBoxStyle.Critical)


            End If

        End If


    End Sub
End Class

推荐答案

将您的数据库对象保存在使用它们的本地，以便您可以控制何时关闭和处理它们.Using...End 即使出现错误，使用块也会为你做到这一点.

Keep your database objects local to where they are used so you can control when they are closed and disposed. Using...End Using blocks will do this for you even if there is an error.

请注意，我们在 Using 块之外声明了 count.它是在块内声明的，它在 End Using 之后将不可见.

Notice that we declare count outside of the Using bock. It it was declared inside the block it would not be visible after the End Using.

我们仅在连接关闭并由 End Using 处理后才显示消息框.连接是有限的资源，应该在最后一刻打开并尽快关闭.

We only show message boxes after the connection is closed and disposed by the End Using. Connections are limited resources and should be opened at the last minute and closed as soon as possible.

我在猜测一个问题.当我看到 ID 时，我会想到一个数字类型.检查您的数据库以查看它是什么类型.如果它是一个整数，那么您正在传递一个字符串值.类型不匹配！

I am guessing at one problem. When I see ID, I think of a numeric type. Check your database to see what type it is. If it is an integer then you were passing a string value. Type Mismatch!

在 sql 语句中，您需要用方括号 [ ] 中的空格将标识符括起来.语法错误！

In the sql statement you need to surround identifiers with spaces in square brackets [ ]. Syntax Error!

另一个问题是您对 .AddWithValue 的使用.第二个参数是一个对象，它是值.您将 .Add(String, OleDbType).Value 方法与 .AddWithValue 方法混合在一起.

Another problem is your usage of .AddWithValue. The second parameter is an Object that is the value. You sort of mixed the .Add(String, OleDbType).Value method with the .AddWithValue method.

我很高兴您使用参数，这当然是最佳实践.

I was very glad that you are using Parameters which is certainly best practices.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If TextBox1.Text = Nothing Or TextBox2.Text = Nothing Then
        MsgBox("Enter credentials ", MsgBoxStyle.Exclamation)
        Return
    End If
    Dim count As Long
    Using Connection As New OleDbConnection(My.Settings.dataConnectionString)
        Using cmd As New OleDbCommand("select count (*) from [log in] where [last name]=@LName and ID=@ID", Connection)
            cmd.Parameters.Add("@LName", OleDbType.VarChar).Value = TextBox1.Text
            cmd.Parameters.Add("@ID", OleDbType.Integer).Value = CInt(TextBox2.Text)
            Connection.Open()
            count = CLng(cmd.ExecuteScalar())
        End Using
    End Using
    If count > 0 Then
        MsgBox("log in succeed", MsgBoxStyle.Information)
    Else
        MsgBox("account not found check credentials", MsgBoxStyle.Critical)
    End If
End Sub

这篇关于OleDbException 是来自子句的未处理的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！