VB2005 reading a query 1

[ericksoda]

I am a VB 6.0 programmer and am trying to convert to VB2005. The app I am working on is one that extracts a comma delimited file from a query in SQL Server. I can do this easily in VB6, but am really struggling with how to do it in VB2005. I have gone through a few books and I know how to use databound controls...

Does anyone have a code sample of how I can:
Open the database
Set the query - "SELECT PatientId, Last, First FROM Patients"
and then read down the query results one row at a time?

It looks like this should be easy - I don't know why I am having this much trouble.

Thanks for any help!

David

 

[jebenson]

Dim Conn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim s As String

'initialize and open database connection
Conn = New SqlConnection("Data Source=SQLServerName;Initial Catalog=DatabaseName;Trusted_Connection=Yes")

'NOTE: SQLServerName and DatabaseName are the
'actual names of your SQL Server and the database you
'want to access.

Conn.Open()

'instantiate DataAdapter and DataSet
da = New SqlDataAdapter("SELECT PatientId, Last, First FROM Patients"), Conn)
ds = New DataSet
da.Fill(ds)

'Loop through all records in the DataTable in the DataSet
For r As Integer = 0 To ds.Tables(0).Rows.Count - 1
s = s & ds.Tables(0).Rows(r).Item("PatientId") & vbCrLf
Next

MsgBox(s)


A good basic overviewfor ADO .NET can be found at:

[URL unfurl="true"]http://www.devarticles.com/c/b/ADO.NET/[/url]

look at the articles titled "All You Need To Know About ADO.NET: Part 1/2" and "All You Need To Know About ADO.NET: Part 2/2"

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[ericksoda]

Thank you! I was just trying too hard, I guess.

FWIW, there is an extra ")" in the da = New SqlDataAdapter line.

And it also required

Imports System
Imports System.Data
Imports System.Data.SqlClient

But that is exactly what I needed. Really appreciate the hand up.

David