Cannot insert value in database

albert albert

15y1.9k.NET

Hi everyone,

I have 3 tables:

[code]

CREATE TABLE [flowsuitedata].[Complaint](
[ComplaintId] [int] NOT NULL,
[OrderNumber] [int] NULL,
[InvoiceNumber] [int] NULL,
[DescriptionComplaint] [ntext] NULL,
[CustomerContactPerson] [ntext] NULL,
[OwnContactPerson] [ntext] NULL,
[ResolutionDescription] [nvarchar](50) NULL,
[RelatedSuborder] [nvarchar](50) NULL,
[Responsible] [nvarchar](50) NULL,
[StatusComplaint] [varchar](50) NULL,
[Amount] [int] NULL,
[StartDateComplaint] [datetime] NULL,
[EndDateComplaint] [datetime] NULL,
[ComplaintAssignedOwnContactPerson] [ntext] NULL,
[ResponsibleId] [int] IDENTITY(1,1) NOT NULL,
[StatusId] [int] NOT NULL,
CONSTRAINT [PK_Complaint] PRIMARY KEY CLUSTERED
[/code]

[code]

[code]
CREATE TABLE [flowsuitedata].[Status](
[StatusId] [int] IDENTITY(1,1) NOT NULL,
[Kind_status] [varchar](50) NULL,
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED
(
[StatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

[/code]

CREATE TABLE [flowsuitedata].[Responsible](
[ResponsibleId] [int] IDENTITY(1,1) NOT NULL,
[Kind_Responsible] [varchar](50) NULL,
CONSTRAINT [PK_Responsible] PRIMARY KEY CLUSTERED
(
[ResponsibleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

[/code]

But I still get the error:

"Cannot insert the value NULL into column 'StatusId', table 'XeroxTest.flowsuitedata.Complaint'; column does not allow nulls. INSERT fails."

by this query:

[INSERT INTO Complaint(InvoiceNumber, DescriptionComplaint,CustomerContactPerson,OwnContactPerson,ResolutionDescription,RelatedSuborder,Responsible,StatusComplaint,Amount,StartDateComplaint,EndDateComplaint, ComplaintAssignedOwnContactPerson)
VALUES(77, 'hoi', 'hoi', 'hoi', 'hoi', 'hoi', 'hoi','hoi',99,'10/4/2011','10/4/2011', 'hallo');]

And I have put in every table set the property: Is Identity to Yes.

THX for helping!!

21 Replies

Know the answer? Post it — somebody with the same question will find it here.

Javeed M Shaikh15y ago
Accepted answer
Perfect!
0
Prabhu Raja15y ago
Hi,

Yes man, you are correct. and i agree with the point "Depends on Application to Application and both having their own advantages and disadvantages".

------------------------------------------------------------------------------------------------------------------------
Thank You, Good Conversation.
0
Javeed M Shaikh15y ago
Hi Prabhu,

It depends on application to application and both has their own advantages and disadvantages. Speaking of that I would say if you are using multiple SQL servers to store data and need to consolidate at the end one should use GUID as that is guaranteed to be unique in time and space and you can get the new id with just a call to NEWID() also on the other hand GUIDs as key values is that they are BIG. At 16 bytes they are one of the largest datatypes in SQL Server. Indexes built on GUIDs are going to be larger and slower than indexes built on IDENTITY columns, which are usually 4 bytes.Not only that, but they're just plain hard to read if you want to share Identity values with front end for some reason (consider reading this B85E62C3-DC56-40C0-852A-49F759AC68FB). Unless you need a truly globally unique identifier, you're probably better off sticking with an IDENTITY.

Anythoughts!
0
Prabhu Raja15y ago
Hi Albert,

Your Query's are looking so good. But i want suggest one thing, Why you can not use GUIDs instead Of Identity's. Because GUID Columns having More Applications then Identity Columns. If you want to know more about GUIDs, you may try this. IDs Vs GUIDs.

Thank You
0
albert albert15y ago
oke, so like I have it now:

Tables:

- Complaint[PK: ComplaintID, FK:ResponsibleID, StatusID ]
- Responsible[PK: ResponsibleID]
- Status[PK:StatusID]

So this is correct?
0
Javeed M Shaikh15y ago
what is the difference between StatusComplaint and StatusId in Complaint table? But I think Status Table should be the master and Complaint should use the StatusID from Status table.
0
albert albert15y ago
yes, I also thought. But an Complain has an Status: Open, appende, cancelled. So status is dependent of Complain. That is why it is I think better to put FK in Status table. not vice versa.

Liker customers and orders. U put FK in Order , because a customer has an order. That is why I ask u. Or is this not correct?

This is the text:

The complaint data should go in a new table to create containing the following fields:
1. Order number
2. New order number
3. Invoice number
4. Description of complaint
5. Customer contact person
6. Own contact person
7. Resolution description
8. Related suborder
9. Who is responsible multiple choice: Customer service, Billing, Shipping, Printer, Finisher, Prepress, Transport, Sales
10. Amount
11. Status drop down: Open, Approved, Cancelled
12. Date time of complaint
13. Date time closed
14. Complaint assigned to Own contact person
0
Javeed M Shaikh15y ago
what I was thinking is table Status is the master table with Complaint Table having Status ID as FK, is this correct?
0
albert albert15y ago
But table[Status ] is dependent off table[Complaint] so it has to be the other way: in table[Status] has too be the FK of table[Complaint]. Because a Complaint has a status: Open, approved or cancelled. I am right or not?
0
Javeed M Shaikh15y ago
Status table looks like a master table and the information you are storing in that table is correct wrt StatusID and Kind_status i.e. description of the status. The StatusId will become a reference in other tables which will link to this master table for the description.

Please do not forget to mark "Accepted Answer"
0
albert albert15y ago
Hi Javeed,

I can add all the values in the complaint table.

But the values: status and Respobible are not added in the database.

But if u can see: I have made an table: status with the columns: statusId and Kind_Status. And then in the form of the Complaint the user can choose: Open, Approved and Cancelled.

I Have made the column: Kind_Status varchar(50) is this the correct datat type? or it has to be int? Maybe u can give me little advice what the best solution is.

THX.
0
Javeed M Shaikh15y ago
listbox.selectedvalue should give you the selected value and then you can insert that in your database.

General Algo:

Open Connection
Create Command
Assign Parameters (if you have stored procedure)
Execute Command (give your SQL)
Close Connection
Dispose
0
albert albert15y ago
Hi Javeed,

other question: I have by status: 3 options: Open, approved and Cancelled.

I have the listbox with the three values so the user can choose 1 of the 3 values. But how do u put the values if the user has choosed 1 of the 3 values in the SQL database?

THX
0
Javeed M Shaikh15y ago
There is nothing to do with the PK or FK. But what you are saying is correct; other option is you can write an insert trigger on Complaint table to insert value in the statusId column.

Please do not forget to mark "Accepted Answer".
0
albert albert15y ago
THX!!
0
albert albert15y ago
So I have to put the identity in the tables: Complaint(PK), Status(PK) and Responsible(PK)? but not the FK' s in the table Complaint?
0
Javeed M Shaikh15y ago
you can create only one identity column per table in SQL Server.

Please do not forget to mark "Accepted Answer".
0
albert albert15y ago
No data, I just deleted the data and reseted the data with the next quary: UPDate Complaint set ComplaintId = -1;
0
Javeed M Shaikh15y ago
you can create only one identity column per tablein SQL Server.

Please do not forget to mark "Accepted Answer".
0
albert albert15y ago
Hi Javee, oke, this is very weird. I just change the statusId of table Complaint to Identity, but it goes back to identity = NO. But I can set the column ResponsibleId to Identity = yes, but not the column: StatusId. How can that be?

The ideer is: 3 tables: PK in table complaint and then 2 FK in the table Complaint. The two FK in the table: Complaint are: StatusId en ResponsibleId.
0
Javeed M Shaikh15y ago
Hi Albert,

I do not see StatusId being defined as IDENTITY in your code for the Table Complaint, also in your INSERT statement ComplaintId and ResponsibleId is not passed and that is defined as NOT NULL in your table.

Please do not forget to mark "Accepted Answer"
0