Hi,can somebody explain me, why the IDENT_CURRENT from an empty table is 1?After insert of the first record it is still 1 (assuming that start valueis 1) which is okay. But if i check the IDENT_CURRENT from a newly createdtable the result should be NULL, or not?bye,Helmut
I have a SSIS package that imports data into a staging table from an excel sheet (This works fine). From the staging tabler i want it to insert the values into my members table, take that unique indentityID that gets created and insert the other values into other tables for that member that was just created.
In the staging table, i have all the values for a single member. But the structure of the database needs all the values inserted into seperate tables. There is no conditions ID in my members table, so the member first has to be created and from there i need to use the newly created member's MemberID and insert the conditions into a seperate table using the MemberID
I have created some sample data that can be used. I think i have an idea of how to do it, but i'm not totally sure if it will work that way, i have however included it in the sample data.
Code Snippet DECLARE @ImportedStagingData TABLE ( ID INT IDENTITY(1,1), Name VARCHAR(50), Surname VARCHAR(50), Email VARCHAR(50), [Chronic Heart Failure] INT, [Colon Cancer] INT ) INSERT INTO @ImportedStagingData VALUES ('Carel', 'Greaves', 'CarelG@Email.com', 1,0) INSERT INTO @ImportedStagingData VALUES ('Jamie', 'Jameson', 'JamieJ@Email.com', 1,1) INSERT INTO @ImportedStagingData VALUES ('Sarah', 'Bolls', 'SarahB@Email.com', 0,1) INSERT INTO @ImportedStagingData VALUES ('Bells', 'Scotch', 'BellsS@Email.com', 1,1) INSERT INTO @ImportedStagingData VALUES ('Stroh', 'Rum', 'StrohR@Email.com', 0,0) DECLARE @Conditions TABLE ( ID INT IDENTITY(1,1), Condition VARCHAR(50) ) INSERT INTO @Conditions VALUES ('Chronic Heart Failure') INSERT INTO @Conditions VALUES ('Colon Cancer') DECLARE @Members TABLE ( MemberID INT IDENTITY(1,1), Name VARCHAR(50), Surname VARCHAR(50), Email VARCHAR(50) ) DECLARE @memConditions TABLE ( MemberID INT, ConditionID INT ) SELECT * FROM @ImportedStagingData SELECT * FROM @Conditions SELECT * FROM @Members SELECT * FROM @memConditions /* --- This is the part that i am battling with --- DECLARE @CurrentValue INT DECLARE @numValues INT SET @numValues = (SELECT COUNT(ID) FROM @ImportedStagingData) WHILE @numValues <> 0 BEGIN INSERT INTO @Members SELECT Name, surname, email FROM @ImportedStagingData GO SET @CurrentValue = (SELECT IDENT_CURRENT('@ImportedStagingData')) INSERT INTO @memConditions (MemberID), (ConditionID) VALUES (@CurrentValue, --ConditionValue from @ImportedStagingData, all the values that have a 1)
I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
Okay, I used the SQLDataSource control to get my data from the database table. What or how do I retrieve individual data from the sqldatasource? I want to do some string comparison and manipulation before I display it to the browser. How can this be accomplish?
Background:I am using Visual Studio 2005 Standard SP1 to create an ASP.NET website that accesses an SQL 2005 database. I am using vb.net as well.I am passing an ID in a query string from one page to the next, where I retrieve it using an SQLDataSource. The data will only be one row; it is returning customer information.Problem:I need to be able to get specific fields from the SQLDataSource and populate some textboxes. Honestly, I'm not really sure where to begin. For the sake of argument, let's say that I am working with this:'My TextboxDim LastName As Textbox'My SQLDataSource (filtered by query string using the customer's ID to only get 1 customer's information at a time)SQLDataQuestion:So, how would I go about retrieving the lastname field from SQLData and inserting it into the textbox LastName?Thanks, J'Tok
Hi! I'm a novice in asp .net I've a SqlDataSource component on an Aspx page.In the associated C# file, I would like to use datas from the query stored in the SqlDataSource component.How to do this? Thanxs :)
I'm an "old" programmer but new to ASP.NET. I want to get a value from the SQL Dataset. What I would normally do in other environments is iterate through the dataset to get the value I would be intrested in, but I can't figure out how to do this without using a visual data display object like a Grid view. Typically I want to get a value from the database that I then after manipulating it, like multply by 5, use to format something on the page. thanks in advance, Thommie
I have made a SQLDataSource control with the select command: SELECT COUNT(*) AS 'Antall' FROM Utgivelse WHERE (medieID = @medieID) I want to use the "Antall" result programmatically in C# code. I try the following statement: IDataReader MyReader;
MyReader = CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty),IDataReader); but it doesnot work. Can somebody help me how to get the data from th control ? Tom
Hello, I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.
It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...
I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.
Thank
Felix Pageau fpageau@str.ca
You can test the functionnality with the following code:
create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1)) Declare @count as int set @count = (select count(IDnumber) from identCurrentBugExeample)
--Print the number of record in the table print @count
--Supposed to print 0 because there isn't any identity values that has been used print ident_current('identCurrentBugExeample') insert identCurrentBugExeample(aChar) values('a')
--Print 1 because the last identity used is 1 print ident_current('identCurrentBugExeample') insert identCurrentBugExeample(aChar) values('a')
--Print 2 because the last identity used is 2 print ident_current('identCurrentBugExeample') drop table identCurrentBugExeample
I found a strange problem with IDENT_CURRENT returning NULL value in all my SP's in which it is used.Following are the general reasons why it returns NULL :1. Its executed against the Empty table.2. Table does not Identity column.But i could check that above two point were not a prolblem for me. The problem was the database user has only db_executor permissions. If it is set to db_owner all my insert SP's work's as it should. But it fails otherwise.Can anybody throw some lights here ?
I have Server1(MSSQL7) & Server2(MSSQL 2000) are linked. For trigger purpose I need to access latest identity value from Server2.mydatabase.mytable in Server1 so I have statement as follows
Hi thereI have small problem with IDENT_CURRENT...We have a table where PK is generated automatically.What I want is to get that value and insert it to another,corresponding table.So here is what I am doing...BEGIN TRANInsert Into table() --> PK is created.SELECT IDENT_CURRENT('Table_Name') AS lastValueCOMMIT TRanIT works fine, but there is a possibility to insert another record byanother user before I get my IDENT_CURRENT.For instance:BEGIN TRANInsert Into table() --> PK is created.---->somebody else inserts another record into same table.SELECT IDENT_CURRENT('Table_Name') AS lastValue---->this is not the value that I generated....COMMIT TRan
I have a table that uses an identity column. I have a second column that will contain the current identity value or, when the original row is replaced, the identity value of the replacing row.
USE [test] GO /****** Object: Table [dbo].[test1] Script Date: 04/10/2008 14:04:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[test1]( [col1] [int] IDENTITY(1,1) NOT NULL, [col2] [int] NOT NULL, [col3] [varchar](max) NOT NULL, [ts] [timestamp] NOT NULL, CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED ( [col1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
My question is on the insert is it safe to use the following statement to insert the new identity value to the second column?
INSERT INTO test1 (col2, col3) VALUES (IDENT_CURRENT('test1'), 'test')
BOL says (or has had added ) on the subject of IDENT_CURRENT
Exceptions
Returns NULL on error or if a caller does not have permission to view the object.
In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object.
However am finding that SCOPE_IDENTITY does continue to function for a user who has not been granted meta data permisisons or directly (select) on the underlying table.
can somebody explain why they are different behaviours. I need to be able to capture the ID of a row just inserted (under the insert rights of my application role) I'm glad SCOPE_IDENTITY works but am nervous am working in an area of undefined behaviour ! after all its also a metadata-emitting, built-in function
My application role is the owner of the schema and the storedprocedure which trys to use IDENT_CURRENT but it consistently returns me a null when logged in as a user without admin priveleges
Hello, The following statement behave different between SQL2000 and SQL2005. On our database we have some insert statements like that, and as we test our DB for SQL2005, we have found this difference. Can anyone tell me why?
hi to all who is the best use among IDENT_CURRENT and @@IDENTITY and SCOPE_IDENTITY when i wnat to get last inserted id from a table. and also give the reason why because i am little bit confuse for useing these.. thanks in advance. arvind
SO when i try to load from  Master table to parent and child table i am using using expresssion likeÂ
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ONÂ A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER ANDÂ A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) Â and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.
If i run this expression to remote server  i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database. I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields. It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table. In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters. The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%') If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null> My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>. For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?Thanks in advance.
I am trying t get output for the following querry but I know am missing something. Can anyone help me out with it.
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
In the above query am getting the productnum and quanity and it looks like this
productnum quantity abc 6 ttt 3 sss 1
What am tring to do to this query is that . From another table 'product' i want all the data to be retrieved with this productnum(the table 'product' has a column called prductnum). I don't know how to write a query for this.
my query is select * from product where productnum in ( select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
here is my table: MVPTABLE columnName: Accsno JanMail JanVisit JanPhone JanComments FebMail..upto DecMail DecVisit DecPhone DecComments.
eg: table with values: Accsno JanMail JanVisit JanPhone JanComments FebMAil FebVisit A234 1 2 3 yes jim 0 2 A234 0 2 0 No Comments 1 2 As34 0 0 0 No Comments 1 2 A235 1 2 3 yes jim 0 2 A235 0 2 0 No Comments 1 2 As35 0 0 0 No Comments 1 2
am sending 2 parameter as 1> @param1= A234 2> @param2= 'JanMail,JanVisit,JanPhone,FebMail,FebVisit,FebPhone,MarMail,MarVisit,MarPhone,AprMail,AprVisit,AprPhone,MayMail,MayVisit,MayPhone,JunMail,JunVisit,JunPhone,JulyMail,JulyVisit,JulyPhone,AugMail,AugVisit,AugPhone,SepMail,SepVisit,SepPhone,OctMail,OctVisit,OctPhone,NovMail,NovVisit,NovPhone,DecMail,DecVisit,DecPhone
based on these 2 parameter i wanted to retrieve respective Comments say, if @param1 Ac234 @param2= JanMail,JanVisit,JanPhone,MarMail if the value of JanMail or FebMAil....DecMail = 1,then retieve respective comments if the value of JanVisit,FebVisit..........DecVisit=2,then retrieve respective comments if the value of JanPhone,or FebPhone,.....DecPhone=3 then retive respective comments
Hi, how can i retrieve 10 fake rows from any table? for examle for a row it will seem like that.. Id Name Value Status----- ----------- ----------- ---------------1 null null null thanks in advance..
I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code:
CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals] AFTER INSERT AS Declare @Portal int set @Portal = (select PortalId FROM inserted)
Declare @TabId Int set @Tabid = (select TabID from Tabs where Tabs.PortalID = @Portal and Tabs.TabName = 'MyTab')
Declare @ModuleId int set @ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @TabId and Modules.ModuleTitle = 'MyModule')
I am more familiar with writing SQL for Oracle than MS SQL Server. Within Oracle there is a simple command, 'Describe', which actually shows the data types, and whether or not an attribute accepts NULLS. MS SQL Server does appear to support such a command, however I am looking for a way to describe the attributes of tables nonetheless.
I am having a big problem, trying to create datebase. I have 3 tables: SUPERAVATAR, MASTERAVATAR, MEGAAVATAR. - SuperAvatars are heroes in an online role playing gaming. They have an ID, ‘superavatarID’ which contain an UNIQUE NUMBER NOT NULL PRIMARY KEY to identify them. A wisdom – ‘trickster’,’conjuror’,’magician’, etc.. A current owner… who is the user or player of the game and has that Super Avatar– we associate the link to USERID to know the person. SuperAvatars can be fathers or mothers of Mega Avatars. -MegaAvatars are the children of SuperAvatars…. They also have an ID UNIQUE NUMBER NOT NULL PRIMARY KEY to indentify them. A magic power – it can be a ‘Leader’ or ‘Sheep’… A ‘parent’ – parent is the number identifying to know to who Super Avatar belongs. e.g. SUPERAVAT WISD CURRENTOWNER FATHEROF MOTHEROF 1 Thick 3 1 2 Mentally Ch. 11 3 3 Smart 9 2 4 Genius 16 4 5 Thick 19
-We see that MEGAAVATAR 1 has a magic power as Magician and his father is ‘1’. ‘1’ identifies the SUPERAVATAR. We see SUPERAVATARID…. who has the number ‘1’? the first in the row… who has wisdom – thick and he belongs to the USER with ID number 3. -We see MEGAAVATARID… we choose the number 2…. His magic power is as WIZARD… and his father is the number 3. We see SUPERAVATARID now… we look up the SuperavatarID 3…. We can see he has a wisdom – GENIUS… who belongs to the USERID 16 and he is father of MEGAAVATAR number 2. The list can carry on and never stop.
I have this Problems: We create in this example 3 tables: Users, SuperAvatar, MegaAvatar
SQL CREATE TABLE users(userID NUMBER CONSTRAINT pk_user PRIMARY KEY,email VARCHAR2(50) NOT NULL UNIQUE,password VARCHAR2(15) NOT NULL UNIQUE,subscription CHAR(8) NOT NULL CHECK (subscription IN('ACTIVE' , 'INACTIVE' ) ) );
CREATE TABLE superavatar(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL, fatherOf NUMBER,motherOf NUMBER);
CREATE TABLE megaavatar (megaavatarID NUMBER CONSTRAINT pk_megaavatar PRIMARY KEY, magicPower VARCHAR2(12) NOT NULL CHECK (magicPower IN('TRICKSTER','CONJUROR','MAGICIAN','WIZARD','SORCERER')), parent NUMBER);
Now, the 3 tables are created….. What happen when we try to insert values to this table? In this case we insert to User Table some examples: INSERT INTO users VALUES('3','john@hotmail.com','great78','ACTIVE'); INSERT INTO users VALUES('9','chrisandsandra@gmail.com','chrisandra)','ACTIVE'); Now, we insert to SuperAvatar some examples; INSERT INTO superavatar VALUES('1','THICK','3','1',''); INSERT INTO superavatar VALUES('3','SMART','9','3',''); | | ‘9’ is the UserID that we already insert to the User table What’s the problem? We have to insert manually the data from USERID to CURRENTOWNER as we didn’t match or link CURRENTOWNER from SUPERAVATAR Table to USERID from USER Table with a SQL CODE. What happen if we have thousands of USERS that they register to this game…? We will never know to how belongs that SUPERAVATAR but if someone do it manually can spend a year.
I am trying to fix this problem …. I add in SUPERAVATAR TABLE ‘CHECK’ in currentOwner..
SQL> CREATE TABLE superavatar (superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL CHECK (currentOwner IN(SELECT userID FROM users)), fatherOf NUMBER, motherOf NUMBER); * ERROR: ORA-02251: subquery not allowed here
