• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Populating the PK for a table automatically?

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();      
rac.UpdateEvtRequest(this.UserId,DateTime.Parse(this.txtDate.Text),sb.ToString());
.
.
.
}

//controller

public class evtReqAppController
{
//get request information on submitted application

//update UpdateEvtRequest
public void UpdateEvtRequest(int userId, DateTime dateSubmitted, string notes)
{
//insert into eEventRequestInfo table            
                  DataProvider.Instance().eEventRequestInfo(userId,dateSubmitted,notes);
}
}

0
sabrina_spillane
Asked:
sabrina_spillane
  • 2
  • 2
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
 
sabrina_spillaneAuthor Commented:
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

AS
SET NOCOUNT ON

/* DECLARATIONS */
DECLARE @PersonId int

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


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

INSERT INTO [eEventRequestInfo]
(
[UserId],
[DateSubmitted],
[Notes]
)
VALUES(
@UserId,
@Date,
@Notes)


GO
0
 
Carl TawnSystems and Integration DeveloperCommented:
Apparently you cannot assign to an nText variable. See this similar thread: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21644849.html
0
 
sabrina_spillaneAuthor Commented:
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now