|
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! 
|
|
|
|
|
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"
|
|
|
|
|
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!
|
|
|
|
|
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"
|
|
|
|
|
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 
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
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
"I am too late but i will never give up"
|
|
|
|
|
|
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
|
|
|
|
|
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”
|
|
|
|
|
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.
|
|
|
|
|
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”
|
|
|
|
|
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
|
|
|
|
|
leckey wrote: ORDER BY order_by
Aren't you missing either ASC or DESC to tell it how you want to order the fetched recordset?
What do you mean by order_by?
Usually it is a field or column name that comes after the ORDER BY.
|
|
|
|
|
hey, guys. i'm trying to build a generic usp, which i dont think is possible, so i may end up just making several different usp's that take the same parameters...what i'd love to do is
select @returnColumn
from @table
where @searchColumn=@searchValue
i know the above code wont work, but does anyone know some nifty trick to make something like that work?
Thanks
John
|
|
|
|
|
Something like this will work:
CREATE PROCEDURE GetColumn
@returnColumn varchar(100),@table varchar(100),@searchColumn varchar(100), @searchValue varchar(100)
AS
exec('select ' + @returnColumn + ' from ' + @table + ' where ' + @searchColumn + ' = ' + @searchValue)
But be sure to check out this article: SqlInjectionAttacks[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks i never would have thought of that...i've been thinking about Sql Injection Attacks since i was told that i'd have to add this kind of functionality to the app...luckily this is only going to be run by a handfull of people in our office, and can only be run in our office...so all i should need to do is make sure that the input is alphanumeric characters only, no puncuntation(sp?) or spiecal characters i.e. *,%, etc.. and i should be ok...mabye.
thanks again for your help
john
|
|
|
|
|
i have sql server 2000 DATABASE i want to add columns to store sounds and another to store video files what type of column for each one (sound files&videos files)
ma_refay
|
|
|
|
|
|
field type should be "image", unless you know they will all be under 8000 bytes long, then you could use varbinary. image fields can be up to 2GB in size.
|
|
|
|
|
Hi,
When I do a normal select in a stored procedure, must I include the BEGIN and END, or is it not necessary?
Regards,
ma se
|
|
|
|
|
BEGIN and END surround a block of statements, allowing them to be treated as a single statement in some circumstances. They're really T-SQL keywords rather than part of SQL. The T-SQL control constructs (IF, WHILE) only control a single statement or a block of statements. I would recommend when using IF and WHILE statements in T-SQL, that you always use BEGIN and END to save any confusion during maintenance. This is the same recommendation as in C, C++, Java, C# where you should always use { } to delimit controlled statements.
Example:
IF EXISTS (
SELECT * FROM authors WHERE au_lname = 'Green' )
SELECT * FROM titleauthor
SELECT * FROM titlesDespite the poor formatting, the second SELECT will always be performed, not just when there's an author with the last name of Green. If you wanted to ensure that both statements would only execute when the IF condition was true, you should do::
IF EXISTS (
SELECT * FROM authors WHERE au_lname = 'Green' )
BEGIN
SELECT * FROM titleauthor
SELECT * FROM titles
END
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
You can also use ELSE as well
<br />
create proc myProc @Account int AS<br />
<br />
-- Select ALL RECORDS<br />
if @Account = 0 <br />
BEGIN<br />
SELECT * FROM Accounts<br />
END<br />
ELSE<br />
-- SELECT SINGLE ACCOUNT<br />
BEGIN<br />
SELECT * FROM Account<br />
WHERE ID = @Account<br />
END<br />
<br />
Hope tihs helps
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Here I use Access to build up a database with a time/date field.
How can I query the database according to the time/date field, that is
SELECT * FROM [DATABASE] WHERE TIMEDATE < 20:20? Can time/date field be operated with > = and < ?Thanks
|
|
|
|
|
Select from [database.table] where [table.field] < '20;20 00:00'
Got the idea
Glen Harvy
|
|
|
|
|
Sorry, not very clear.Factually I have two question to ask:
1. how to input the date-time format: "1987-12-29 12:23:45" into Access DB with InputMask?
2. if I want to query the record that has the date-time before "1987-12-29 12:23:45" and after "1986-10-15 23:25:26", how do I write the sql string?Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
