How to get record number from original table

[Martin Heuckeroth]

Hi

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Please help,

Regards, Martin

[Lucas Tam]

Martin Heuckeroth <ni**@nospam.invalid> wrote in
news:ot********************************@4ax.com:

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Did you do a SELECT COUNT(*)? Or did you do a SELECT COUNT(FIELDNAME)? If
you did the second, it returns non-null values which maybe why your row
counts are inaccurate:

http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_ca-co_5790.asp

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Richard Myers]

What are you trying to do?

One of the basic rules of relational databases is that the order of rows is
not important. If you need to identify a particular row you should be using
keys and unique indexes.

hth
Richard

[VJ]

I think Martin is looking for something like RowIndex the hexadecimal value
that is available in Oracle. Are you trying to delete duplicate rows created
by mistake? what are you trying do ? If you are trying to delete dup rows i
have queries that can do that.. let me know .... but if you are looking to
use in program I am not aware of it...

VJ

"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:ot********************************@4ax.com...

Hi

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Please help,

Regards, Martin

[Martin Heuckeroth]

We are looking for a way to determine the rownumber of the record in
the original database. Records are renumbered in the relation and we
want to edit/modify the original record in the original table.
Therefore we think we need the recordnumber of the original record in
the original table.

Here some code we brew... We need for nRecord in the 2nd line the
rownumber of the original table where nRecord NOW returns the
recordnumber of the relation.

Function SaveItem()
Dim nRecord As Integer = Me.BindingContext(DsGroup_News1,
"NewsGroup.NewsGroupNews").Position
Console.WriteLine(nRecord)
Dim nRow As DataRow = DsGroup_News1.News.Rows(nRecord)
Dim lDelete As Boolean =
DsGroup_News1.Tables("News").Rows(nRecord)("Record _Delete")
StatusBarSync(2)
If (nRow.HasVersion(DataRowVersion.Proposed) = True) Then
Me.DsGroup_News1.Tables("News").Rows(nRecord)("Vie wed") +=
1

Me.DsGroup_News1.Tables("News").Rows(nRecord)("Dat e_Modify") = Now()
Me.DsGroup_News1.Tables("News").Rows(nRecord)("Ver sion") =
Microsoft.VisualBasic.Right(StatusBarPanel_Version .Text, 8)
If lDelete = True Then

Me.DsGroup_News1.Tables("News").Rows(nRecord)("Dat e_Delete") = Now()
End If
On Sun, 5 Sep 2004 16:16:28 -0500, "VJ" <vi********@yahoo.com> wrote:

I think Martin is looking for something like RowIndex the hexadecimal value
that is available in Oracle. Are you trying to delete duplicate rows created
by mistake? what are you trying do ? If you are trying to delete dup rows i
have queries that can do that.. let me know .... but if you are looking to
use in program I am not aware of it...

VJ

"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:ot********************************@4ax.com.. .

Hi

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Please help,

Regards, Martin

[Doug Bell]

Martin,
You haven't said what sort of DB you are connecting to but in any case one
of the rules of relational databases is that rows are un-ordered.
Your table should have a primary key and you could sort by it. You can't
rely on a record number.

Doug

"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:ot********************************@4ax.com...

Hi

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Please help,

Regards, Martin

[Doug Bell]

"Doug Bell" <dug@bigpond> wrote in message
news:uY*************@TK2MSFTNGP09.phx.gbl...

Martin,
You haven't said what sort of DB you are connecting to but in any case one
of the rules of relational databases is that rows are un-ordered.
Your table should have a primary key and you could sort by it. You can't
rely on a record number.

Doug

"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:ot********************************@4ax.com...

Hi

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Please help,

Regards, Martin

[Lucas Tam]

Martin Heuckeroth <ni**@nospam.invalid> wrote in
news:pn********************************@4ax.com:

We are looking for a way to determine the rownumber of the record in
the original database.

There is no such thing as a row number in a relational database.

If you need some sort of identifier... add an integer identity (autonumber)
column to the original table. You can track changes via the identity field.

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Gajendra]

In modern relational databases their is no such thing as "physical record
number" of any record. Every thing is relational.

The only way to identify a particular record in a sure shot way is the
primary key of the table.

Gajendra
-------------------------------------
"Lucas Tam" <RE********@rogers.com> wrote in message
news:Xn***************************@140.99.99.130.. .

Martin Heuckeroth <ni**@nospam.invalid> wrote in
news:pn********************************@4ax.com:

We are looking for a way to determine the rownumber of the record in
the original database.
There is no such thing as a row number in a relational database.

If you need some sort of identifier... add an integer identity

(autonumber) column to the original table. You can track changes via the identity field.
--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Richard Myers]

O.k what you've posted here is a bunch of dataset related code.

I dont really see what this code has to do with your DBMS explaination. If
the data in this dataset is filled from a table then why not put a Primary
key (MessageId) on the table to use when referring back to the DBMS source
table if thats what your trying to do.

It's would also be a good idea to check out the With keyword, it would help
clean your code up quite a lot and is more performant in places like this.

hth
Richard


"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:pn********************************@4ax.com...

We are looking for a way to determine the rownumber of the record in
the original database. Records are renumbered in the relation and we
want to edit/modify the original record in the original table.
Therefore we think we need the recordnumber of the original record in
the original table.

Here some code we brew... We need for nRecord in the 2nd line the
rownumber of the original table where nRecord NOW returns the
recordnumber of the relation.

