active row

[Fenerbahce]

hi,
how can i find out that what number of record(row) my cursor focused on in database?

thanks

label1.text= active row`s number ?????

example :

Dim baglanti AsNew SqlClient.SqlConnection()
Dim command As New SqlClient.SqlCommand()
Dim kisiler As SqlClient.SqlDataReader

baglanti.ConnectionString = "data source=instance2000;" & "initial catalog=dbf;" & "integrated security= SSPI"
baglanti.Open()
command = baglanti.CreateCommand
command.CommandText = "select number, name, surname from veriler where name=@name"
command.Parameters.AddWithValue("@name", TextBox1.Text)
kisiler = command.ExecuteReader()

If kisiler.HasRows Then
While kisiler.Read()
[B][I]label1.text= active row`s number ?????[/I][/B]
End While
Else
MsgBox("no record")
End If
baglanti.Close()
kisiler.Close()

[Paladine]

Are you wanted the Number from your select statement criteria?

Because if the is the case then simply put:

label1.text = kisiler.GetValue(0)
'  Where 0 represents the first colum or 
'  ordinal position in the dataset returned by the reader.   
'   Columns are 0 based in SQL

FYI - It is poor programming edicate to use names such as Kisiler for an object like a datareader. It makes reading and debugging could very difficult. I would just suggest something like objKisilerReader instead. At least then, others will know what you are referring the variable name to.

Hope this helps.:cool:

[Fenerbahce]

I understand what you mean about my style, you are right. i will change it.

question; i have 10 persons in my database. i search for `John`. there are name and surname columns in table. What number of row has `john` in it.

answer ; label1.text= ? (for eample, john is in number 5th row)

thanks again

[Paladine]

Well, as I originally stated you are seeming to already be grabbing the row number in your select statement!

Note the changes in red to your code to get the "number" from the select statement.

command.CommandText = "select number from veriler where name=@name"
command.Parameters.AddWithValue("@name", TextBox1.Text)
kisiler = command.ExecuteReader()

If kisiler.HasRows Then
    While kisiler.Read()
        label1.text= kisiler.GetValue(0)
    End While
Else
    MsgBox("no record")
End If
baglanti.Close()
kisiler.Close()

** NOTE ** If you have a table with 50 rows, and do a select statement on a specific parameter you could have a result set of 1 or many rows, depending on the parameter. If the result set produced 5 rows, the first row in the result set wouldn't necessarily be Row number 1 in the table. It is relative to what you are referring to.

Hope this helps

[Fenerbahce]

hi,

there is something real that my english is not enough to explain my problem. that is why i couldn`t explain it.

Lets say, there isn`t any "number" column in "veriler" table. it has only "name and surname" columns. when i search a name in that table, result will say that "you found that name on number 3th row".

NAME SURNAME
------ -----------
john tree
lucy ant
jason lory
nick mad

SQL ------ select name from veriler name="lucy"

Result ----- Lucy is on 2nd row

Thank you very much

[Paladine]

Understood, but without the Number column it is a very complex means of determining the row number of a record in a table. Why? Several reasons. First of all, remember, a SELECT statemet or QUERY is a snapshot of the TABLE of data based on the criteria or parameters you set. Thus the result set (result of executing a SELECT statement) yields a subset of data.

Following your example
select name from veriler name="lucy"
would yield:

____veriler
=======
1] lucy

A result of 1 column and 1 row.

More specifically why is what you are asking difficult? Because the "position" or row where lucy is found is completely arbitrary without a Number column.

Why ? Well, what if I took your example table and sorted it like so:

ORIGINAL TABLE VIEW
NAME SURNAME
------ -----------
john tree
lucy ant
jason lory
nick mad

NEW TABLE VIEW

NAME SURNAME
------ -----------
lucy ant
jason lory
nick mad
john tree

The same table but with the view changed to SORT ASCENDINGLY by SURNAME.

So the result of which row lucy would be found on is different. Row #2 in the original table view and #1 in the new table view.

So the only methods I am aware of to accomplish this is the following;

1. capture the entire table in a dataSet and loop through and count the rows until you find the fow with the value you are looking for. Then the Count will represent the row you are pointing at.
2. Create a colum with the a numerical count to know which row is truly row 5, etc.

Hope this helps, and if anyone has a better suggestion I would also like to hear it.

[Fenerbahce]

at the end, i think i have to add number column in the table and use getvalue as you said. this will be easiest.

thank you very much for all of your helps