asp_databases thread: Inserting Into identity field
Message #1 by awin@a... on Thu, 12 Oct 2000 16:30:44 +0100
|
|
Hi,
My SQL table has identity field called EmpID. So, when I insert, I
left out the EmpID like this.
INSERT INTO P1Payroll (SurveyID, empName, JobCode,GAP, PFT,
hrlywage,tphryr, tphryrVac, benDlr, benPct)VALUES(\'P1CA0100\',\'ann
win\',\'01\',40,99,25,49,90,90099,99)
It work fine in my development server. But When I uploaded to the
server that I rent for production, I got the following error message.
Error: -2147217900
description: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
the value NULL into column \'EmpID\', table
\'neilscos.neilscos.p1Payroll\'; column does not allow nulls. INSERT
fails.
Tech support says I cannot use idendity fields to produce autonumbers
and I need to write the code to produce auto numbers. It does not sound
right. May be he is right. If he is correct, how can I write the code?
Thanks in advance.
-ann
Message #2 by Catherine <catherine.s@t...> on Thu, 12 Oct 2000 11:11:51 -0500
|
|
Check the table structure in your production server. The EmpId should be
identity field. Why do you have to write the code if its already an
existing feature of SQL Server. Tell your tech support that it is proper
and easy to make the field an identity key rather than write the code for
yourself. Ask also your DBA to change the structure.
Hope this helps.
-----Original Message-----
From: awin@a... [SMTP:awin@a...]
Sent: Thursday, October 12, 2000 9:31 AM
To: ASP Databases
Subject: [asp_databases] Inserting Into identity field
Hi,
My SQL table has identity field called EmpID. So, when I insert, I
left out the EmpID like this.
INSERT INTO P1Payroll (SurveyID, empName, JobCode,GAP, PFT,
hrlywage,tphryr, tphryrVac, benDlr, benPct)VALUES(\'P1CA0100\',\'ann
win\',\'01\',40,99,25,49,90,90099,99)
It work fine in my development server. But When I uploaded to the
server that I rent for production, I got the following error message.
Error: -2147217900
description: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
the value NULL into column \'EmpID\', table
\'neilscos.neilscos.p1Payroll\'; column does not allow nulls. INSERT
fails.
Tech support says I cannot use idendity fields to produce autonumbers
and I need to write the code to produce auto numbers. It does not sound
right. May be he is right. If he is correct, how can I write the code?
Thanks in advance.
-ann
---
NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS? Is FREE okay?
Visit EarthWeb for the latest in IT Management, Software Development,
Web Development, Networking & Communications, and Hardware & Systems.
Click on http://www.earthweb.com for FREE articles, tutorials,
and discussions from the experts.
Message #3 by "Jez" <ray@j...> on Thu, 12 Oct 2000 10:57:55 -0500
|
|
The fact that you "can't" use identity fields to generate autonumbers is
obviously a limitation specific to your server provider.. why they are doing
this who knows... but to get around the issue you could create a stored
procedure that gets the max(EmpID) from the table and adds 1 to it and then
inserts the new number as the next id field. You may have to load one row
in manually in the begining though cause max() will return null.
-----Original Message-----
From: awin@a... [mailto:awin@a...]
Sent: Thursday, October 12, 2000 10:31 AM
To: ASP Databases
Subject: [asp_databases] Inserting Into identity field
Hi,
My SQL table has identity field called EmpID. So, when I insert, I
left out the EmpID like this.
INSERT INTO P1Payroll (SurveyID, empName, JobCode,GAP, PFT,
hrlywage,tphryr, tphryrVac, benDlr, benPct)VALUES(\'P1CA0100\',\'ann
win\',\'01\',40,99,25,49,90,90099,99)
It work fine in my development server. But When I uploaded to the
server that I rent for production, I got the following error message.
Error: -2147217900
description: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
the value NULL into column \'EmpID\', table
\'neilscos.neilscos.p1Payroll\'; column does not allow nulls. INSERT
fails.
Tech support says I cannot use idendity fields to produce autonumbers
and I need to write the code to produce auto numbers. It does not sound
right. May be he is right. If he is correct, how can I write the code?
Thanks in advance.
-ann
Message #4 by "Dallas Martin" <dmartin@z...> on Thu, 12 Oct 2000 19:37:54 -0400
|
|
It seems that the EmpID field on the production server is not flagged as an
identity field. If it was then you wouldn't be getting this error. Identity
fields
by definition cannot be null. On your production server, try and create a
table with an identity field and "ALLOW NULLS" set to true. SQL Server will
complain.
By the way, if you don't have production data yet, you can simply
use the script generator on your development server to script the
creation of the "Employee" table and email that script to the ISP.
Hopefully, the ISP knows how to run the script. I've used this technique
to create an empty copy of a database and then move it to a production
server.
What you have on the server is EmpID created as non-identity integer field
not allowing NULLS.
Dallas Martin
----- Original Message -----
From: <awin@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, October 12, 2000 11:30 AM
Subject: [asp_databases] Inserting Into identity field
> Hi,
> My SQL table has identity field called EmpID. So, when I insert, I
> left out the EmpID like this.
> INSERT INTO P1Payroll (SurveyID, empName, JobCode,GAP, PFT,
> hrlywage,tphryr, tphryrVac, benDlr, benPct)VALUES(\'P1CA0100\',\'ann
> win\',\'01\',40,99,25,49,90,90099,99)
>
> It work fine in my development server. But When I uploaded to the
> server that I rent for production, I got the following error message.
>
> Error: -2147217900
> description: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
> the value NULL into column \'EmpID\', table
> \'neilscos.neilscos.p1Payroll\'; column does not allow nulls. INSERT
> fails.
>
> Tech support says I cannot use idendity fields to produce autonumbers
> and I need to write the code to produce auto numbers. It does not sound
> right. May be he is right. If he is correct, how can I write the code?
> Thanks in advance.
> -ann
>
Message #5 by "Ken Schaefer" <ken@a...> on Fri, 13 Oct 2000 11:12:14 +1000
|
|
What type of database do you have on the production server?
I've never heard of any modern RDBMS that can't auto-generate primary
keys...
Cheers
Ken
----- Original Message -----
From: <awin@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, October 13, 2000 1:30 AM
Subject: [asp_databases] Inserting Into identity field
> Hi,
> My SQL table has identity field called EmpID. So, when I insert, I
> left out the EmpID like this.
> INSERT INTO P1Payroll (SurveyID, empName, JobCode,GAP, PFT,
> hrlywage,tphryr, tphryrVac, benDlr, benPct)VALUES(\'P1CA0100\',\'ann
> win\',\'01\',40,99,25,49,90,90099,99)
>
> It work fine in my development server. But When I uploaded to the
> server that I rent for production, I got the following error message.
>
> Error: -2147217900
> description: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
> the value NULL into column \'EmpID\', table
> \'neilscos.neilscos.p1Payroll\'; column does not allow nulls. INSERT
> fails.
>
> Tech support says I cannot use idendity fields to produce autonumbers
> and I need to write the code to produce auto numbers. It does not sound
> right. May be he is right. If he is correct, how can I write the code?
> Thanks in advance.
> -ann
|
View Cart