Function SaveItem()
Dim nRecord As Integer = Me.BindingContext(DsGroup_News1,
"NewsGroup.NewsGroupNews").Position
Console.WriteLine(nRecord)
Dim nRow As DataRow = DsGroup_News1.News.Rows(nRecord)
Dim lDelete As Boolean =
DsGroup_News1.Tables("News").Rows(nRecord)("Record _Delete")
StatusBarSync(2)
If (nRow.HasVersion(DataRowVersion.Proposed) = True) Then
Me.DsGroup_News1.Tables("News").Rows(nRecord)("Vie wed") +=
1

Me.DsGroup_News1.Tables("News").Rows(nRecord)("Dat e_Modify") = Now()
Me.DsGroup_News1.Tables("News").Rows(nRecord)("Ver sion") =
Microsoft.VisualBasic.Right(StatusBarPanel_Version .Text, 8)
If lDelete = True Then

Me.DsGroup_News1.Tables("News").Rows(nRecord)("Dat e_Delete") = Now()
End If
On Sun, 5 Sep 2004 16:16:28 -0500, "VJ" <vi********@yahoo.com> wrote:

I think Martin is looking for something like RowIndex the hexadecimal valuethat is available in Oracle. Are you trying to delete duplicate rows createdby mistake? what are you trying do ? If you are trying to delete dup rows ihave queries that can do that.. let me know .... but if you are looking touse in program I am not aware of it...

VJ

"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:ot********************************@4ax.com.. .

Hi

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Please help,

Regards, Martin

[Martin Heuckeroth]

Ok a lot of the previous we found out for ourselves already. Thx for
the input!

Is there any brilliant way to do a search in VB.NET on a table. Any
code? Apparently we are running around in a circle and need to break
out :D

TIA, Martin

On Mon, 6 Sep 2004 22:25:00 +1200, "Richard Myers"
<ri*********************@basd.co.nz> wrote:

O.k what you've posted here is a bunch of dataset related code.

I dont really see what this code has to do with your DBMS explaination. If
the data in this dataset is filled from a table then why not put a Primary
key (MessageId) on the table to use when referring back to the DBMS source
table if thats what your trying to do.

It's would also be a good idea to check out the With keyword, it would help
clean your code up quite a lot and is more performant in places like this.

hth
Richard


"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:pn********************************@4ax.com.. .

We are looking for a way to determine the rownumber of the record in
the original database. Records are renumbered in the relation and we
want to edit/modify the original record in the original table.
Therefore we think we need the recordnumber of the original record in
the original table.

Here some code we brew... We need for nRecord in the 2nd line the
rownumber of the original table where nRecord NOW returns the
recordnumber of the relation.

Function SaveItem()
Dim nRecord As Integer = Me.BindingContext(DsGroup_News1,
"NewsGroup.NewsGroupNews").Position
Console.WriteLine(nRecord)
Dim nRow As DataRow = DsGroup_News1.News.Rows(nRecord)
Dim lDelete As Boolean =
DsGroup_News1.Tables("News").Rows(nRecord)("Record _Delete")
StatusBarSync(2)
If (nRow.HasVersion(DataRowVersion.Proposed) = True) Then
Me.DsGroup_News1.Tables("News").Rows(nRecord)("Vie wed") +=
1

Me.DsGroup_News1.Tables("News").Rows(nRecord)("Dat e_Modify") = Now()
Me.DsGroup_News1.Tables("News").Rows(nRecord)("Ver sion") =
Microsoft.VisualBasic.Right(StatusBarPanel_Version .Text, 8)
If lDelete = True Then

Me.DsGroup_News1.Tables("News").Rows(nRecord)("Dat e_Delete") = Now()
End If
On Sun, 5 Sep 2004 16:16:28 -0500, "VJ" <vi********@yahoo.com> wrote:

>I think Martin is looking for something like RowIndex the hexadecimalvalue >that is available in Oracle. Are you trying to delete duplicate rowscreated >by mistake? what are you trying do ? If you are trying to delete dup rowsi >have queries that can do that.. let me know .... but if you are lookingto >use in program I am not aware of it...
>
>VJ
>
>"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
>news:ot********************************@4ax.com.. .
>>
>>
>> Hi
>>
>> Any idea on how to get a row number from the original table? We do a
>> query and get a result. The row number from the result is different
>> from the rownumber of the table the result originated from. I need the
>> record number from the original tabel. How do you get THAT number?
>>
>> Please help,
>>
>> Regards, Martin
>

[Martin Heuckeroth]

Hi Doug,

The original message was lost in the thread! We are building in VB.NET
on a database in SQL server 2000 and we did find out we cant rely on a
record number but were convinced it would be possible to get them
working reliable. Our bad and all we needed was some armtwisting by
you guys :D
On Mon, 6 Sep 2004 08:38:04 +1000, "Doug Bell" <dug@bigpond> wrote:

Martin,
You haven't said what sort of DB you are connecting to but in any case one
of the rules of relational databases is that rows are un-ordered.
Your table should have a primary key and you could sort by it. You can't
rely on a record number.

Doug

"Martin Heuckeroth" <ni**@nospam.invalid> wrote in message
news:ot********************************@4ax.com.. .

Hi

Any idea on how to get a row number from the original table? We do a
query and get a result. The row number from the result is different
from the rownumber of the table the result originated from. I need the
record number from the original tabel. How do you get THAT number?

Please help,

Regards, Martin

[Cor Ligthert]

Martin,

To get answers I would never add new questions to old ones, however assuming
you are talking about datatables than there are 3 all different methods. But
really all very powerfull.

The datatable.select (a static select)
http://msdn.microsoft.com/library/de...electtopic.asp

The dataview.rowfilter (a dynamic select)
http://msdn.microsoft.com/library/de...iltertopic.asp

The datarowcollection.find (a static find)
http://msdn.microsoft.com/library/de...sFindTopic.asp

In most cases the dataview.rowfilter is the best (it is faster than a
datatable.select)

I hope this helps?

Cor