Q: GUID

[Geoff Jones]

Hi

How do I set a primary key to be a GUID?

That is, using the following code, I can set an integer primary key, but how
do I change it to a GUID?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE books
(" ID INT IDENTITY, Book VARCHAR(50), CONSTRAINT [pk_ID] PRIMARY KEY (ID))",
myDatabaseConnection)

Many thanks in advance

Geoff

 

[Oenone]

How do I set a primary key to be a GUID?

That is, using the following code, I can set an integer primary key,
but how do I change it to a GUID?

This is probably better suited to a SQL Server newsgroup (assuming it's SQL
Server you're using), but this might help. Create a "default" constraint on
the ID field which gives it the value "NewID()". This is a SQL Server
function that generates a GUID. So change your table creation for these two
SQL statements:

CREATE TABLE books
(
[ID] [varchar] (50) NOT NULL ,
[Book] [varchar] (50) NULL
CONSTRAINT [pk_ID] PRIMARY KEY ([ID])
)

ALTER TABLE [dbo].[books] ADD
CONSTRAINT [DF_books_ID] DEFAULT (newid()) FOR [ID]

Now if you insert values into the table and don't specify anything for the
ID field, it'll automatically receive a GUID for each row.

 

[Greg Burns]

CREATE TABLE [dbo].[books] (
[book_id] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[books] ADD
CONSTRAINT [DF_books_bookid_quid] DEFAULT (newid()) FOR [book_id],
CONSTRAINT [PK_books] PRIMARY KEY CLUSTERED
(
[book_id]
) ON [PRIMARY]
GO

HTH,
Greg

 

[Chris Dunaway]

That is, using the following code, I can set an integer primary key, but how
do I change it to a GUID?

Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat, and
replace certain words in my E-Mail address.

 

[Greg Burns]

(O)enone,

I realize this isn't a SQL group, but I was curious why you chose
varchar(50) for your ID field and not the 'uniqueidentifier' data type?

Greg

How do I set a primary key to be a GUID?

That is, using the following code, I can set an integer primary key,
but how do I change it to a GUID?

This is probably better suited to a SQL Server newsgroup (assuming it's SQL
Server you're using), but this might help. Create a "default" constraint on
the ID field which gives it the value "NewID()". This is a SQL Server
function that generates a GUID. So change your table creation for these two
SQL statements:

CREATE TABLE books
(
[ID] [varchar] (50) NOT NULL ,
[Book] [varchar] (50) NULL
CONSTRAINT [pk_ID] PRIMARY KEY ([ID])
)

ALTER TABLE [dbo].[books] ADD
CONSTRAINT [DF_books_ID] DEFAULT (newid()) FOR [ID]

Now if you insert values into the table and don't specify anything for the
ID field, it'll automatically receive a GUID for each row.

 

[Oenone]

I realize this isn't a SQL group, but I was curious why you chose
varchar(50) for your ID field and not the 'uniqueidentifier' data
type?

Ah, primarily because I wasn't aware of it. That's a better solution.

 

[Geoff Jones]

Hi Chris

Like this you mean?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE books
(" ID UNIQUEIDENITFIER, Book VARCHAR(50), CONSTRAINT [pk_ID] PRIMARY KEY
(ID))",
myDatabaseConnection)

Geoff

That is, using the following code, I can set an integer primary key, but how
do I change it to a GUID?

Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat, and
replace certain words in my E-Mail address.

 

[Greg Burns]

You need to set a default of NEWGUID(), or it won't "autonumber" like an
identity field.

Greg

Hi Chris

Like this you mean?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE books
(" ID UNIQUEIDENITFIER, Book VARCHAR(50), CONSTRAINT [pk_ID] PRIMARY KEY
(ID))",
myDatabaseConnection)

Geoff

but

how

do I change it to a GUID?

Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat, and
replace certain words in my E-Mail address.

 

[Geoff Jones]

Hi Greg

Sorry, but I'm a real Newbie to all this and I don't know how to set a
default

Could you show me in the code example I gave?

Many thanks in advance

Geoff

P.S. And sorry for being so dim!!!!!!

You need to set a default of NEWGUID(), or it won't "autonumber" like an
identity field.

Greg

Hi Chris

Like this you mean?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE books
(" ID UNIQUEIDENITFIER, Book VARCHAR(50), CONSTRAINT [pk_ID] PRIMARY KEY
(ID))",
myDatabaseConnection)

Geoff

On Thu, 5 Aug 2004 11:59:44 +0100, Geoff Jones wrote:

That is, using the following code, I can set an integer primary key,

but

how
do I change it to a GUID?


Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat, and
replace certain words in my E-Mail address.

 

[Greg Burns]

I need more info. For being a newbie you are attempting stuff I've never
had to do in code before. :^)

