Old school learning new techniques.

[p7542]

Hi there,

I recently started using VB.NET and doing my first VB/SQL project.

I have very basic question.

In VB 6 I used to get the last record result through below SQL (OleDB)

Dim LastRecord as Integer
adoPrimaryRS1.Open "Select * from BUY ORDER BY ID ASC", dB4, adOpenStatic, adLockOptimistic
adoPrimaryRS1.MoveLast
LastRecord = adoPrimaryRS1.Fields!Id

and now I am using SQL Server client, could you please tell me how to achieve above. I am using the below code.


Dim cmd As SqlCommand = New SqlCommand("SELECT TOP 1 ID FROM dbo.Accounts ORDER BY ID DESC", CON)
Dim DA As New SqlDataAdapter(cmd)
Dim DT As New DataTable

Many thanks in advance.

Khawa

[jmcilhinney]

If you've read the documentation for the SqlDataAdapter class or any tutorials on its use then you know that you have to call its Fill method to populate the DataTable with the result set. That said, you probably don't actually need a DataTable at all. You can just call ExecuteReader on the SqlCommand object and the get the field values from the SqlDataReader it returns. Having said that, if all you need is the ID then you can call ExecuteScalar and it will return a single value from the first column of the first row of the query's result set.

I suggest that you follow the Database FAQ link in my signature and check out some of the ADO.NET resources on this site. One of the links will be to my own ADO.NET code examples, which will cover all three of these scenarios.

[khawarqadri]

Many thanks for you time and help. ExecuteScalar did the job.

[Shaggy Hiker]

VB6 and VB.NET both interact with databases, but the model is sufficiently different that it can really trip you up if you try to bring forwards (most of) your understanding of VB6. The closest analog to the recordset from VB6 is probably the datareader, but it's not all THAT close. VB6 seemed to expect you to keep a connection open and interact with the DB more directly. VB.NET is geared more towards get in, get the data into a local construct, then get out.