vb.net 如何检查值是否存在于数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18812298/
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
How to check if value exists in a database or not
提问by user2780962
I want code in VB to check if values in a database exist or not.
我希望 VB 中的代码检查数据库中的值是否存在。
Here is my code
这是我的代码
conn.open()
Dim select As New OleDbCommand
select.Connection = conn
select.CommandText = " SELECT COUNT(*) FROM your_table WHERE field = textbox.value"
if count>0
MsgBox("already exists")
conn.close()
but it is not working.
但它不起作用。
回答by Steve
Your query is wrong, you don't put the textbox value inside the query in that way.
You should always use a parameterized query
您的查询是错误的，您没有以这种方式将文本框值放入查询中。
您应该始终使用参数化查询
Dim commandText = "SELECT count(*) from your_table where field = ?"
Using(conn = new OleDbConnection(.....)
Using(select = New OleDbCommand(commandText, conn)
conn.open()
select.Parameters.Add("@p1", textbox.value)
Dim count = Convert.ToInt32(select.ExecuteScalar())
if count > 0 Then
MsgBox(" already exsist ")
End If
End Using
End Using
Here you set the parameter placeholder (?) in the command text and add the parameter to the command with its value. After that you need to execute the command. In your case you need only a single value as your return from the command so the ExecuteScalar is the correct method to use.
在此设置命令文本中的参数占位符 (?) 并将参数及其值添加到命令中。之后，您需要执行命令。在您的情况下，您只需要一个值作为命令的返回值，因此 ExecuteScalar 是正确的使用方法。
Note also that the connection and the command are disposable objects and thus should be closed and disposed when you don't need them anymore. For this the correct approach is the Using Statement
另请注意，连接和命令是一次性对象，因此当您不再需要它们时应关闭并处理它们。为此，正确的方法是使用语句
回答by vcs
Your query is not correct
您的查询不正确
"SELECT COUNT(*) FROM your_table WHERE field = textbox.value"
Change it to
将其更改为
"SELECT COUNT(*) FROM your_table WHERE field = '" & textbox.text & "'"
1) Winforms TextBox control dose not have a Value property. it has text property ( I am assuming you are using Textbox control)
1) Winforms TextBox 控件没有 Value 属性。它具有 text 属性（我假设您使用的是 Textbox 控件）
2) You need to send the value contained in the Textbox.Text and not "TextBox.Value". Hence you have add two string as mentioned in new query
2) 您需要发送包含在 Textbox.Text 中的值，而不是“TextBox.Value”。因此，您添加了新查询中提到的两个字符串
3) Then & endifis missing in your if statement
3) then & endif在您的 if 语句中丢失
4) This is not the correct way to use the query as mentioned by @steve and others.
4) 这不是@steve 和其他人提到的使用查询的正确方法。
回答by Akora
This works fine.
这工作正常。
Public Sub insrt()
con.Open()
Dim cmd1 As New OleDbCommand("select count(*) from tbcat where catname=?", con)
cmd1.Parameters.Add("@cat", OleDbType.VarChar).Value = txtcourse.Text
Dim count = Convert.ToInt32(cmd1.ExecuteScalar)
If count > 0 Then
MsgBox("Already Exist")
'Exit Sub
Else
Dim cmd As New OleDbCommand("insert into tbCat(Catcode,catname,protype)values('" & txtcode.Text & "','" & txtcourse.Text & "','" & txttype.Text & "')", con)
cmd.ExecuteNonQuery()
cmd.Dispose()
'con.Close()
display_course()
txtcode.Text = String.Empty
txtcourse.Text = String.Empty
End If
cmd1.Dispose()
con.Close()
End Sub