I see you are using oledb, which implies to me that you are not using SQL
server. (If you're not, then all this talk of uniqueidentifiers isn't gonna
fly). If this is an Access database, I don't think you can issue the same
DDL (data denition language) SQL statements as you would for SQL server.

If it is SQL (then I would start using the SqlClient's SqlCommand object),
then I think you have to issue to a second cmd.ExecuteNonQuery after you
create the table with the first command. The second command should be
similar to this (assuming your key field is named "book_id"):

ALTER TABLE [dbo].[books] ADD
CONSTRAINT [DF_books_book_id_quid] DEFAULT (newid()) FOR [book_id],
CONSTRAINT [PK_books] PRIMARY KEY CLUSTERED
(
[book_id]
) ON [PRIMARY]

What are you trying to accomplish, why do you need to make your table on the
fly?

Greg

Hi Greg

Sorry, but I'm a real Newbie to all this and I don't know how to set a
default

Could you show me in the code example I gave?

Many thanks in advance

Geoff

P.S. And sorry for being so dim!!!!!!

You need to set a default of NEWGUID(), or it won't "autonumber" like an
identity field.

Greg

Hi Chris

Like this you mean?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE books
(" ID UNIQUEIDENITFIER, Book VARCHAR(50), CONSTRAINT [pk_ID] PRIMARY KEY
(ID))",
myDatabaseConnection)

Geoff

"Chris Dunaway" <"dunawayc[[at]_lunchmeat_sbcglobal[dot]]net"> wrote in
message On Thu, 5 Aug 2004 11:59:44 +0100, Geoff Jones wrote:

That is, using the following code, I can set an integer primary

key,
but

how
do I change it to a GUID?


Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat, and
replace certain words in my E-Mail address.

 

[Geoff Jones]

Hi Greg

I've been given a project which a colleague was working on. This isn't my
field!!! Still, it is nice to learn something new.

This is an Access database project. I'm trying to create a new database "on
the fly" with the primary key set to a GUID. I would have thought this an
easy thing to do - obviously, I am very wrong

Geoff

I need more info. For being a newbie you are attempting stuff I've never
had to do in code before. :^)

I see you are using oledb, which implies to me that you are not using SQL
server. (If you're not, then all this talk of uniqueidentifiers isn't gonna
fly). If this is an Access database, I don't think you can issue the same
DDL (data denition language) SQL statements as you would for SQL server.

If it is SQL (then I would start using the SqlClient's SqlCommand object),
then I think you have to issue to a second cmd.ExecuteNonQuery after you
create the table with the first command. The second command should be
similar to this (assuming your key field is named "book_id"):

ALTER TABLE [dbo].[books] ADD
CONSTRAINT [DF_books_book_id_quid] DEFAULT (newid()) FOR [book_id],
CONSTRAINT [PK_books] PRIMARY KEY CLUSTERED
(
[book_id]
) ON [PRIMARY]

What are you trying to accomplish, why do you need to make your table on the
fly?

Greg

Hi Greg

Sorry, but I'm a real Newbie to all this and I don't know how to set a
default

Could you show me in the code example I gave?

Many thanks in advance

Geoff

P.S. And sorry for being so dim!!!!!!

You need to set a default of NEWGUID(), or it won't "autonumber" like an
identity field.

Greg

Hi Chris

Like this you mean?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE books
(" ID UNIQUEIDENITFIER, Book VARCHAR(50), CONSTRAINT [pk_ID] PRIMARY KEY
(ID))",
myDatabaseConnection)

Geoff

"Chris Dunaway" <"dunawayc[[at]_lunchmeat_sbcglobal[dot]]net"> wrote in
message On Thu, 5 Aug 2004 11:59:44 +0100, Geoff Jones wrote:

That is, using the following code, I can set an integer primary key,
but
how
do I change it to a GUID?


Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat, and
replace certain words in my E-Mail address.

 

[Greg Burns]

Since it is Access, you will not be able to use the uniqueidentifier data
type. You also won't be able to use the NEWGUID() function. Those are both
SQL server specific.

My Access exposure is limited to 97 version. Do the newer version have
better support for GUIDs?

I see in 97 that you can use an Access datatype autonumber and change the
field size to "Replication ID". It seems to be making GUIDs.
Unfortunately, I have no idea how to make such a beast using DDL.

Greg

Hi Greg

I've been given a project which a colleague was working on. This isn't my
field!!! Still, it is nice to learn something new.

This is an Access database project. I'm trying to create a new database "on
the fly" with the primary key set to a GUID. I would have thought this an
easy thing to do - obviously, I am very wrong

Geoff

I need more info. For being a newbie you are attempting stuff I've never
had to do in code before. :^)

