如何测试数据库中是否存在值

本文介绍了如何测试数据库中是否存在值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我该如何检查res4是否找到了值。如果我粘贴textbox4不存在的值，我在第一行有错误：附加信息：如果对象打开，则不允许操作。有什么问题？

cnn.ConnectionString =driver = {SQL Server}; &安培; server = xxx.xxx.xx; uid = xx; pwd = xxxx +; database = xxxxx

我有什么试过：

Private Sub TextBox4_TextChanged（sender As Object，e As EventArgs）处理TextBox4.TextChanged cnn.ConnectionString =driver = {SQL Server}; &安培; server = xxx.xxx.xx; uid = xx; pwd = xxxx +; database = xxxxx 尝试 cnn.Open（） Catch MsgBox（Připojeníkdatabázisenezdařilo。，Chyba）结束尝试 'Overeni zda pripojeni probehlo OK 如果cnn.State = 1则$ b $b'Automatickévyhledáníčíslapílulekóduoperace尝试如果TextBox4.Text.Length = 9则 res4 = cnn.Execute（SELECT VKmenStavVC.RegCis From TabVyrCS LEFT OUTER JOIN TabKmenZbozi VKmenStavVC ON VKmenStavVC.ID =（SELECT TabStavSkladu.IDKmenZbozi FROM TabStavSkladu WHERE TabStavSkladu.ID = TabVyrCS.IDStavSkladu）LEFT OUTER JOIN TabVyrCS_EXT WITH（NOLOCK）ON TabVyrCS_EXT.ID = TabVyrCS.ID WHERE TabVyrCS.Nazev1 ='& TextBox1.Text &'）如果cnn.ExecuteScalar<> 0然后 Dim registracni4 As String = res4.GetString registracni4 = registracni4.Replace（vbCr，）如果registracni4 = TextBox11.Text那么 TextBox15.Text =OK TextBox10.Text = registracni4 Else TextBox15.Text =NOK MsgBox（Nesouhlasíčíslodíluvýrobníoperací！，Chyba）结束如果 其他 MsgBox（Hodnota nenalezena，Chyba）结束如果结束如果捕获 cnn .Close（） MsgBox（Napojenídotabulkysenezdařilo，Chyba） TextBox4.Clear（）返回结束尝试否则 cnn.Clos e（）结束如果结束子

解决方案

这里有太多错误...... 1）不要共享连接，每次想要使用它们时重新创建它们，并将构造放入使用块，这样它就是自动关闭并在最后处置。同样的事情适用于Command对象 2）永远不要这样做！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。 连接字符串时会导致问题，因为SQL会收到如下命令：

SELECT * FROM MyTable WHERE StreetAddress = ' Baker' s Wood '

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

SELECT * FROM MyTable WHERE StreetAddress = ' x'; DROP 表 MyTable; - '

哪个SQL看作三个单独的命令：

SELECT * FROM MyTable WHERE StreetAddress = ' x';

完全有效的SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

- '

其他一切都是评论。 所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。 所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期进行备份，不是吗？ 并在登录时进行备份？那真是太傻了...... 3）用纯文本存储密码？哦亲爱的...有一天在GDPR上查看规则。切勿以明文形式存储密码 - 这是一个主要的安全风险。有关如何在此处执行此操作的信息：密码存储：如何做到这一点。 [ ^ ] - 代码在C＃中，但很明显。 4）为什么你要做两次相同的命令？一旦得到数据，一次得到计数？ 5）帮自己一个忙，并停止使用Visual Studio默认名称 - 你可能还记得TextBox8是今天的手机号码，但是当你必须在三周内修改它时，你会吗？使用描述性名称 - 例如tbMobileNo - 您的代码变得更容易阅读，更自我记录，更易于维护 - 并且编码速度更快，因为Intellisense可以通过三次击键来tbMobile，其中TextBox8需要思考大概和8次击键... 6）想想你的用户：你想要面对一个充满控件的页面吗？你至少有15个文本框有

Hi, How can I check if res4 found a value. If I paste value which not exist to textbox4 I have a problem on first line with wrong: Additional information: Operation is not allowed, if object is open. What is do wrong?

cnn.ConnectionString = "driver={SQL Server};" & "server=xxx.xxx.x.x;uid=xx;pwd=xxxx+;database=xxxxx"

What I have tried:

Private Sub TextBox4_TextChanged(sender As Object, e As EventArgs) Handles TextBox4.TextChanged cnn.ConnectionString = "driver={SQL Server};" & "server=xxx.xxx.x.x;uid=xx;pwd=xxxx+;database=xxxxx" Try cnn.Open() Catch MsgBox("Připojení k databázi se nezdařilo.", , "Chyba") End Try ' Overeni zda pripojeni probehlo OK If cnn.State = 1 Then 'Automatické vyhledání čísla dílu dle kódu operace Try If TextBox4.Text.Length = 9 Then res4 = cnn.Execute("SELECT VKmenStavVC.RegCis From TabVyrCS LEFT OUTER JOIN TabKmenZbozi VKmenStavVC ON VKmenStavVC.ID=(SELECT TabStavSkladu.IDKmenZbozi FROM TabStavSkladu WHERE TabStavSkladu.ID=TabVyrCS.IDStavSkladu) LEFT OUTER JOIN TabVyrCS_EXT WITH(NOLOCK) ON TabVyrCS_EXT.ID=TabVyrCS.ID WHERE TabVyrCS.Nazev1 = '" & TextBox1.Text & "'") If cnn.ExecuteScalar <> 0 Then Dim registracni4 As String = res4.GetString registracni4 = registracni4.Replace(vbCr, "") If registracni4 = TextBox11.Text Then TextBox15.Text = "OK" TextBox10.Text = registracni4 Else TextBox15.Text = "NOK" MsgBox("Nesouhlasí číslo dílu s výrobní operací!", , "Chyba") End If Else MsgBox("Hodnota nenalezena", , "Chyba") End If End If Catch cnn.Close() MsgBox("Napojení do tabulky se nezdařilo", , "Chyba") TextBox4.Clear() Return End Try Else cnn.Close() End If End Sub

解决方案

There is so much wrong here ... 1) Don't share a connection, create them anew each time you want to use them, and put the construction into a Using block so it is automatically closed and disposed at the end. The same thing applies to Command objects 2) Never do that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead. When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment. So it does: selects any matching rows, deletes the table from the DB, and ignores anything else. So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you? And doing it on a login? That's really silly... 3) Storing passwords in plain text? Oh dear ... Look up the rules on GDPR some day. Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^] - the code is in C# but it's pretty clear. 4) Why are you doing the same command twice? Once to get the data, and once to get the count? 5) Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes... 6) Think about your users: do you want to be faced with a page full of controls?you have at least 15 text boxes there!