Database

7-Jul-06 7:07

How could I add a new record to a table (Using a stored procedure) that has only one column(ID column) that has the Identity property enabled with Seed=1 and Increment=1?

For example if the table has two columns where the second column is CustomerName we should use INSERT INTO to add only a value to the second column because the first column (ID column) will have a new value automatically because of the Identity properties mentioned above.

"I am too late but i will never give up"

-- modified at 13:48 Friday 7th July, 2006

Elina Blank7-Jul-06 8:11

7-Jul-06 8:11

In your stored proecdure write the following:
SET IDENTITY_INSERT [TableName] ON
BEFORE inserting new record

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

TheEagle7-Jul-06 9:29

7-Jul-06 9:29

That is exactly what I needed but in the following code:

--The Identity column name: FieldNO
SET IDENTITY_INSERT [ProjectField] ON
INSERT INTO ProjectField_Locale (FieldNO,CultureID,FieldName,FieldDescription)
VALUES (@@Identity,dbo.GetCultureID(@CultureName),@FieldName,@FieldDescription)

The ProjectField_Locale has another ID column (FieldCultureID) with the same Identity properties(I didnt write its name in the INSERT INTO statement because it will be added automatically too) so which Identity value I will get from @@Identity(the one from [ProjectField] table or the new one in ProjectField_Locale table?).

"I am too late but i will never give up"

Elina Blank7-Jul-06 10:34

7-Jul-06 10:34

I have this from the msdn Help:
"You can set the identity properties on only one column per table"

So, how can you have two columns with the same Identity property?

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

TheEagle7-Jul-06 22:37

7-Jul-06 22:37

I didn't mean in the same table.I meant FieldNO(Primary Key) in the first table(ProjectField) and FieldCultureID(Primary Key) in the second table(ProjectField_Locale).

The FieldNO in the second table has the Identity Property Disabled and it is a Foreign Key.

Sorry if I could't explain my question because it is complex.

"I am too late but i will never give up"

Elina Blank10-Jul-06 4:04

10-Jul-06 4:04

I think, it will be the identity of the ProjectField_Locale, but i am not 100% sure.
You can always test it with print statement.
Sorry, for not being able to help too much Frown | :(

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

TheEagle11-Jul-06 1:36

11-Jul-06 1:36

I am the one who should say Sorry...

I didnt understand the statement :

SET IDENTITY_INSERT [table name] ON

But after viewing several articles through the internet I understood it and then I found a solution for my problem and it worked perfectly:

DECLARE @AA INT
DECLARE @bb INT
SET @AA=(SELECT MAX(FieldNO) FROM ProjectField)+1
SET IDENTITY_INSERT [ProjectField] ON
INSERT INTO ProjectField (FieldNO)
VALUES (@AA)
SET @BB=@@Identity

INSERT INTO ProjectField_Locale (FieldNO,CultureID,FieldName,FieldDescription)
VALUES (@BB,dbo.GetCultureID(@CultureName),@FieldName,@FieldDescription)

INSERT INTO ProjectField_Locale (FieldNO,CultureID,FieldName,FieldDescription)
VALUES (@BB,dbo.GetCultureID(@CultureName2),@FieldName2,@FieldDescription2)

Ofcourse your first respond helped me to go in the right way..Thank you very much..I hope that we meet again in another exciting conversation Smile | :)

"I am too late but i will never give up"

Elina Blank11-Jul-06 2:32

11-Jul-06 2:32

Happy to hear you found the solution.
Sorry, that I wasn't able to you very clear.

TheEagle wrote:
Ofcourse your first respond helped me to go in the right way..Thank you very much..I hope that we meet again in another exciting conversation

Hope so too Smile | :)

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

ORDER BY question

leckey7-Jul-06 6:51

leckey

7-Jul-06 6:51

I have a stored proc which populates a drop-down box in an ASP.NET datagrid. The user enters a part number and the datagrid populates based on that part number. The datagrid is already in edit mode using template columns.

What I would like to do on the SQL side if possible is the following:
a) If the part number does not exist, populate the drop-down box with all part types, and order by description. (Okay, this is no problem.)
b) If the part number DOES exist, populate the drop-down box with all part types, but list the saved description first, then list the rest by description.

This is what I have thus far:

CREATE PROCEDURE [dbo].[GetPartDescriptions]<br />
<br />
@PartNumber varchar (60)<br />
<br />
 AS<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
SELECT ID as ptID, Description from PartTypes pt ORDER by Description<br />
END<br />
<br />
GO

Jerry Hammond8-Jul-06 4:54

Jerry Hammond

8-Jul-06 4:54

Sorry for not answering your question, but I have to ask you this question: Why would you have parts with the same part number? I would think that would be unique for each part type. No?

“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”

Paul Conrad8-Jul-06 19:54

Paul Conrad

8-Jul-06 19:54

Jerry Hammond wrote:
Why would you have parts with the same part number

Parts is the table leckey is using and partnumber is the field.

Yes, the partnumber field ought to be unique for each part type.

Jerry Hammond9-Jul-06 3:22

Jerry Hammond

9-Jul-06 3:22

Maybe I'm the confused one. Part b of your requirements seem to imply multiple parts (or part types) with the same part number.

“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”

leckey10-Jul-06 3:17

leckey

10-Jul-06 3:17

Sorry for wording my question poorly. The part number is indeed unique. What I am looking for is if the user enters a part number that already exists in the database, the dropdown list populates with the part type first, then the remaining part type choices alphabetically. If the part does not exist, just order alphabetically. I had a friend help me last week and got this:

CREATE PROCEDURE [dbo].[GetPartDescriptions2]<br />
<br />
@PartType varchar (60)<br />
<br />
 AS<br />
<br />
SELECT PartTypes.ID, PartTypes.description,<br />
CASE when PartTypes.ID = @PartType then 1<br />
else PartTypes.ID<br />
end as order_by<br />
from PartTypes INNER JOIN parts p on p.PartTypeID = PartTypes.ID<br />
<br />
ORDER BY order_by<br />
<br />
But I was getting confused and the bolded parts are not right. I am passing in the part number so it should be something like this:<br />
<br />
<code>CREATE PROCEDURE [dbo].[GetPartDescriptions3] <br />
@PartNumber varchar (60)<br />
<br />
 AS<br />
<br />
SELECT PartTypes.ID, PartTypes.description, Parts.PartNumber, Parts.PartTypeID<br />
CASE when PartTypes.ID = Parts.PartTypeID then 1<br />
else PartTypes.ID<br />
end as order_by<br />
from PartTypes INNER JOIN parts p on p.PartTypeID = PartTypes.ID<br />
<br />
ORDER BY order_by<br />
GO

But enterprise manager doesn't like that. Asked my friend and she sent me this:

You could get the Part Type by

Declare @PartTypeId int

select @PartTypeId = PartTypeId
from PartTypes where Partnumber = @PartNumber

I'm now having trouble figuring out where to stick this new select statement in the entire code.

GO