I see you are using oledb, which implies to me that you are not using SQL
server. (If you're not, then all this talk of uniqueidentifiers isn't gonna
fly). If this is an Access database, I don't think you can issue the same
DDL (data denition language) SQL statements as you would for SQL server.

If it is SQL (then I would start using the SqlClient's SqlCommand object),
then I think you have to issue to a second cmd.ExecuteNonQuery after you
create the table with the first command. The second command should be
similar to this (assuming your key field is named "book_id"):

ALTER TABLE [dbo].[books] ADD
CONSTRAINT [DF_books_book_id_quid] DEFAULT (newid()) FOR [book_id],
CONSTRAINT [PK_books] PRIMARY KEY CLUSTERED
(
[book_id]
) ON [PRIMARY]

What are you trying to accomplish, why do you need to make your table on the
fly?

Greg

Hi Greg

Sorry, but I'm a real Newbie to all this and I don't know how to set a
default

Could you show me in the code example I gave?

Many thanks in advance

Geoff

P.S. And sorry for being so dim!!!!!!

You need to set a default of NEWGUID(), or it won't "autonumber"

like

an

identity field.

Greg

Hi Chris

Like this you mean?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE
books
(" ID UNIQUEIDENITFIER, Book VARCHAR(50), CONSTRAINT [pk_ID]

PRIMARY
KEY

(ID))",
myDatabaseConnection)

Geoff

"Chris Dunaway" <"dunawayc[[at]_lunchmeat_sbcglobal[dot]]net">

wrote
in

message On Thu, 5 Aug 2004 11:59:44 +0100, Geoff Jones wrote:

That is, using the following code, I can set an integer

primary
key,

but
how
do I change it to a GUID?


Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores

,lunchmeat,
and

replace certain words in my E-Mail address.

 

[Greg Burns]

This may be of help to you:

http://www.smithvoice.com/dbcopier.aspx

Greg

Since it is Access, you will not be able to use the uniqueidentifier data
type. You also won't be able to use the NEWGUID() function. Those are both
SQL server specific.

My Access exposure is limited to 97 version. Do the newer version have
better support for GUIDs?

I see in 97 that you can use an Access datatype autonumber and change the
field size to "Replication ID". It seems to be making GUIDs.
Unfortunately, I have no idea how to make such a beast using DDL.

Greg

Hi Greg

I've been given a project which a colleague was working on. This isn't my
field!!! Still, it is nice to learn something new.

This is an Access database project. I'm trying to create a new database "on
the fly" with the primary key set to a GUID. I would have thought this an
easy thing to do - obviously, I am very wrong

Geoff

I need more info. For being a newbie you are attempting stuff I've never
had to do in code before. :^)

I see you are using oledb, which implies to me that you are not using SQL
server. (If you're not, then all this talk of uniqueidentifiers isn't gonna
fly). If this is an Access database, I don't think you can issue the same
DDL (data denition language) SQL statements as you would for SQL server.

If it is SQL (then I would start using the SqlClient's SqlCommand object),
then I think you have to issue to a second cmd.ExecuteNonQuery after you
create the table with the first command. The second command should be
similar to this (assuming your key field is named "book_id"):

ALTER TABLE [dbo].[books] ADD
CONSTRAINT [DF_books_book_id_quid] DEFAULT (newid()) FOR [book_id],
CONSTRAINT [PK_books] PRIMARY KEY CLUSTERED
(
[book_id]
) ON [PRIMARY]

What are you trying to accomplish, why do you need to make your table

on
the

fly?

Greg


Hi Greg

Sorry, but I'm a real Newbie to all this and I don't know how to set a
default

Could you show me in the code example I gave?

Many thanks in advance

Geoff

P.S. And sorry for being so dim!!!!!!

You need to set a default of NEWGUID(), or it won't "autonumber"

like

an
identity field.

Greg

Hi Chris

Like this you mean?

Dim databaseCommand As OleDbCommand = New OleDbCommand("CREATE TABLE
books
(" ID UNIQUEIDENITFIER, Book VARCHAR(50), CONSTRAINT [pk_ID] PRIMARY
KEY
(ID))",
myDatabaseConnection)

Geoff

"Chris Dunaway" <"dunawayc[[at]_lunchmeat_sbcglobal[dot]]net"> wrote
in
message On Thu, 5 Aug 2004 11:59:44 +0100, Geoff Jones wrote:

That is, using the following code, I can set an integer primary
key,
but
how
do I change it to a GUID?


Make your column type a UNIQUEIDENITFIER type

--
Chris

dunawayc[AT]sbcglobal_lunchmeat_[DOT]net

To send me an E-mail, remove the "[", "]", underscores ,lunchmeat,
and
replace certain words in my E-Mail address.