Need help with ADO recordset?

[mrdod]

First off I'm not even sure if this is the right area to post so please advise if not correct. Second I just started trying to convert my Access DB to Visual Basic (Visual Studio). I created a form and a ListView. I can get the headers on the form with no problems. I created an ADO connection to my Access DB but am not sure of the syntax on how to loop through my recordset and put info into my ListView. Below is the code I have so far (Be gentle with me). If someone could nudge me in the right direction it would be great!!

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, ListView1.Resize, MyBase.Resize
'TODO: This line of code loads data into the 'Audit_DatabaseDataSet.tblhistory' table. You can move, or remove it, as needed.

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strDSN As String

strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\Lwcfil01\public\Safety\Audit Database\Audit_Database.mdb;"

strSQL = "SELECT * FROM QRYhistory;"
cnt.Open(strDSN)
rst = cnt.Execute(strSQL)

ListView1.View = View.Details

With ListView1.Columns
.Add("Mycounter", 0, HorizontalAlignment.Left)
.Add("Audit Number", 100, HorizontalAlignment.Left)
.Add("Status", 100, HorizontalAlignment.Left)
.Add("Assigned To", 150, HorizontalAlignment.Left)
.Add("Date Audited", 150, HorizontalAlignment.Left)
.Add("Area Audited", 150, HorizontalAlignment.Left)
.Add("Product Team", 100, HorizontalAlignment.Left)
.Add("Auditor", 150, HorizontalAlignment.Left)
.Add("Shift Audited", 100, HorizontalAlignment.Left)
.Add("Issue", 150, HorizontalAlignment.Left)
.Add("Comments", 200, HorizontalAlignment.Left)
.Add("Positive Comment", 150, HorizontalAlignment.Left)
.Add("Positive Comments", 150, HorizontalAlignment.Left)
.Add("Action Items", 200, HorizontalAlignment.Left)
.Add("To Be Completed", 150, HorizontalAlignment.Left)
.Add("Date Completed", 150, HorizontalAlignment.Left)
End With

'How do I fill the ListView using my ADO recordset?

'Close recordset
rst.Close()
cnt.Close()
rst = Nothing
cnt = Nothing

End Sub

End Class

 

[tedsmith]

Generally you set the recordset property to a recordset you make.
I'm not sure about the listview control but I use a datagrid and have no problem reading or writing to the database by typing in the grid.
You first set the column names and widths by the properties box of the datagrid control, easier than using a lot of code.
I always seem to have problems writing and requerying reliably to a database when I use the recommended datacontrol with the datagrid so I hook the datagrid directly to the database as follows

In project references you have to have selected:
Microsoft DAO 3.6 Object library
Microsoft ActiveX Data Objects 2.5 Library

Put a datagrid1 on the form (Components, Microsoft DataGrid Control 6 (OLEDB)

'Form Declarations:
Public CN As New ADODB.Connection ' Connection tool
Public RS As New ADODB.Recordset ' Recordset tool
Dim strSQL as String

Sub Command1_click()
    strSQL = "SELECT * FROM QRYhistory" 'or whatever
 ' Opening database conection
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "\\Lwcfil01\public\Safety\Audit" Database\Audit_Database.mdb"
    ' The following instruction must be executed before opening our recordset
    RS.CursorLocation = adUseClient
    ' Opening the recordset
    RS.Open  strSQL, CN, adOpenStatic, adLockOptimistic
    ' Now we set our DataGrid to have the content of the db
    Set DataGrid1.DataSource = RS
    ' This datagrid is configured to the table so data can be viewed or changed as soon as you click on the command1 button.
End Sub

I got this code from somewhere else I cant remember.

You just change the sql to find any one or groups of entry

When you close, be sure to say
RS.Update
RS.Close
CN.Close
to make sure any changes are kept in the original table.

 

[BobRodes]

Listview is good for viewing, well, lists. You're wanting to view a table of data, and a datagrid is a better choice to do that. Furthermore, Ted's approach, that of not using a data control and instead creating an ADO recordset and assigning it as the datagrid's record source, is the correct one IMO.

Please note, however, that I do NOT recommend that you allow a user to directly edit data via a datagrid.

Bob

 

[mrdod]

I've tried putting a Data Grid control on my form and am not getting anything to show in the grid. Below is my code.
I am using Visual Basic 2005 Express Edition. Does this make a difference? Are there any properties I need to be setting?


Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, ListView1.Resize, MyBase.Resize
'TODO: This line of code loads data into the 'Audit_DatabaseDataSet.QRYhistory' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'Audit_DatabaseDataSet.tblhistory' table. You can move, or remove it, as needed.

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strDSN As String

'Creating Connection String
strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\Lwcfil01\public\Safety\Audit Database\Audit_Database.mdb;"
'Generating SQL statement for information I need.
strSQL = "SELECT * FROM QRYhistory;"

cnt.Open(strDSN)
rst.Open(strSQL, cnt, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

Me.GrView.DataSource = rst
rst.Update()
rst.Close()
cnt.Close()
rst = Nothing
cnt = Nothing

End Sub

End Class

 

[BobRodes]

You'll want to repost in the VB.Net forum, forum796.

Bob