Cannot insert value in database

Question

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(11) NOT NULL [StatusId] [int] NOT NULL CONSTRAINT [PK_Complaint] PRIMARY KEY CLUSTERED [/code] [code] [code] CREATE TABLE [flowsuitedata].[Status]( [StatusId] [int] IDENTITY(11) 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(11) 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 DescriptionComplaintCustomerContactPersonOwnContactPersonResolutionDescriptionRelatedSuborderResponsibleStatusComplaintAmountStartDateComplaintEndDateComplaint 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!!

Javeed M Shaikh · Answer

Perfect!

Prabhu Raja · Answer

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.

Javeed M Shaikh · Answer

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!

Prabhu Raja · Answer

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

albert albert · Answer

oke so like I have it now: Tables: - Complaint[PK: ComplaintID FK:ResponsibleID StatusID ] - Responsible[PK: ResponsibleID] - Status[PK:StatusID] So this is correct?

Javeed M Shaikh · Answer

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.

albert albert · Answer

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

Javeed M Shaikh · Answer

what I was thinking is table Status is the master table with Complaint Table having Status ID as FK is this correct?

albert albert · Answer

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?

Javeed M Shaikh · Answer

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

albert albert · Answer

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.

Javeed M Shaikh · Answer

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

albert albert · Answer

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

Javeed M Shaikh · Answer

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.

albert albert · Answer

THX!!

albert albert · Answer

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?

Javeed M Shaikh · Answer

you can create only one identity column per table in SQL Server. Please do not forget to mark Accepted Answer.

albert albert · Answer

No data I just deleted the data and reseted the data with the next quary: UPDate Complaint set ComplaintId = -1;

Javeed M Shaikh · Answer

you can create only one identity column per tablein SQL Server. Please do not forget to mark Accepted Answer.

albert albert · Answer

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.

Javeed M Shaikh · Answer

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