Hi
I have a Jobs Table: Job_ID,User_ID,Job_Info,...
Job_ID is the Primary Key.
I want to add [Job_ID_PerUser] so I wiil get:
Job_ID Job_ID_PerUser User_ID
1 1 A
2 1 B
3 1 C
4 2 A
5 3 A
6 2 C
Thanks
I is true that SQL-Server use lower values than last integer value (in auto incremet field). Is there option that i could prevent that. So the Server would always put + 1 for the auto increment value.
If delete all the records from a table that has an incremental identity. Is there a TSQL way of reset the first number on an insert back to be 1 again without going to the table taking it off then saving it the putting it back on again?
Hello, I need some help writing a script to generate Identity keys. I cannot use the row number generator because I would like to start the identity at a package level variable. Is this possible? Thank you in advance
Iam trying to add a column to an existing table that would be an "identifier". I called it "ReadingNumber" and selected Identity "Yes" and "Identity increment" as 1. When I add it, it just gives the rows random numbers instead of by the order they were inserted into the database by.....is there a way to autonumber the columns correctly? I have a COLUMN called Date and also one called Time that have the date and Time, but the format is char. Would I have to convert the date time columns into something SQL understands, sort them ASC or DESC and then do the Identity column add? Thanks for any help
I am creating an a stored procedure which could insert new records, the identity of the primary key in my table is not incremented. i want to increment the id when adding a new record in the stored procedure. what would be the system for that?
Reset the Identity IncrementHello:I have a table with a bigint type column (field) that has an identity seedof 1 and an identity increment of 1. The column is the primary key for thetable.After I backup and clean out the database (delete all of the data in the DB)I need to have the column with the identiy seed/increment value reset to 1automatically. (start counting at 1 again). How does one do that, becauseas it is now, the DB keeps increasing the value of the column from where itleft off, regardless of the fact that I deleted all of the data in thetable.The DB is MS SQL Server 2000.Thanks and appreciate any help.Ryan Kennedy
I have created a table that generates a sequential id and a stored procedure that will return that id. The trouble is no matter what I set the Seed or Increment values to, the id will always start with #1 and increment by 1.
My table is BILLING_TIME_ID Identity field BT_GEN_ID (SEED 200, INCREMENT 1)
The sp is as follows: CREATE PROCEDURE BT_NEXT_ID AS INSERT dbo.BILLING_TIME_ID DEFAULT VALUES select count (*) from dbo.BILLING_TIME_ID GO I have double checked that Identity_Insert is set to off for this table. (does this default to off unless it is set to on?) Since there is only 1 field in the table, I don`t have any indexes set.
Hi, for some reason I want to have a unique ID with a seed and random Identity increment (I want an ascending ID's but without able to know how many objects there are).
Hi, How to Change Increment Value for existing Identity Column (MS SQL2000) ?
I know how to change the seed : DBCC CHECKIDENT (activity, RESEED,4233596)
but I need the future id generated with step 2 4233596 4233598 4233600 I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .
I've been thrown a curve ball late in the game on an application I'm developing. Without getting into the specifics of the application I store a unique employee ID number for all person records in it. This ID is provided to us by the companies we're servicing. Up until this point in time we didn't have need of an externally visable ID other than the one provided to us. Now, a need for an internally generated (by our application) unique ID has been discovered. This number needs to be a minimum 5 digits (e.x. 10001, 10002, ....). I could achieve this nicely by seeding an identity column at 10000 with an increment of 1. However, I've alredy made different settings for this and there are records in the db with the old ones.
My question is: If I initially set the seed / increment at 1/1 can I change this after the fact without causing data integrity problems. Will all subsequent insertions into this table just start at the new values?
Or, ideally I'd like to create a new column seperate from the PK Identity column already in place that serves the same function, incrementing a 5 digit number by 1 for each new record. Seems that you're only allowed one identity column per table though. Is there another way to achieve get the same result as identity?
Hi I am trying to use the identity data type ( column) I am using examples from the book and management studio in sql server 2005. I am using the identity property for the customer Id in customers table.I accidentatly executed the querry twice and I had two same record with different customer id numbers of 1 and 2 . I realised the problem and I had to delete the second record. The problem is now even if I have deteted the 2nd record with customer id 2 , when I insert a new record the identity value ( customer Id) increments with a number after the value I deleted. i.e if I deleted a second row with customer id 2 ( identity 2) when I enter a new record it enters with a customer id of 3 and whenever i add a new record it increments from there. So instead of first record have cust id 1 and second record customer id 2 etc , I get first record with cust id 1 , second record with cust id 3 , third record cust id 4 etc. How can I get rid of this wrong values of identity values whenever I delete a record and try to add a new record?
I had a strange problem today with one of the identity fields in a frequently used table. It appears that the Identity column for a table had stopped incrementing after it reached 2147483585. Since I had inherited this table, I am not sure if the identity column type has been modified from int to numeric, but the current type is Numeric (9) which is 19 precision and 0 scale value.
When resetting the seed to 1, it started working. I tried creating a temp table with numeric value and it increments well beyond billions with no problems.
Has anyone encountered this? Any best practices around defining Identity data type (ie. use int or bigint and avoide Numeric)? Thanks NS
I need help. The security principal is the correct DomainUser, but the error message says I am not authenticated. So here is my error Message: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Me.User.Identity.Name=CompanyDomainRyan; System.Security.Principal.WindowsIdentity.GetCurrent=CompanyDomainRyan; HttpContext.Current.User.Identity.Name=CompanyRyan Generated by: Dim secPrinc As System.Security.Principal.WindowsIdentity = System.Security.Principal.WindowsIdentity.GetCurrent Me.lblError.Text = ex.Message & vbCrLf & vbCrLf & "Me.User.Identity.Name=" & Me.User.Identity.Name & "; System.Security.Principal.WindowsIdentity.GetCurrent=" & secPrinc.Name & "; HttpContext.Current.User.Identity.Name=" & HttpContext.Current.User.Identity.Name Here is the Connection String: Private ConnString As String = "Server=10.144.162.111;Database=DNS;Trusted_Connection=True"
IIS has 4 websites hosted on it. The parent level allows anonymous with windows auth off, but the website level has anonymous off with widnows auth and digest. In IIS, is there a problem with having the parent level anonymous and website level windows auth?
I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30) declare @COUNT as varchar(10) declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value set @STARTINV = ?C --User will input the starting invoice number SELECT COUNT as OrderCount FROM SHIPHIST where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query STARTINV=00010 --Value entered by user
I am looking to make the primary key auto increment by 1. I have found by looking around the internet that you need to do this in the Tables -> [Table Name] -> Columns -> [Column Name], Properties window, and I see the "Identity Increment" however all of the properties there are greyed out -- I can't access them. Any ideas on how to make this work?For some background: I'm running SQL Server 2005 with Visual Studio 2005. To create this database, I right-clicked on my project, and went to 'Add SQL Database', I filled in the columns all from within visual studio.
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2), detail_print_id INT NOT NULL IDENTITY(2,2), --blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
Hi,I have an on screen control to select data from a table. Here are the parameters for the select: <asp:Parameter DefaultValue="bezlan" Name="recievername" /> <asp:Parameter DefaultValue="bezlan" Name="recieversname" />this works perfectly if I sign in as bezlan <asp:Parameter DefaultValue="User.Identity.Name" Name="recievername" /> <asp:Parameter DefaultValue="User.Identity.Name" Name="recieversname" /> this doesnt pick up any data at all if I sign in as bezlan.Why is this? Is there a special bit of code I need for parameters?Thanks,Jon
Hello everyone, In SQL Query I need to update a field to my ASP.NET app which has a variable User.Identity.Name() where it contains the Form Authentication of current logon user, SQL has the suser_name() function but this is not what I needed because it returns the user accessing the Database. UPDATE [tblData] SET [UserUpdated] = User.Identity.Name() <= this failed because SQL doesn't recordnize the User.Identity.Name() function. Is there any method I can use to achieve this? Thanks in advance.
is there a way that I can use the sqldatasource with a form view where my sqldatasource select statement is like this select * from tblUsers where vcUserName=@vcUserName <selectparameters> <asp:Parameter Name="user.identity.name" Type="String /> </selectParameters>
Hiy all, I have used the code below to capture the current logged in user into my LoginID Filed in my SQL DB. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.InsertParameters("LoginID").DefaultValue = Page.User.Identity.Name End Sub Now that I have this in my DB I want to create a Gridview that will filter by this LoginID. So I want to filter all records where LoginID = the currenlty logged in user ie: filter using Page.User.Identity.Name So in my SQL code I have WHERE loginID = @Currentuser. VWD now wants me to define a parameter for this Varaibale @Currentuser... So I want to set this to Page.User.Identity.Name. My Problem is that in the datasource define parameter area I'm not sure what to define this as. ie: is Page.User.Identity.Name a cookie , control , Query String , session ? can someone point me in the right direction to what syntax to use? Ray..
I use forms authentication with my Report manager. The problem is that I can't display the same information to all the users into the report. For example, in my report, i have one dataset that give me a list of all the store of a compagny. And i have to display only the store onto the user is working. If the user is a boss, he can see the information of all the stores. So i have to know in my report what is the login he uses in the form authetication and i don't know how to do.
Hi, any suggestions on why my label doesn't show the current user.identity.name when used in a detailsview (header-field or insert-field), when it works just fine placed directly on my page ? Please help ! (I want to bind myLabelX.Text to a db-table, but it contains no value..) codebehind : 1 using System; 2 using System.Data; 3 using System.Configuration; 4 using System.Collections; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.WebControls; 9 using System.Web.UI.WebControls.WebParts; 10 using System.Web.UI.HtmlControls; 11 12 13 public partial class Test_2 : System.Web.UI.Page 14 { 15 16 protected void Page_Load(object sender, EventArgs e) 17 { 18 if ((!IsPostBack)) myLabelX.Text = User.Identity.Name; 19 } 20 21 }
Hi,How do I get hold of the HttpContext.Current.User.Identity.Name from within an sqldatasource? I want to create a WHERE clause that says WHERE UserName = @UserName and the @UserName parameter be filled with the current user name. I would be able to do this in code, but I don't know how to get at this information from within a datasource. At a guess I tried to use the Cookie option from the drop down list, and use the name of the FormsAuthentication Cookie, but this didn't work. Is there a way I can do this? If so how? I don't want to resort to using a hidden field or anything like that if I can help it. Many thanks, Steve
Hello everyone, I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches. So far i have tried: SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' " with no success. Any help is appreciated
I am having issues getting this to work. I have the user login to a page to put in a request for vacation. When they login, I have a label that isn't visible that is equal to their User.Identity.Name. I select the user from the employee table where the username = the label User Identity Name and pull in the emp_id which is the primary key that identifies the user. I need to insert the request into the request table with the emp_id from the select statement, without showing the em_id on the screen. I tried using a hidden field and assigning the emp_id as the value, but it isn't working. Not sure if this is the best way to do this. Really new to ASP.NET 2.0 so I really appreciate any help. Thank you!
I am new to MS SQL SERVER 2005. My web application using SQL Server 2005 as back end database to store all the information. I am using connection pooling to get connection form db. But my fron end user( user who logged in into webapplication) lost his identity b'coz for database user is application server. Now I wanna to track front end user in db.
In oracle I can do it by using CLIENT_IDENTIFIER. But in sql server I don't know how to do it.
in oracle i can do it as below public void setIdentity(Connection conn, String identity) { PreparedStatement ps; try { ps = conn.prepareCall("begin dbms_session.set_identifier(?); end;"); ps.setString(1, identity); ps.execute(); ps.close(); } catch (SQLException e) { // Handle the exception } } <!--[if !supportLineBreakNewLine]--> once the transaction is complete, the application should reset the identity as follows:
public void clearIdentity(Connection conn) { PreparedStatement ps; try { ps = conn.prepareCall("begin dbms_session.clear_identifier(); end;"); ps.execute(); ps.close(); } catch (SQLException e) { // Handle the exception } } <!--[if !supportLineBreakNewLine]--> Please guide me for same.