如何搜索访问数据库以获取字段的数据...以便我可以检查它是否重复 [英] How do i search the access database to get data of a field... so that i can check if it is repeated or not
问题描述
例如,我需要检查表中是否已存在busid = 5
如何从数据库中提取所有busid字段的数据,以便将其与刚刚输入的新输入进行比较对于busid(以便在db中不重复busid)
改编pdoxtrader的代码
公共函数BusIdExists(ByVal busid As String)As Boolean
Dim thisSql As String =SELECT Count(*)FROM [yourtable] WHERE busid = @ busid
Dim rowCount As Integer
''获取具有该busid的行列表
使用conn As New SqlConnection(your sql connect string)
conn.Open()
使用comm As New SqlCommand(thisSql,conn)
comm.Parameters.AddWithValue(@ busid,busid)
rowCount = Convert.ToInt32(comm.ExecuteScalar( ))
结束使用
结束使用
返回(r owCount> 0)
结束函数
1)我跳过了DataTable和DataReader对象 - 不需要为这些对象实例化这个
2)我更改了sql并添加了Count(*)并删除了数据库名称(这是连接字符串中的selfevident)
3)我添加了busid使用参数而不是连接字符串。在更复杂的情况下避免sql注入通常更好的实践
你可以使用这个函数:
公共 功能 BusIdExists( ByVal busid 作为 字符串)作为 布尔
Dim theTable 作为 DataTable = 新 DataTable()
Dim thisSql 正如 字符串 =
' 准备我们的选择语句
thisSql = SELECT [some_colume_name] FROM [&你的数据库& ]。[yourtable] WHERE busid =& busid
' 获取具有该busid的行列表
使用 conn 作为 新 SqlConnection( 你的sql连接字符串)
conn.Open()
使用 comm 作为 新 SqlCommand(thisSql,conn)
使用 reader 作为 SqlDataReader = comm.ExecuteReader()
theTable.Load(reader)
conn.Close()
结束 使用
结束 使用
结束 使用
如果 theTable.Rows.Count> 0 返回 True
返回 错误
结束 功能
您可以这样使用它:
if BusIdExists( 5)
' 使用busid 5处理现有记录的代码
else
' 处理发现busid 5 isn'的代码t在数据库中
结束 如果
eg i need to check if busid =5 already exist in the table or not
how do i extract data of all the busid field from the database to compare it with the new input i just entered for busid (so that busid is not repeated in the db)
Adaption of pdoxtrader''s code
Public Function BusIdExists(ByVal busid As String) As Boolean Dim thisSql As String = "SELECT Count(*) FROM [yourtable] WHERE busid=@busid" Dim rowCount As Integer '' Get the list of rows with that busid Using conn As New SqlConnection("your sql connect string") conn.Open() Using comm As New SqlCommand(thisSql, conn) comm.Parameters.AddWithValue("@busid", busid) rowCount = Convert.ToInt32(comm.ExecuteScalar()) End Using End Using Return (rowCount > 0) End Function
1) I skipped the DataTable and DataReader objects - no need to instanciate those for this
2) I changed the sql and added Count(*) and removed the database name (that''s selfevident from the connection string)
3) I added the busid using a parameter instead of concatenating a string. That''s generally better praxis to avoid sql injections in more complex cases
You could use this function:
Public Function BusIdExists(ByVal busid As String) As Boolean Dim theTable As DataTable = New DataTable() Dim thisSql As String = "" ' Prepare our select statement thisSql = "SELECT [some_colume_name] FROM [" & yourdatabase & "].[yourtable] WHERE busid=" & busid ' Get the list of rows with that busid Using conn As New SqlConnection("your sql connect string") conn.Open() Using comm As New SqlCommand(thisSql, conn) Using reader As SqlDataReader = comm.ExecuteReader() theTable.Load(reader) conn.Close() End Using End Using End Using If theTable.Rows.Count > 0 then Return True Return False End Function
You would use it like this:
if BusIdExists("5") then ' code to handle an existing record with the busid 5 else ' Code to handle discovering that busid 5 isn't in the database End If
这篇关于如何搜索访问数据库以获取字段的数据...以便我可以检查它是否重复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!