Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
id field cannot be left empty (Read 3300 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
id field cannot be left empty
06.12.08 at 15:51:02
 
id field cannot be left empty
 
Quote:
I am using XL version 4.0.43 and it worked fine before (I'm not 100% sure which version as I changed computer), now I have following problem:
When inserting (insert multiple rows) rows into table I get error message that xxx_id field (key field) cannot be left empty. Before it worked fine as database generates values for that key field when I left it empty.

So, how can I insert values as I don't know the key-value?

 
The procedure is to completely ignore the column in the upload. So don't even include it in the list of columns to upload. Just delete the column from your spreadsheet first and then load the data in.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: id field cannot be left empty
Reply #1 - 06.12.08 at 15:52:56
 
SQL Server example using identity column
 
I have received this question a couple of times now so perhaps it is a good idea to elaborate on the issue and give you all the details of how I perform inserts with auto generated key columns.
 
For the test I am using SQL Server 2000, the table Categories in the sample Northwind database. I use ODBC to connect to Northwind.
 
If you don't have a copy of this table or database, this is the script to create it.  
 
Code:
CREATE TABLE [Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Picture] [image] NULL ,
	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 
	(
		[CategoryID]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO 


 
Please note that the CategoryId field is Not Null (mandatory) and that it is an identity column.  
 
Before I insert any data I query the table:
select * from categories
 
CategoryIDCategoryNameDescriptionPicture
1BeveragesSoft drinks, coffees, teas, beers, and ales
2CondimentsSweet and savory sauces, relishes, spreads, and seasonings
3ConfectionsDesserts, candies, and sweet breads
4Dairy ProductsCheeses
5Grains/CerealsBreads, crackers, pasta, and cereal
6Meat/PoultryPrepared meats
7ProduceDried fruit and bean curd
8SeafoodSeaweed and fish

 
Now I type two extra rows with the new data. I just enter some sample text for the Categoryname and description fields.
 
To insert the new rows I open the multiple rows insert dialog of SQL*XL.  
It asks for the tablename: Categories
Then it asks for the column names. I point to the cells where CategoryName and Description are stored.
Then it asks for the range with the values. I select the data cells.
 
After inserting the data it puts successful after the data rows.
 
To verify SQL*XL correctly inserted the rows I run the query again:
select * from categories
 
CategoryIDCategoryNameDescriptionPicture
1BeveragesSoft drinks, coffees, teas, beers, and ales
2CondimentsSweet and savory sauces, relishes, spreads, and seasonings
3ConfectionsDesserts, candies, and sweet breads
4Dairy ProductsCheeses
5Grains/CerealsBreads, crackers, pasta, and cereal
6Meat/PoultryPrepared meats
7ProduceDried fruit and bean curd
8SeafoodSeaweed and fish
10testtesting testing 123
11test2test test test

Back to top
 
« Last Edit: 06.12.08 at 16:05:05 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1