我目前收到此错误，系统无效操作异常：'Fill: SelectCommand.Connection property has not been initialized.'

Question

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.DataTable
Public Class Form1
Private Sub btnlogin_Click(sender As Object, e As EventArgs) Handles btnlogin.Click
Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersKatlegoDocumentsLoginDb.accdb")
Dim cmd As New OleDbCommand("select * from logintable where username1=@username1 and password1=@password1, conn")
cmd.Parameters.Add("@username1", oleDbType:=OleDbType.VarChar).Value = txtusername.Text
cmd.Parameters.Add("@password1", oleDbType:=OleDbType.VarChar).Value = txtpassword.Text
Dim adapter1 As New OleDbDataAdapter(cmd)
Using logintable As New DataTable
Dim unused = adapter1.Fill(logintable)
If logintable.Rows.Count <= 0 Then
MsgBox("error username or password")
Else
MsgBox("login sucessfull")
End If
End Using
End Sub

Answer 1

您需要先打开连接

conn.Open()

Answer 2

初始化cmd对象时有一个错别字。在中包含conn对象连接字符串。所以cmd对象缺少一个连接对象。

Dim cmd As New OleDbCommand("select * from logintable where username1=@username1 and password1=@password1, conn")

应:

Dim cmd As New OleDbCommand("select * from logintable where username1=@username1 and password1=@password1", conn)

Answer 3

将用户界面代码(单击按钮并显示消息框的地方)与数据库代码分开通常是一个好主意。

需要对Connections、Commands和DataAdapters进行处置，以释放其未受管理的资源。Using...End Using块将为我们做这些。

因为您只想知道记录是否存在，所以您可以只获得计数。你的命令文本中有一个错别字。结束语包括conn.

我们不需要DataTable或DataAdapter。这条数据只需要一个ExecuteScalar，它返回一个Object，所以我们需要CInt。

Private Sub btnlogin_Click(sender As Object, e As EventArgs) Handles btnlogin.Click
If IsLoginValid(txtusername.Text, txtpassword.Text) Then
MsgBox("login sucessfull")
Else
MsgBox("error username or password")
End If
End Sub
Private Function IsLoginValid(uname As String, pword As String) As Boolean
Dim count As Integer
Using conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersKatlegoDocumentsLoginDb.accdb"),
cmd As New OleDbCommand("select Count(*) from logintable where username1=@username1 and password1=@password1;", conn)
cmd.Parameters.Add("@username1", OleDbType.VarChar).Value = uname
cmd.Parameters.Add("@password1", OleDbType.VarChar).Value = pword
conn.Open()
count = CInt(cmd.ExecuteScalar)
End Using
If count = 1 Then
Return True
End If
Return False
End Function

代码仍然有一个大问题。密码应该永远不要以纯文本形式存储。它们需要加盐和散列，但这超出了这个问题的范围。