CODEWITHSUNDEEP
sql-server
CodyMan
CodyMan

Reputation: 153

cannot insert null value in id column

I'm trying to insert data into medicine + Stock tables.

But it through an error cannot insert null value into column id table medicalstore.dbo.stock

Here are my queries:

query = "Insert Into Medicine (Code,Name,CompanyID,UnitPrice,CategoryID) Values ('" & row.Cells("MedCode").Value & "','" & row.Cells("MedName").Value & "','" & row.Cells("MedCompID").Value & "','" & row.Cells("MedPrice").Value & "','" & row.Cells("MedCatID").Value & "')"
query1 =   "Insert Into Stock(MedicineID,AvailableQuantity) Values('2338','10')"

Here is my table structure.

Create table medicine
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nchar](10) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [CompanyID] [int] NOT NULL,
    [UnitPrice] [varchar](15) NOT NULL,
    [ExpiryDate] [datetime] NOT NULL,
    [CategoryID] [int] NOT NULL,

    CONSTRAINT [ID_Medicine] PRIMARY KEY CLUSTERED 
)

CREATE TABLE [dbo].[Stock]
(
    [ID] [int] NOT NULL,
    [MedicineID] [int] NOT NULL,
    [AvailableQuantity] [bigint] NOT NULL,

    CONSTRAINT [Stock_ID] PRIMARY KEY CLUSTERED 
    CONSTRAINT [FK_Stock_Medicine] FOREIGN KEY([MedicineID])
      REFERENCES [dbo].[Medicine] ([ID])
)

Here is what I get when I enter StockID by myself

Here is what I get when I enter StockID by myself

Upvotes: 0

Views: 1237

Answers (2)

iDevlop
iDevlop

Reputation: 25262

You should either insert the Stock.ID yourself or specify IDENTITY(1,1) for Stock.ID

Upvotes: 0

Dgan
Dgan

Reputation: 10285

You specified Not Null in stock Table

[ID] [int] NOT NULL

Then You have to Specify its ID while inserting and Single Quotes is not need for Integers

query1 =   "Insert Into Stock(ID,MedicineID,AvailableQuantity) Values(1,2338,10)"

or

You can Change table Definition Something Like This

CREATE TABLE [dbo].[Stock](
[ID] [int] [int] IDENTITY(1,1) NOT NULL,
[MedicineID] [int] NOT NULL,
[AvailableQuantity] [bigint] NOT NULL,
CONSTRAINT [Stock_ID] PRIMARY KEY CLUSTERED 
CONSTRAINT [FK_Stock_Medicine] FOREIGN KEY([MedicineID])
REFERENCES [dbo].[Medicine] ([ID])
)

then try like this:

 query1 =   "Insert Into Stock(MedicineID,AvailableQuantity) Values(2338,10)"

Upvotes: 2

Related Questions