Searching a database table & retreiving one record using dataset

See more:

VB9

VB

SQL-Server

Visual-Studio

, +

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.