MS SQLCE - How to determine GUID of newly-inserted record?

Author

How to determine GUID of newly-inserted record?

PGallez

2005-10-27, 7:53 am

I'm doing merge replication between SQL Server 2000 and CE running on a PDA.
The PK of the tables are all uniqueidentifiers with a default value of
newid() and the IsRowGuid property set to true.

Immediately after application code running on the PDA executes an INSERT
statement, I need to determine the PK value (a GUID) of the newly-inserted
record. How can I do this?

Since the tables currently also include an identity column, my initial
solution was to query the database for the PK of the record with the maximum
identity value (which would be the most recently inserted record) immediately
after inserting the record; however, I intend to drop the identity columns
due to problems managing ranged identities, so even if this solution worked
reliably (it doesn't), it won't work after I drop the columns. Besides, it's
kludgy.

So, any suggestions re how to determine the PK GUID of a record just
inserted via code?

Darren Shaffer

2005-10-27, 7:53 am

only solution is to insert the new record and then query the table
for that new GUID. there is no automatic function in SQL CE
like @@IDENTITY that works with GUIDs.

--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com

"PGallez" <PGallez@discussions.microsoft.com> wrote in message
news:50AD7B1D-A22E-4009-ADC4- 56E413EBA6D5@microso
ft.com...
> I'm doing merge replication between SQL Server 2000 and CE running on a
> PDA.
> The PK of the tables are all uniqueidentifiers with a default value of
> newid() and the IsRowGuid property set to true.
>
> Immediately after application code running on the PDA executes an INSERT
> statement, I need to determine the PK value (a GUID) of the newly-inserted
> record. How can I do this?
>
> Since the tables currently also include an identity column, my initial
> solution was to query the database for the PK of the record with the
> maximum
> identity value (which would be the most recently inserted record)
> immediately
> after inserting the record; however, I intend to drop the identity columns
> due to problems managing ranged identities, so even if this solution
> worked
> reliably (it doesn't), it won't work after I drop the columns. Besides,
> it's
> kludgy.
>
> So, any suggestions re how to determine the PK GUID of a record just
> inserted via code?

João Paulo Figueira [eMVP]

2005-10-27, 7:53 am

You may also generate the GUIDs yourself and remove the IsRowGuid property
from the columns. This way, you would know the GUID value before inserting
into the table.

--
João Paulo Figueira
Embedded MVP
http://nativemobile.blogspot.com/

"PGallez" <PGallez@discussions.microsoft.com> wrote in message
news:50AD7B1D-A22E-4009-ADC4- 56E413EBA6D5@microso
ft.com...
> I'm doing merge replication between SQL Server 2000 and CE running on a
> PDA.
> The PK of the tables are all uniqueidentifiers with a default value of
> newid() and the IsRowGuid property set to true.
>
> Immediately after application code running on the PDA executes an INSERT
> statement, I need to determine the PK value (a GUID) of the newly-inserted
> record. How can I do this?
>
> Since the tables currently also include an identity column, my initial
> solution was to query the database for the PK of the record with the
> maximum
> identity value (which would be the most recently inserted record)
> immediately
> after inserting the record; however, I intend to drop the identity columns
> due to problems managing ranged identities, so even if this solution
> worked
> reliably (it doesn't), it won't work after I drop the columns. Besides,
> it's
> kludgy.
>
> So, any suggestions re how to determine the PK GUID of a record just
> inserted via code?

PGallez

2005-10-27, 7:53 am

You mean, query the table for the new record using other known attributes,
rather than the (unknown) primary key, right? Similar to what I was doing
with the separate identity column?

"Darren Shaffer" wrote:

> only solution is to insert the new record and then query the table
> for that new GUID. there is no automatic function in SQL CE
> like @@IDENTITY that works with GUIDs.
>
> --
> Darren Shaffer
> ..NET Compact Framework MVP
> Principal Architect
> Connected Innovation
> www.connectedinnovation.com
>
>
> "PGallez" <PGallez@discussions.microsoft.com> wrote in message
> news:50AD7B1D-A22E-4009-ADC4- 56E413EBA6D5@microso
ft.com...
>
>
>

PGallez

2005-10-27, 7:53 am

So I would make a call to the newid() function directly (rather than letting
SQL Server create the value by default)? But doesn't merge replication
require that one of the columns in the table have the IsRowGuid set to True?

"JoÃ£o Paulo Figueira [eMVP]" wrote:

> You may also generate the GUIDs yourself and remove the IsRowGuid property
> from the columns. This way, you would know the GUID value before inserting
> into the table.
>
> --
> JoÃ£o Paulo Figueira
> Embedded MVP
> http://nativemobile.blogspot.com/
>
> "PGallez" <PGallez@discussions.microsoft.com> wrote in message
> news:50AD7B1D-A22E-4009-ADC4- 56E413EBA6D5@microso
ft.com...
>
>
>

Darren Shaffer

2005-10-27, 7:53 am

JP's approach is also a good one, just realize that in CF1.1 there
is no support for generating a new GUID type. There is an MSDN
sample that shows how to do this. Search MSDN for GUID and
Compact Framework.
--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com

"PGallez" <PGallez@discussions.microsoft.com> wrote in message
news:775B39E5-D088-47E9-AD44- 5082776B13BB@microso
ft.com...[color=darkred]
> So I would make a call to the newid() function directly (rather than
> letting
> SQL Server create the value by default)? But doesn't merge replication
> require that one of the columns in the table have the IsRowGuid set to
> True?
>
> "João Paulo Figueira [eMVP]" wrote:
>

PGallez

2005-10-27, 7:53 am

Thanks to both of you for the help. I'll take a look at the suggested
reference and see where to go from here.

"Darren Shaffer" wrote:

> JP's approach is also a good one, just realize that in CF1.1 there
> is no support for generating a new GUID type. There is an MSDN
> sample that shows how to do this. Search MSDN for GUID and
> Compact Framework.
> --
> Darren Shaffer
> ..NET Compact Framework MVP
> Principal Architect
> Connected Innovation
> www.connectedinnovation.com
>
> "PGallez" <PGallez@discussions.microsoft.com> wrote in message
> news:775B39E5-D088-47E9-AD44- 5082776B13BB@microso
ft.com...
>
>
>

Sponsored Links