搜索数据库表&使用数据集检索一条记录 [英] Searching a database table & retreiving one record using dataset
问题描述
我想在SQL Database表中搜索一条记录，其中搜索算法依赖于文本框值。这意味着字符串
sEmpID = txtEmpID.text;
如果我在txtEmpID文本框中输入一个值，那么结果应该使用该值获得。它应该只检索一条记录。为此，我可以使用数据集。但是在数据集中，主键是在数据集的一侧设置的。但是当我想从数据库中添加主键时，它会创建一个重复的主键。那会产生错误。
有没有其他方法可以使用数据集？
更新的问题
DbConn Class
Imports System.Data.SqlClient
Imports System.Data.DataTable
Public Class DbConn
' 声明即时变量
Public DbConnct As New SqlConnection
Public DbComm As New SqlCommand
Public DbAdapt As New SqlDataAdapter
Public dsItemPurchase As New DataSet
Public dtItemPurchase As New DataTable
Public objICode As New Object
Public Sub Conn（）
'数据库连接
DbConnct.ConnectionString = 数据源= Chiranthaka_HP \ SQLSERVER2008R2;初始目录= SenMax;集成安全性= True
DbConnct.Open（）
End Sub
Public Sub AdaptIPurchse（）
DbAdapt = New SqlDataAdapter（ 选择*来自ItemPurchase，DbConnct）
DbAdapt.Fill（dsItemPurchase， ItemPurchase）
DbAdapt.Fill（dtItemPurchase）
' 设置PrimaryKey
dsItemPurchase.Tables（ItemPurchase）。PrimaryKey = New DataColumn（）{dsItemPurchase.Tables（ItemPurchase）。Columns（ItemCode） }
结束子
表格
Imports System.Data.SqlClient
Public Class UStockDetails
Inherits System.Web.UI.Page
Private DataConn As New DbConn
Protected Sub btnFind_Click（ByVal sender as Object ，ByVal e As EventArgs）处理btnFind.Click
' 检查ItemCode
如果不是（txtICode.Text =）那么
'声明对象变量
' objICode = txtICode.Text
DataConn.objICode = txtICode.Text
' 声明Find DataRow
Dim FindRow As Data.DataRow = DataConn.dsStockDetails.Tables（ StockDetails）。Rows.Find（objICode）
' Dim FindRow As Data.DataRow = DataConn.dtStockDetails.Rows.Find（DataConn.objICode）
If Not（FindRow Is Nothing）然后
'将记录分配给Texboxes
txtIType.Text = FindRow（ ItemType）
txtTitle.Text = FindRow（ 标题）
txtNoDVD.Text = FindRow（ NoDVD）
txtQTY.Text = FindRow（ QTY）
txtPrice.Text = FindRow（ PricePUnit）
txtBDate.Text = FindRow（ BuyDate）
txtSName.Text = FindRow （ SName）
txtSID.Text = FindRow（ SID）
' 设置th焦点
txtICode.Focus（）
'显示消息
lblMSG.Text = < span class =code-string> Record Found
Else
' 显示消息
lblMSG.ForeColor = Drawing.Color.Red
lblMSG.Text =未找到记录
结束如果
结束如果
结束Sub
结束等级
现在你可以看到我用过了数据集但在数据集中我必须创建一个主键！但它不允许从数据库中为数据库表创建主键。如果创建它会创建一个错误。为了防止解决方案被称为DataTable（）。然后我的问题是如何只过滤01记录。该记录根据sEmpNo中的文本进行过滤。
这就是我需要的。
而不是....
Dim FindRow As Data.DataRow = DataConn.dsStockDetails.Tables（ StockDetails）。Rows.Find （objICode）
使用...
Dim rows = DataConn.dsStockDetails.Tables（ StockDetails）。选择（字符串 .Format（ sEmpNo = {0}，txtICode.Text））
if （rows.GetLength（ 0 ）！= 0 ）然后 Fin dRow = rows [ 0 ]
打扰一下，如果有VB错误，我习惯了C＃
I want to search a record that in SQL Database table which the searching algorithm is depend on a textbox value. It means that the String
sEmpID = txtEmpID.text;
If I enter a value to the txtEmpID textbox then the result should gain using that value. It should retrieve only one record. To do that I can use a dataset. But in dataset the primary key is set on the datasets'' side. But when I want to add a primary key from the database it would create a duplicate primary key. That will generate an error.
Is there any other way to do that using a Dataset ?
Updated Question
DbConn Class
Imports System.Data.SqlClient
Imports System.Data.DataTable
Public Class DbConn
'Declare instant Variables
Public DbConnct As New SqlConnection
Public DbComm As New SqlCommand
Public DbAdapt As New SqlDataAdapter
Public dsItemPurchase As New DataSet
Public dtItemPurchase As New DataTable
Public objICode As New Object
Public Sub Conn()
'Database Connection
DbConnct.ConnectionString = "Data Source=Chiranthaka_HP\SQLSERVER2008R2;Initial Catalog=SenMax;Integrated Security=True"
DbConnct.Open()
End Sub
Public Sub AdaptIPurchse()
DbAdapt = New SqlDataAdapter("Select * From ItemPurchase", DbConnct)
DbAdapt.Fill(dsItemPurchase, "ItemPurchase")
DbAdapt.Fill(dtItemPurchase)
'Set PrimaryKey
dsItemPurchase.Tables("ItemPurchase").PrimaryKey = New DataColumn() {dsItemPurchase.Tables("ItemPurchase").Columns("ItemCode")}
End Sub
The Form
Imports System.Data.SqlClient
Public Class UStockDetails
Inherits System.Web.UI.Page
Private DataConn As New DbConn
Protected Sub btnFind_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnFind.Click
'Check the ItemCode
If Not (txtICode.Text = "") Then
'Declare the Object Variable
' objICode = txtICode.Text
DataConn.objICode = txtICode.Text
'Declare the Find DataRow
Dim FindRow As Data.DataRow = DataConn.dsStockDetails.Tables("StockDetails").Rows.Find(objICode)
' Dim FindRow As Data.DataRow = DataConn.dtStockDetails.Rows.Find(DataConn.objICode)
If Not (FindRow Is Nothing) Then
'Assign the Record to the Texboxes
txtIType.Text = FindRow("ItemType")
txtTitle.Text = FindRow("Title")
txtNoDVD.Text = FindRow("NoDVD")
txtQTY.Text = FindRow("QTY")
txtPrice.Text = FindRow("PricePUnit")
txtBDate.Text = FindRow("BuyDate")
txtSName.Text = FindRow("SName")
txtSID.Text = FindRow("SID")
'Set th Focus
txtICode.Focus()
'Display the Message
lblMSG.Text = "Record Found"
Else
'Display the Message
lblMSG.ForeColor = Drawing.Color.Red
lblMSG.Text = "Record Not Found"
End If
End If
End Sub
End Class
Now you can see I have used a dataset but in dataset I have to create a primary key! But it do not allow to create primary key for the database table from the database. If created it create an error. To prevent the solution is said to be the DataTable(). Then my question is how to filter 01 record only. That record is filtered according to the text in the sEmpNo.
That is what I needed.
Instead of ....
Dim FindRow As Data.DataRow = DataConn.dsStockDetails.Tables("StockDetails").Rows.Find(objICode)
use ...
Dim rows = DataConn.dsStockDetails.Tables("StockDetails").Select(String.Format("sEmpNo = {0}",txtICode.Text)) if(rows.GetLength(0) != 0) Then FindRow = rows[0]
Excuse me if there are VB errors I am used to C#
这篇关于搜索数据库表&使用数据集检索一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
