0

I am having a problem with trying to create new rows in sql server with a typed data adapter where the primary key is set to GUID and the default value is NewSequentialID()

The column friendInviteID is a GUID and has a default value of NewSequentialID()

        Using myDT As New DAL.mbrFriendInvites.mbrFriendInvitesDataTable
            Using myTA As New DAL.mbrFriendInvitesTableAdapters.mbrFriendInvitesTableAdapter

                Dim row As DAL.mbrFriendInvites.mbrFriendInvitesRow = myDT.NewmbrFriendInvitesRow
                With row
                    .friendInviteID = Nothing
                    .mbrID = 11
                    .appID = 2
                    .contactEmail = "blah@blah.com"
                    .contactName = "blah blah"
                    .timesSent = 0
                End With

                myDT.AddmbrFriendInvitesRow(row)

                Dim result As Integer = myTA.Update(myDT)
                HttpContext.Current.Response.Write("rows updated: " & result)

            End Using
        End Using

When I insert a new row using the above method, SQL Server sets the value of friendInviteID to: 00000000-0000-0000-0000-000000000000

It doesn't seem to want to use the NewSequentialID() default value.

I can manually create a GUID and insert it that way , but that will not create a SequentialID.

What am I missing?

George Filippakos
  • 16,359
  • 15
  • 81
  • 92
  • Where have you used your scalar function as default value, in SQL-Server or in your typed DataSet? If it's really a strong typed DataSet, there is also an autogenerated function `brFriendInvitesRow.setfriendInviteIDNull()` that you should use. – Tim Schmelter Dec 13 '11 at 18:06
  • It is set in SQLServer as the default value. If I open the table in sql server management studio and manually add a row to the table, it will auto insert a sequentialID - so i know it is working in sql server. There is no setfriendInviteIDNull because it is a primary key and cannot be null. – George Filippakos Dec 13 '11 at 18:14
  • If it cannot be `null` in your DataSet, an exception should be raised or it explains why `nothing` gets translated to the default GUID. You should try to allow null in the DataAdapter. – Tim Schmelter Dec 13 '11 at 18:26
  • I tried setting it to allow Nulls in the data adapter but it still throws the error: Column 'friendInviteID' does not allow nulls. – George Filippakos Dec 13 '11 at 18:31
  • I concur with @Tim here. I was just about tho write the same thing. The DataSet needs to have the AllowDbNull set to true for that column. IIRC. – RockyMountainHigh Dec 13 '11 at 18:34
  • I tried that and it doesn't work, still get the error Column 'friendInviteID' does not allow nulls – George Filippakos Dec 13 '11 at 18:38

1 Answers1

0

Change your TableAdapter's InsertCommand so that it creates the GUID or calls any scalar valued UDF that returns it:

Click here for full-view

enter image description here

If you simply want to create a GUID in sql-server - you could also use newid():

INSERT INTO Test (idTest, Value, Test)
VALUES    (newid(),@Value,@Test);

You can also use a Stored-Procedure as CommandType of the InsertCommand that does that all and returns the actual values via output parameters.

I've asked myself recently how to retrieve those primary keys: Retrieve identity value from inserted record's primary key

If you need more informations on this issue i'll have a look how i've implemented it. Anyhow you need InputOutput as Direction for your InsertCommand's parameters then.

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I dont like to mofidy the autogenerated adapters - can be a real pain when they are overwritten by sombody else. I guess I could write a stored proc that returns a new sequentialID and then use that when inserting row instead. It seems strange that the default value does not seem to be supported for GUIDs – George Filippakos Dec 13 '11 at 19:02
  • There are many things that are not or poorly implemented in typed DataSet. There's a reason for being obsolete and not longer supported. You often need to work around things. Have you seen that i've edited my answer. The best way(when using typed DataSets) in this case would be to use a SP that inserts the row and returns all dynamic values. But in my opinion a `uniqueidentifier` is a poor choice for a primary key(as @gbn already has noted). – Tim Schmelter Dec 13 '11 at 19:14
  • I assume that a uniqueidentifier cannot be returned via `SCOPE_IDENTITY`, therefore the return value cannot set. Maybe that is the reason for sending `00000000-0000-0000-0000-000000000000` to the database. http://stackoverflow.com/a/1510529/284240 – Tim Schmelter Dec 13 '11 at 19:25
  • I'm using VS 2010 and was not aware that typed datasets were obsolete??! what have they neen replaced with - link to sql? I don't need another data abstraction layer :( over the top. – George Filippakos Dec 13 '11 at 19:42
  • Datasets still have their place, for example if you need fast(test) results or for small projects. Or if you need to load data into memory for whatever reason(comparing between different databases etc). Another option is to use raw ADO.NET in codebehind without an abstraction layer. – Tim Schmelter Dec 13 '11 at 20:03
  • I have accepted your answer because it provides a workaround to the problem. It seems that DataAdapters do not support GUIDs as auto generated fields when creating new records. – George Filippakos Dec 15 '11 at 11:06