帮助使用vb.net检查SQL数据库中的条目 [英] Help with checking entry in SQL database using vb.net

问题描述

使用vb.net帮助检查SQL db中是否存在条目



我试图在vb.net中编写一个子程序来检查SQL数据库以查看如果表1中存在记录，如果找到，则转到标签1，否则检查表2，如果找到，转到标签2.如果在表1或表2中找不到，则转到标签3.我是什么目前总是进入Not Found场景（标签3），即使该项目存在于两个表中的至少一个中。显然我在写的内容中存在逻辑问题，但我无法弄清楚出了什么问题。有人可以帮我解决我的困境吗？非常感谢。



这是我目前子程序的麻烦部分：



Dim作为布尔值存在



sql =从资产中选择ASSETTAG，ASSETTAG ='&TextBox1.Text&'，

DBConnect（sql）

虽然reader.Read

如果exists = False那么

GoTo CheckALL_ASSETS

Else

GoTo FoundAssets

结束如果

DBClose（）

结束时



CheckALL_ASSETS：

sql =从ALL_ASSETS中选择ASSETTAG，其中ASSETTAG ='&TextBox1.Text&'，

DBConnect（sql）

虽然读者阅读

如果exists = False那么

GoTo NotFound

否则

GoTo LookupAllAssets

结束如果

DBClose（）

结束时

Help with checking existence of entry in SQL db using vb.net

I am trying to write a subroutine in vb.net to check an SQL database to see if a record exists in table 1, and if found, go to label 1, otherwise check in table 2, and if found, go to Label 2. If not found in either table 1 or table 2 then go to Label 3. What I have at the moment always goes to the Not Found scenario (Label 3), even though the item is present in at least one of the two tables. Evidently I have a logic problem in what I have written, but I can't figure out what is wrong. Can somebody help solve my dilemma please? It will be greatly appreciated.

This is the troublesome part of the subroutine I have at the moment:

Dim exists As Boolean

sql = "SELECT ASSETTAG from Assets where ASSETTAG ='" & TextBox1.Text & "',"
DBConnect(sql)
While reader.Read
If exists = False Then
GoTo CheckALL_ASSETS
Else
GoTo FoundAssets
End If
DBClose()
End While

CheckALL_ASSETS:
sql = "SELECT ASSETTAG from ALL_ASSETS where ASSETTAG ='" & TextBox1.Text & "',"
DBConnect(sql)
While reader.Read
If exists = False Then
GoTo NotFound
Else
GoTo LookupAllAssets
End If
DBClose()
End While

推荐答案

您可以使用COUNT（ *）在sql中返回记录计数以确定是否存在记录。最好修剪文本框输入。您还应该使用参数化查询来阻止sql注入。参考： SqlCommand.Parameters [ ^ ]

You can use COUNT(*) in sql to return record count to determine the existence of record. Better to trim the textbox input too. You should also use parameterized query to prevent sql injection. Refer: SqlCommand.Parameters[^]

Dim count As Int16 = 0

Dim commandText As String = _
     "SELECT COUNT(*)  from Assets   where ASSETTAG = @para" 

Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(commandText, connection)

        command.Parameters.AddWithValue("@para", Trim(TextBox1.Text) )

count = command.ExecuteScalar  
   
End Using 

If count = 0 Then
GoTo CheckALL_ASSETS
Else
GoTo FoundAssets
End If

这篇关于帮助使用vb.net检查SQL数据库中的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！