Question

Locked

VB.NET 2008 and ms access selecting a record to textboxes with dataset?

By pbaloyi ·
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

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

You should start by doing the query manually, using MS Query

by robo_dev In reply to VB.NET 2008 and ms access ...

Then you can determine if your select statement is asking for too much or not.

Just a random guess, but it's easy to get the row count logic wrong.

For debugging (and so we can help you here) please just query one field, indent your code, and put all your variable declarations together. If this is a school assignment, you will get a better grade.

http://www.homeandlearn.co.uk/net/nets12p7.html

generally speaking, writing VB to hit a database is grunt work, there is a great tool called ASPMAKER that generates all the ugly code for you. There is a .net version, a PHP version, even a XML flavor of the app. Best $40 you will ever spend.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums