I have a new clustered database and I've been moving data into the tables from our old database. When I import the data, everything works fine, but when I manually insert a record the Identity column does not follow the next identity number. So, for example, I import 100 records into the table and the Identity column shows 1-100, but i...
An Orders table has a CustomerId column and an OrderId column. For certain reasons it's important that an order's id is no longer than 2-bytes. There will be several million orders in total, which makes 2-bytes not enough for a global order id. A customer will have no more than several thousand orders making 2-bytes enough. The obviou...
Within our unit tests we use plain ADO.NET (DataTable, DataAdapter) for preparing the database resp. checking the results, while the tested components themselves run under NHibernate 2.1. .NET version is 3.5, SqlServer version is 2005. The database tables have identity columns as primary keys. Some tables apply instead-of-insert/update ...
Hey guys i am have an sql table which is basically a statement. Now lest say the records i have in my table have a date and an identity column which is autonumbered and defines the order which the transactions are displayed in the front end to the client. The issue is during an insert some of the data have gone missing and some transact...
hey all. I have a table in my DB that has about a thousand records in it. I would like to reset the identity column so that all of the ID's are sequential again. I was looking at this but I'm ASSuming that it only works on an empty table Current Table ID | Name 1 Joe 2 Phil 5 Jan 88 Rob ...
I'm playing around with building a new web application using DB4O - piles of fun and some really interesting stuff learned. The one thing I'm struggling with is DB4O's current lack of support for stateless applications (i.e. web apps, mostly) and the need for automatically-generated IDs. There are a number of creative and interesting a...
In other words, is the following "cursoring" approach guaranteed to work: retrieve rows from DB save the largest ID from the returned records for later, e.g. in LastMax later, "SELECT * FROM MyTable WHERE Id > {0}", LastMax In order for that to work, I have to be sure that every row I didn't get in step 1 has an Id greater than Last...
I have a stored procedure that inserts into a table (where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value). We then use SCOPEIDENTITY() to get the value inserted, then there is some logic to generate the primary key field value based on this value, whi...
If you have the select statement below where the PK is the primary key: select distinct dbo.DateAsInt( dateEntered) * 100 as PK, recordDescription as Data from MyTable and the output is something like this (the first numbers are spaced for clarity): PK Data 2010 01 01 00 New Years Day 2010 0...
I'm sure I'm missing the obvious answer here, but could use a hand. I'm new to SubSonic and using version 3. I've got myself to the point of being able to query and insert, but I'm stuck with how I would get the value of the identity column back after my insert. I saw another post that mentioned Linq Templates. I'm not using those (a...
We're using NHibernate.Mapping.Attributes to do our mappings. In order to get NHibernate to populate a data object automatically, it appears that you need to allow for an identity column. Okay, no problem, I added a (fake) PK column to my stored procedure results (it's a report query, so the identifier doesn't need to mean anything as l...
Hi.. I have 3 tables similar to the sctructure below CREATE TABLE [dbo].[EmpBasic]( [EmpID] [int] IDENTITY(1,1) NOT NULL Primary Key, [Name] [varchar](50), [Address] [varchar](50) ) CREATE TABLE [dbo].[EmpProject]( [EmpID] [int] NOT NULL primary key, // referencing column with EmpBasic ...
I'm building an ASP.NET MVC site that uses LINQ to SQL to connect to SQL Server, where I have a table that has an IDENTITY bigint primary key column that represents an ID. In one of my code methods, I need to create an object of that table to get its ID, which I will place into another object based on another table (FK-to-PK relationshi...
Is it possible to reseed an auto increment column in a SQLite database, and if so, how is this done? ie. The equivalent of DBCC CHECKIDENT ('MyTable', RESEED, 1) in SQL Server. ...
Hello, thats my table: -- Original table schema CREATE TABLE [SchoolYear] ( [Start] datetime NOT NULL, [End] datetime NOT NULL, [Id] integer PRIMARY KEY ON CONFLICT ABORT AUTOINCREMENT NOT NULL ); my Entity in the EF designer has StoredGeneratedPattern set to Identity OR Compute and datatype is int64. Everytime I insert a...
hi I have put a DataGridView in my program such that this DataGridView is corresponding to a dataset of one table and this table has a auto-incremented identity column(also,this column is set to be primary key). This identity column is not visible in DataGridView and when a user fills other cells and clicks "save",NoNullAllowedExceptio...
I'm trying to write a stored procedure to copy a subset of data from one set of tables to an identical set of tables in a different database. The "source" database needs to be a parameter to the stored procedure. I've struggled with this for two days now, and I thought I had a good solution: Validate that the schemas are the same. Cr...
I have marked a column as Identity in my table create table Identitytest( number int identity(1,001) not null, value varchar(500) ) I need the identity column to be incremented as 001,002,003, etc. The database shows that it is inserting as 1,2,3, etc. How can this be done? ...
When adding an item in my database, I need it to auto-determine the value for the field DisplayOrder. Identity (auto-increment) would be an ideal solution, but I need to be able to programmatically change (UPDATE) the values of the DisplayOrder column, and Identity doesn't seem to allow that. For the moment, I use this code: CREATE PROC...
i am trying to insert a row to a table with an identity column and three "normal" columns using entity framework in wpf. however i got this error message: Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF here is my code snippet, trying to add to the user table who has name, surname, a...