Insert to SQL server table is failing in app

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert to SQL server table is failing in app

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-20 : 00:06:31

The following code http://pastebin.com/LWF4PMSp

has an Insert statement at the end, and it is failing with this error message: Cannot insert the value NULL into column 'customerDocumentTypeId', table 'SpendAnalyzer.dbo.CustomerDocumentTypes'; column does not allow nulls. INSERT fails. The statement has been terminated.
this is a small table that holds document types in our system that a customer can access from the document sever. In the app the user can click on the document number and it will go to the document server and get all the doc types for that number based on the values in this table. this table has 3 columns: customerdocumenttype,customerid and documenttypeid. Allow nulls are allowed on customerid and documenttypeid but not on customerdocumenttypeid.
However when i sought to allow nulls on customerdocumenttypeid this is rejected as this column, is 'part of the primary key'

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-20 : 11:13:50

A primary key cannot be null. You need to track down why the application is trying to pass a null value for a column that cannot be nullable.

p.s. Please post the code here, not some other strange place that may or may-not be a malware site.

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-20 : 21:51:05

OK sorry about the posting. I have resolved the issue thanks to your help.

quote:
Originally posted by GilaMonster

A primary key cannot be null. You need to track down why the application is trying to pass a null value for a column that cannot be nullable.

p.s. Please post the code here, not some other strange place that may or may-not be a malware site.

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-21 : 10:54:00

code is here: the insert and select is at the end:
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="CustomerDocuments.aspx.cs" Inherits="CustomerDocuments" StylesheetTheme="Theme" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<table>
<tr>
<td style="width:20%"></td>
<td style="width:80%"><asp:Label ID="lblErr" runat="server" ForeColor="Red"></asp:Label></td>
</tr>
<tr>
<td colspan="2" style="background-color:rgb(255, 233, 126)" >
<asp:Label ID="Label2" runat="server" Text="New Document Types"
style="font-weight: 700"></asp:Label>
</td>
</tr>
<tr>
<td><asp:Label ID="Label3" runat="server" Text="Document Type"></asp:Label></td>
<td>
<asp:TextBox ID="txtDocType" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td><asp:Button ID="btnSave0" runat="server" onclick="btnSave0_Click"
Text="Save Document Type" />
</td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="2" style="background-color:rgb(255, 233, 126)" >
<asp:Label ID="Label1" runat="server" Text="Customer Associated Document Types"
style="font-weight: 700"></asp:Label>
</td>
</tr>

<tr>
<td><asp:Label ID="Label4" runat="server" Text="Customers"></asp:Label></td>
<td><asp:DropDownList ID="ddlCustomers" runat="server" BackColor='AliceBlue' Width=218px
DataSourceID="Customers" DataTextField="CustomerName"
DataValueField="CustomerId" AutoPostBack="True">
</asp:DropDownList></td>
</tr>
<tr>
<td><asp:Label ID="Label5" runat="server" Text="Customer Type"></asp:Label></td>
<td>
<asp:DropDownList ID="ddlDocumentTypes" runat="server"
DataSourceID="DocumentTypes" DataTextField="DocumentType"
DataValueField="DocumentTypeId" Width="209px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td><asp:Button ID="btnSave" runat="server" onclick="btnSave_Click" Text="Save" />
</td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" DataSourceID="CustomerDocumentTypes"
EnableModelValidation="True" SkinID="GridListView"
EmptyDataText="Customer does not have any document types associated"
onrowcommand="GridView1_RowCommand"
onrowdatabound="GridView1_RowDataBound" DataKeyNames="DocumentTypeId">
<Columns>
<asp:BoundField DataField="DocumentType"
HeaderText="Customer Document Types"
SortExpression="DocumentType" />
<asp:ButtonField CommandName="Delete" Text="Delete" />
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td> </td>
<td>

<asp:SqlDataSource ID="Customers" runat="server"
ConnectionString="<%$ ConnectionStrings:SecurityDB_20101025ConnectionString %>"

SelectCommand="SELECT * FROM [Customers] WHERE ([CustomerId] = ISNULL(@CustomerId, '') OR @IsSuperAdmin = 1 )
order by customername
"
>
<SelectParameters>
<asp:SessionParameter Name="CustomerId" SessionField="CustomerID"
/>
<asp:SessionParameter Name="IsSuperAdmin" SessionField="IsSuperAdmin" Type="Boolean"
/>

</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="DocumentTypes" runat="server"
ConnectionString="<%$ ConnectionStrings:SecurityDB_20101025ConnectionString %>"

SelectCommand="SELECT * FROM [DocumentTypes]
WHERE DocumentTypeId NOT IN ( Select DocumentTypeId from CustomerDocumentTypes WHERE CustomerId = @CustomerId)" InsertCommand="INSERT INTO [DocumentTypes]
(DocumentType)
VALUES
(@DocumentType)">
<InsertParameters>
<asp:ControlParameter ControlID="txtDocType" Name="DocumentType" PropertyName="Text" />
</InsertParameters>
<SelectParameters>
<asp:ControlParameter ControlID="ddlCustomers" Name="CustomerId"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="CustomerDocumentTypes" runat="server"
ConnectionString="<%$ ConnectionStrings:SecurityDB_20101025ConnectionString %>"
SelectCommand="SELECT D.DocumentType, D.DocumentTypeId FROM [CustomerDocumentTypes] C
INNER JOIN DocumentTypes D
ON C.DocumentTypeID = D.DocumentTypeID
WHERE ([CustomerId] = @CustomerId)" InsertCommand="INSERT INTO CustomerDocumentTypes
(CustomerID, DocumentTypeId)
VALUES
(@CustomerId, @DocumentTypeId)"

Deletecommand="DELETE from CustomerDocumentTypes WHERE CustomerID = @CustomerID and DocumentTypeId=@DocumentTypeId"
>

<SelectParameters>
<asp:ControlParameter ControlID="ddlCustomers" Name="CustomerId"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter ControlID="ddlCustomers" Name="CustomerId"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="ddlDocumentTypes" Name="DocumentTypeId"
PropertyName="SelectedValue" Type="Int32" />
</InsertParameters>
<DeleteParameters>
<asp:ControlParameter ControlID="ddlCustomers" Name="CustomerId"
PropertyName="SelectedValue" Type="Int32" />
<asp:Parameter Name="DocumentTypeId"
Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource>
<br />
</td>
</tr>
</table>
</asp:Content>

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-21 : 13:40:34

You said you had resolved the issue, is there still a problem?

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-21 : 15:19:13

No it has been resolved.

quote:
Originally posted by GilaMonster

You said you had resolved the issue, is there still a problem?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources