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 hereconn.Open()
'Execute stored proccmdcommand.ExecuteNonQuery()
Catch exAs Exceptionerrstr =
""'An exception occured during processing.'Print message to log file.errstr =
"Exception: " & ex.MessageFinally'close the connection immediatelyconn.Close()
EndTryIf errstr =""ThenServer.Transfer(
"TimeSheetEntry.aspx")EndIfMy 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 INTOReportDetails
(
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
)
RETURNAny 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.
