I am a new born to vb.net. I am trying to retrieve a record from a database to textboxes by double clicking it from a ListView control. It seems my datatable is being filled with all the records instead of selecting only one record with a selectcommand. myval (Dim myval As Integer = dsAsset.Tables(0).Rows.Count) is counting all the records instead of the selected ID. I am now stuck. The code is below;
‘Connection string in a module
Module modADO
Public Const cnString As String = “Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=../data/prizedb.mdb”
End Module
Dim daAsset As New OleDbDataAdapter()
Dim dsAsset As New DataSet()
Private Sub lvViewAssets_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lvViewAssets.DoubleClick
SelectAsset()
End Sub
Sub SelectAsset()
Dim AssetID As String = lvViewAssets.SelectedItems(0).SubItems(2).Text
If Me.lvViewAssets.SelectedItems.Count > 0 Then
Dim sSQL As String = “”
Dim cnAsset As OleDbConnection
cnAsset = New OleDbConnection
Dim dtAsset As New DataTable
With cnAsset
If .State = ConnectionState.Open Then .Close()
.ConnectionString = cnString
.Open()
End With
Try
Dim qryAsset As String = “SELECT * FROM tblProjectAsset WHERE AssetCode = ‘” & AssetID & “‘”
daAsset.SelectCommand = New OleDbCommand(qryAsset, cnAsset)
daAsset.Fill(dsAsset)
Dim myval As Integer = dsAsset.Tables(0).Rows.Count
If dsAsset.Tables(0).Rows.Count <> 0 Then
dtAsset = dsAsset.Tables(0)
cboLocationID.Text = dtAsset.Rows(0)(“LocationID”)
cboSubLocationID.Text = dtAsset.Rows(0)(“SubLocationID”)
txtAssetCode.Text = IIf(IsDBNull(dtAsset.Rows(0)(“ProjectSiteCode”)), “”, dtAsset.Rows(0)(“ProjectSiteCode”))
txtSitename.Text = IIf(IsDBNull(dtAsset.Rows(0)(“SiteName”)), “”, dtAsset.Rows(0)(“SiteName”))
cboAssetType.Text = IIf(IsDBNull(dtAsset.Rows(0)(“ActivityCode”)), “”, dtAsset.Rows(0)(“ActivityCode”))
dtpCollectionDate.Text = dtAsset.Rows(0)(“RegDate”)
Else
MessageBox.Show(“No Records Available”, “Error”, MessageBoxButtons.OK)
End If
Catch ex As OleDbException
MsgBox(ex.ToString)
Finally
cnAsset.Close()
End Try
End If
End Sub
