How To Insert into a table with a uniqueidentifier as primary key?

I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId

My VB code is this.

ProtectedSub btncreate_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btncreate.Click'set connection stringDim errstrAsString =""Dim conn =New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")'set parameters for SPDim cmdcommand =New SqlCommand("sprocInsertNewReport", conn)

cmdcommand.commandtype = CommandType.StoredProcedure

cmdcommand.parameters.add(

"@.UserName", Session("UserName"))

cmdcommand.parameters.add(

"@.Week", vbNull)

cmdcommand.parameters.add(

"@.Date", vbDate)

cmdcommand.parameters.add(

"@.StartTime", vbNull)

cmdcommand.parameters.add(

"@.EndTime", vbNull)

cmdcommand.parameters.add(

"@.HeatTicket", vbNull)

cmdcommand.parameters.add(

"@.Description", vbNull)

cmdcommand.parameters.add(

"@.TakenAs", vbNull)

cmdcommand.parameters.add(

"@.Dinner", vbNull)

cmdcommand.parameters.add(

"@.Hours", vbNull)

cmdcommand.parameters.add(

"@.Rate", vbNull)

cmdcommand.parameters.add(

"@.PayPeriod", vbNull)

cmdcommand.parameters.add(

"@.LastSave", vbNull)

cmdcommand.parameters.add(

"@.Submitted", vbNull)

cmdcommand.parameters.add(

"@.Approved", vbNull)

cmdcommand.parameters.add(

"@.PagerDays", vbNull)

cmdcommand.parameters.add(

"@.ReportEnd", vbNull)Try'open connection here

conn.Open()

'Execute stored proc

cmdcommand.ExecuteNonQuery()

Catch exAs Exception

errstr =

""'An exception occured during processing.'Print message to log file.

errstr =

"Exception: " & ex.MessageFinally'close the connection immediately

conn.Close()

EndTryIf errstr =""Then

Server.Transfer(

"TimeSheetEntry.aspx")EndIf

My SP looks like this

ALTER PROCEDURE

sprocInsertNewReport

@.UserNamenvarchar(256),

@.Week

Int,

@.Date

Datetime,

@.StartTime

Datetime,

@.EndTime

DateTime,

@.HeatTicket

int,

@.Description

nvarchar(max),

@.TakenAs

nchar(10),

@.Dinner

Nchar(10),

@.Hours

Float,

@.Rate

Float,

@.PayPeriod

int,

@.LastSave

Datetime,

@.Submitted

Datetime,

@.Approved

DateTime,

@.PagerDays

int,

@.ReportEnd

DateTimeASINSERT INTO

ReportDetails

(

rpUserName,

rpWeek,

rpDate,

rpStartTime,

rpEndTime,

rpHeatTicket,

rpTicketDescription,

rpTakenAs,

rpDinnerPremium,

rpHours,

rpRate,

rpPayPeriod,

rpLastSaveDate,

rpSubmittedDate,

rpApprovedDate,

rpPagerDays,

rpReportDueDate

)

VALUES

(

@.Username,

@.Week,

@.Date,

@.StartTime,

@.EndTime,

@.HeatTicket,

@.Description,

@.TakenAs,

@.Dinner,

@.Hours,

@.Rate,

@.PayPeriod,

@.LastSave,

@.Submitted,

@.Approved,

@.PagerDays,

@.ReportEnd

)

RETURN

Any Ideas?

thx!

I am not sure what you mean by "go up one each time." Uniqueidentifiers don't work that way. You have three choices:

1 - generate a guid on the front end and pass it to the proc as the id

2 - Generate the guid within the proc

3 - set the default value for the key field to newid()

|||Why not use an identity column?|||

mpswaim:

Why not use an identity column?

Good question because a GUID is 16bytes binary data type to be used with care, while IDENTITY is INT and a property to the column.

|||

I am using VS2005 Server Explorer, is there an option to set and say int to an identity?

I do not see any ways to do so

|||

I changed it to an int and found the as Identity property and it works!

Thanks for the help!

|||

You can do it with SQL Management Studio (I believe that there's a free version available), or Enterprise Manager. Unfortunately I don't see a good way to alter a table to make an existing column an identity column.

|||You just do ANSI SQL ALTER table SET IDENTITY because IDENTITY is defined in ANSI SQL. Hope this helps.