Identity help plz {Resolved} - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identity help plz {Resolved}

Author

Topic

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-10 : 11:51:28

I'm fairly new to SQL so finding similarities and differences between sql and access have been fun. I'm trying to use a autonum feature (access) in sql. I finally found that Identity is sql's form of autonum.

I have a asp.net page that a user fills in client's information and the med_chart# is the PK or Identity in this case. With each new client I want to incriment the customer number by one.

So here is the process:

1.user fills in clients information (fname, lname, minitial, dob, [date])
2.clicks submit
3.asp does validation
4.confirmation page showing the user the inputted information AND customer number issued

I can get the page to work if I put ALL the information manually. However if I leave the med_chart# blank I get a invalid string error. I was thinking that sql would incriment the number by itself if I left the text box blank for customer number.

ANY help would be greatly appreciated.

Here is my code snippet that works if I put in med_chart# manually:

insert_query = "SET IDENTITY_INSERT chartassignment ON " & _
"INSERT INTO chartassignment (fname, lname, minitial, dob, [date], med_chart#) VALUES ('" & tmpFirstName & "' ,'" & tmpLastName & "' ,'" & tmpMidInit & "' ,'" & tmpDOB & "' ,'" & tmpDate & "','" & tmpMedChart & "') " & _
"SET IDENTITY_INSERT chartassignment OFF"

Jack of all trades, Master of none!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-10 : 12:15:04

First, move all of your SQL into a stored procedure, like so
Create Procedure dbo.Insert_Chart_Assignment
( @tmpFirstName varchar(50)
,@tmpLastName varchar(50)
,@tmpMidInit char(1)
,@tmpDOB datetime
,@tmpDate datetime
)

AS

SET NOCOUNT ON;

INSERT INTO chartassignment
(fname, lname, minitial, dob, [date])
VALUES
(@tmpFirstName ,@tmpLastName,@tmpMidInit ,@tmpDOB ,@tmpDate,)

SELECT SCOPE_IDENTITY() --<<--- Here is the ID# of the record you just inserted

Then, from your asp page, you can do and ExecuteScalar function on your stored proc. The newly inserted ID number will be returned.

DO NOT allow your program to insert an identity. Let SQL do it for you.

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-10 : 18:29:28

Do I want to create this procedure in enterprise manager? or in my webpage code? I'm assuming enterprise manager.

So how do I use this stored procedure with my webpage?

Jack of all trades, Master of none!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-11 : 10:47:11

declare a variable to hold your new ID
Dim foo as int

now, execute the stored proc

foo = YourCommandObject.ExecuteScalar("Insert_Chart_Assignment", YourParametersHere)
this assumes .NET as your back end.

If this is not clear enough, look up ExecuteScalar in the .NET help.

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-11 : 11:52:24

Thanks!

I will try it out and do some poking around help.

Jack of all trades, Master of none!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources