Question How to get last record from certain column from SQL Server?

[black zero]

I have a code like this:

Dim counter2 As Integer

        cmdT = New SqlCommand("select * from Kelancaran", con)
        drT = cmdT.ExecuteReader()

        Do While drT.Read
            tempcounter = Val(drT.Item(0))
        Loop

        drT.Close()
        cmdT.Dispose()


        counter2 = tempcounter + 1 

        labeltransaction.Text = counter2

        tempcounter = counter2

However, I'd like to know if there's a faster way to get the last 'Val(drT.Item(0))' from Kelancaran database.

Also, if I'm using that code, tempcounter variable sometimes doesn't show the latest record from drT.Item(0)... well...

Any information is really appreciated.

 

[black zero]

How do I read the last record from SQL Server?

Uh... Sorry for asking too much.

I just want to read one column and load it to textbox. The said column is located at the end of the table. On VB6, we can use EOF to do this, but what's the equivalent method on .NET?

In my case, the table contains 40 columns, and I want to get the last record, and column 37 from that last record should be parsed to textbox.

 

[jmcilhinney]

There's no point reading the whole table just to get one column from one row, whether you're using ADO or ADO.NET. Your SQL query should be:

SELECT TOP 1 SomeColumn FROM SomeTable ORDER BY IDColumn DESC

That will return only the single value you want.

In ADO.NET you'd create a SqlCommand containing that query and call its ExecuteScalar method to get the single value result.

 

[black zero]

Thanks, it's working.

However, I'd like to know whether .NET has EOF in VB6 equivalent or not?

 

[jmcilhinney]

Thanks, it's working.

However, I'd like to know whether .NET has EOF in VB6 equivalent or not?

Yes and no. ADO.NET is different to ADO in a lot of ways. You can still use ADO in VB.NET and make use of a Recordset if you want but it's not recommended. Unlike ADO, which has a Recordset that does basically everything, ADO.NET follows good OO design rules and has multiple classes that each have a specific job to do. There's one class to create the connection to the database, another to execute a SQL command, another to manage multiple commands to get and save a result set, another to read a result set, another to store a result set and so on.

So, when you retrieve data from a database using ADO.NET you'll generally do one of two things:

1. Create a DataAdapter and call its Fill method to populate a DataTable.

2. Create a Command and call its ExecuteReader method to create a DataReader.

If you use the first option then the DataTable will contain the entire result set and provide random access to it. In this case EOF doesn't really make sense. You can access any row at any time and if you want to access each row consecutively then you'd just use a For Each loop, e.g.

For Each row As DataRow In myDataTable.Rows
    MessageBox.Show(CStr(row("Name")), row("ID").ToString())
Next

If you want to know how many records there are then you get the Rows.Count property of the DataTable.

If you use the second option then the DataReader provides read-only, forward only access to the entire result set one record at a time. In this case EOF does make sense but there is no actual EOF property.

Initially the DataReader doesn't expose any data and you must call its Read method to advance it to the first record. Subsequently you must call Read again to advance to the next record. Read returns True as long as there was a record to advance to and False if it's already on the last record. As such, you normally use a While loop to read a DataReader from beginning to end, e.g.

While myDataReader.Read()
    MessageBox.Show(CStr(myDataReader("Name")), myDataReader("ID").ToString())
End While

 

[black zero]

...that's good, way too good explanation. Thank you very much, you have my gratitude.

Sorry for my noobishness...

 

[cjard]

Sorry for my noobishness...

The only stupid questions are those that you do not ask..