Populating the PK for a table automatically?

Posted on 2006-05-08
Last Modified: 2010-04-16
I have a table created in SQL Server that contains four fields. The primary key is an int but I was just wondering how can I make this an autonumber. What I mean by this is if I want to Insert into the table some information how do I have this field incremented automatically? So I select something different in the table or will I use a for loop in the source code. If I am using a for loop I guess it will have to be used with the insert controller statement that I have created and which I call to insert the data that the user wants to save? How would I do this, has anyone got any ideas? Thanks in advance for all the help.

Here is the source code for the controller as follows: The controller doesn't have a field for the primary key. I want the primary key to be automatically generated when I insert the other fields. Do I still have to include it in the controller?
The error I am getting is as follows: Cannot insert the value NULL into column ApplicationId.." How do I fill the ApplicationId which is the primary key for the table automatically when I insert the values for the other fields in the table.

private void btnSubmit_Click(object sender, System.EventArgs e)
.//here i have a string builder that retrieves the information that the user inserts into .the application.

//next update the table with the required information applicationId,userid,body, and date
evtReqAppController rac = new evtReqAppController();      


public class evtReqAppController
//get request information on submitted application

//update UpdateEvtRequest
public void UpdateEvtRequest(int userId, DateTime dateSubmitted, string notes)
//insert into eEventRequestInfo table            

Question by:sabrina_spillane
    LVL 52

    Accepted Solution

    Under the column options for your PrimaryKey field you need to set the "Identity" option to True. This specifies that SQL should give the column an incremental value with each new record.

    Author Comment

    thanks that is great.
    it is not saving the information to the table and everything, however when i do a select on the notes column it is only return half the information that it should contain. I have set it to varchar (255) but I don't think this is enough for to cover all the information. So now I just set it to nText however when I change the SP I get the following error:

    "the assignment operator cannot take a nText datatype as an argument. the nText data type cannot be compared/sorted, except when using ISNULL/ LIKE operators? Do you have any idea what I might be doing wrong in my SP.
    Here is the syntax in the SP. Thanks again for all the help.

    CREATE PROCEDURE [dbo].[InsertEvtRequestInfo]

    @UserId int,
    @Date datetime,
    @Notes nText


    DECLARE @PersonId int

    EXEC dnnA_IDsFromUserId @UserId = @UserId, @PersonId = @PersonId OUTPUT

    @UserId = NULLIF(NULLIF(@UserId, 0), -1),
    @Date = NULLIF(@Date, ''),
    @Notes = NULLIF(@Notes, '')

    INSERT INTO [eEventRequestInfo]

    LVL 52

    Expert Comment

    by:Carl Tawn
    Apparently you cannot assign to an nText variable. See this similar thread:

    Author Comment

    I tried something in the mean time and it must be just pot luck! But I took out the following line from the SP @Notes = NULLIF(@Notes, '')
     and when i did that it compiled without any errors using the nText.
    Thanks again for everything.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Article by: Ivo
    Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
    Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now