SQL server datagrids and GUIDs

inserting a new row

[mcavill]

I'm trying to build a windows forms app with MS SQL Server. I have a simple table with a GUID uniqueidentifier field and a text field.

I've added a connection, adapter, and generated the associated queries, and bound that to a datagrid.

The datagrid displays the table fine, but when I try and add a row it throws an error because the ID field is null.

I was trying to set it up without too much coding as I thought that was one of the benefits of working with data grids / adapters etc - however it looks like I'm going to have to catch the insert new record event and create my own GUID.

Is that the case? I don't really need the table key to be a GUID, it can just be an auto incrementing number.

It's SQL server 2003, and vb.net.

Are there any .net / SQL server experts out there that can offer any suggestions?

[aspdaddy]

Uniqueidentifier doesnt support Identity so you have to generate a GUID in you client application and pass it to SQL Server.

I don't really need the table key to be a GUID

Why define it as one then? Use one of the integer datatypes and set it as Identity and then you wont need to pass a value in.

[mcavill]

Thanks for the reply aspdaddy, I was worried my poor explaination was going to go over everyones heads.

I must admit, I switched the sql server DB for an access DB with an AutoNumber in that field and it's fine, but for scalability I'd like to move back over to sql server.

Use one of the integer datatypes and set it as Identity

I'll try that and see what happens, does that work like the AutoNumber type in access?

Uniqueidentifier doesnt support Identity so you have to generate a GUID in you client application

What's the event I need to catch before the DB is updated? - I guess it's the dataset that I need to keep an eye on rather than catch the adapter before it does it's stuff, sorry for all the questions, I'm still learning the workings of .net.

I was hoping I could just leave the datagrid to do all the updates / deletes / inserts etc, but for the insert it looks like I have to catch that event and send my own SQL, is that correct? As mentioned it doesn't have to be a GUID, so I'm off to test it against the SQL server DB again with the type set as identifier.

Thanks for the pointers so far, they're very much appreciated, the Microsoft Press Books I've got don't seem to cover this (probably got the wrong books :P), I'm quite handy with vb / classic asp, but am trying to build new sites with vb.net behind asp....so I'm back at the bottom of the learning curve :P

[joker197cinque]

I don't really need the table key to be a GUID

Go *away* from Guid.

BOL says:

Integer storage size is 4 bytes.

Uniqueidentifier (GUID) storag size is 16 bytes.

You can imagine in large transactional environment what does it mean.

HTH.

[aspdaddy]

Integer storage size is 4 bytes

Smallint is even less & for lookup tables with less than 255 rows like Counties or PhoneTypes :) I use a TinyInt.

[joker197cinque]

I use a TinyInt.

Yes, when I can I do this too.