vb.net 条件表达式中的数据类型不匹配。微软访问VB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19118744/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Data type mismatch in criteria expression. MS Access VB
提问by GeeJay Luna
' OK button
'确定按钮
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
Dim con As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\Jill\Desktop\saddbase\Sadsystem\Sadsystem\bin\Debug\tenant.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM info WHERE TN_ID = '" & UsernameTextBox.Text & "' AND Password = '" & PasswordTextBox.Text & "' ", con)
con.Open()
Dim sdr As OleDbDataReader = cmd.ExecuteReader()
' If the record can be queried, Pass verification and open another form.
If (sdr.Read() = True) Then
MessageBox.Show("The user is valid!")
Me.Hide()
Else
MessageBox.Show("Invalid Tenant ID or password!")
End If
When I run the program there's an error in cmd.ExecuteReader(). Data type mismatch in criteria expressionplease help how to fix this error.
当我运行程序时，cmd.ExecuteReader() 中出现错误。条件表达式中的数据类型不匹配请帮助解决此错误。
回答by Steve
In your query you pass two strings for the TN_ID and Password fields.
Probably the TN_ID is a numeric field and you don't need to put quotation marks around it and I find really strange that you pass the value of a UserName textbox.
在您的查询中，您为 TN_ID 和密码字段传递两个字符串。
可能 TN_ID 是一个数字字段，您不需要在它周围加上引号，我发现您传递 UserName 文本框的值真的很奇怪。
Said that, I wish to examine your query because there are potential problems that you have not seen:
说到这里，我想检查一下您的查询，因为存在您没有看到的潜在问题：
First of all PASSWORD is a reserved Keyword and thus you need to use Square Brackets around it.
Second, do not use string concatenation to build sql commands but use a parameterized query like this
首先，PASSWORD 是一个保留关键字，因此您需要在它周围使用方括号。
其次，不要使用字符串连接来构建 sql 命令，而是使用像这样的参数化查询
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
Dim commandText = "SELECT * FROM info WHERE TN_ID = ? AND [Password] = ?"
Using con = New OleDbConnection(......))
Using cmd = New OleDbCommand(commandText,con))
con.Open()
' If the TN_ID is really a numeric field then you need '
' to conver the first parameter to a number '
' cmd.Parameters.AddWithValue("@p1", Convert.ToInt32(UsernameTextBox.Text))'
cmd.Parameters.AddWithValue("@p1", UsernameTextBox.Text)
cmd.Parameters.AddWithValue("@p2", PasswordTextBox.Text)
Using sdr As OleDbDataReader = cmd.ExecuteReader())
.....
End Using
End Using
End Using
End Sub
As a side note, not related to your problem, consider also to NOT store password in plain text in the database. There are techniques that HASH the password text and store the result in the database. In this way none can get the password simply looking at the database file. See the details in this question
作为旁注，与您的问题无关，请考虑不要在数据库中以纯文本形式存储密码。有一些技术可以对密码文本进行哈希处理并将结果存储在数据库中。通过这种方式，没有人可以通过查看数据库文件来获得密码。请参阅此问题中的详细信息
回答by S.Mirzaei
Private Sub SumOfIR()
Try
Dim con As New System.Data.OleDb.OleDbConnection(ConnectionString)
Dim com As New System.Data.OleDb.OleDbCommand
con.Open()
com.Connection = con
com.CommandText = "Select Sum(IR) from Spectrum where StdNu='" + TxtNuTeif.Text + "'"
com.Parameters.Clear()
Dim SumIR As OleDbDataReader = com.ExecuteScalar
LblIRTeif.Text = com.ExecuteScalar("SumIR").ToString
con.Close()
com.Dispose()
Catch ex As Exception
BehComponents.MessageBoxFarsi.Show(ex.ToString, "", BehComponents.MessageBoxFarsiButtons.OK, MessageBoxIcon.Warning)
End Try
End Sub
