Cannot insert data using Listview

Background

I am building a user entry form in ASP.net 4.0 using Visual Studio Professional 2010 and C# with SQL Server 2005 as the backend. This form was built using the wizards Microsoft provides. In other words I did not not write the code for this, but rather used the options provided to me by clicking and filling the dialogue boxes for the listview. I have not customized this in any way. The Edit, Delete, Update and Insert are enabled.

This form uses a dropdownlist to filter the records. There are two tables in this form: A Personnel table listing paramedics and a Preferences table listing their shift preferences. This form will filter for a specific paramedic and then add multiple shift preference.

The problem is that when I run the page and attempt to insert data using the Insert command I receive an error message. The error message reads:

Cannot insert the value NULL into column 'Preference_PK', table 'ColdFusion_Apps.dbo.Preferences'; column does not allow nulls. INSERT fails. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Preference_PK', table 'ColdFusion_Apps.dbo.Preferences'; column does not allow nulls. INSERT fails. The statement has been terminated.

How can I fix this ? I suspect the issue is the primary key column of my Preferences table, but if I followed all the wizards and took just the default options, why would this be happening ?

SQL code to create the database

CREATE TABLE Preferences ( Preference_PK INT NOT NULL IDENTITY(1,1) , Name_FK INT NOT NULL , Preference VARCHAR(20) ) ; INSERT INTO Preferences (Name_FK, Preference) VALUES (1,'Make a Selection') ,(2,'Make a Selection') ,(3,'Make a Selection') ,(4,'Make a Selection') ,(5,'Make a Selection') ; ALTER TABLE Preferences ADD CONSTRAINT PreferencePrimaryKey PRIMARY KEY CLUSTERED(Preference_PK) ; ALTER TABLE Preferences ADD CONSTRAINT PersonnelForeignKey FOREIGN KEY(Personnel_FK) REFERENCES Personnel(Personnel_PK) ; CREATE TABLE Personnel ( Personnel_PK INT NOT NULL IDENTITY(1,1) , Name VARCHAR(50) NULL , Title VARCHAR(20) NULL , DateHired SMALLDATETIME NULL ) ; INSERT INTO Personnel (Name, Title, DateHired) VALUES ('Abes, Benjamin','Lieutenant','March 18, 2004') ,('Acton, Traci','Paramedic','May 30, 1991') ,('Adams, Bunny','Paramedic','January 4, 2001') ,('Alcime, Gabner','EMT','April 12, 2007') ,('Angel, Craig','Paramedic','November 5, 1992') ; ALTER TABLE Personnel ADD CONSTRAINT PersonnelPrimaryKey PRIMARY KEY CLUSTERED(Personnel_PK) ;

ASP.NET 4.0 code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="Personnel_sql" DataTextField="Name" DataValueField="Personnel_PK"> </asp:DropDownList> <asp:SqlDataSource ID="Personnel_sql" runat="server" ConnectionString="<%$ ConnectionStrings:ColdFusion_AppsConnectionString %>" SelectCommand="SELECT [Name], [Personnel_PK] FROM [Personnel]"></asp:SqlDataSource> <asp:SqlDataSource ID="Preference_sql" runat="server" ConnectionString="<%$ ConnectionStrings:ColdFusion_AppsConnectionString %>" DeleteCommand="DELETE FROM [Preferences] WHERE [Preference_PK] = @Preference_PK" InsertCommand="INSERT INTO [Preferences] ([Personnel_FK], [Preference], [Preference_PK]) VALUES (@Personnel_FK, @Preference, @Preference_PK)" SelectCommand="SELECT Preference_PK, Personnel_FK, Preference FROM Preferences WHERE (Personnel_FK = @Personnel_FK)" UpdateCommand="UPDATE [Preferences] SET [Personnel_FK] = @Personnel_FK, [Preference] = @Preference WHERE [Preference_PK] = @Preference_PK"> <DeleteParameters> <asp:Parameter Name="Preference_PK" Type="Int32" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="Personnel_FK" Type="Int32" /> <asp:Parameter Name="Preference" Type="String" /> <asp:Parameter Name="Preference_PK" Type="Int32" /> </InsertParameters> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="Personnel_FK" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="Personnel_FK" Type="Int32" /> <asp:Parameter Name="Preference" Type="String" /> <asp:Parameter Name="Preference_PK" Type="Int32" /> <asp:Parameter Name="Preference_PK" /> </UpdateParameters> </asp:SqlDataSource> <asp:ListView ID="ListView1" runat="server" DataKeyNames="Preference_PK" DataSourceID="Preference_sql" InsertItemPosition="LastItem"> <AlternatingItemTemplate> <tr style=""> <td> <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" /> <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" /> </td> <td> <asp:Label ID="Preference_PKLabel" runat="server" Text='<%# Eval("Preference_PK") %>' /> </td> <td> <asp:Label ID="Personnel_FKLabel" runat="server" Text='<%# Eval("Personnel_FK") %>' /> </td> <td> <asp:Label ID="PreferenceLabel" runat="server" Text='<%# Eval("Preference") %>' /> </td> </tr> </AlternatingItemTemplate> <EditItemTemplate> <tr style=""> <td> <asp:Button ID="UpdateButton" runat="server" CommandName="Update" Text="Update" /> <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" /> </td> <td> <asp:Label ID="Preference_PKLabel1" runat="server" Text='<%# Eval("Preference_PK") %>' /> </td> <td> <asp:TextBox ID="Personnel_FKTextBox" runat="server" Text='<%# Bind("Personnel_FK") %>' /> </td> <td> <asp:TextBox ID="PreferenceTextBox" runat="server" Text='<%# Bind("Preference") %>' /> </td> </tr> </EditItemTemplate> <EmptyDataTemplate> <table runat="server" style=""> <tr> <td> No data was returned. </td> </tr> </table> </EmptyDataTemplate> <InsertItemTemplate> <tr style=""> <td> <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="Insert" /> <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Clear" /> </td> <td> <asp:TextBox ID="Preference_PKTextBox" runat="server" Text='<%# Bind("Preference_PK") %>' /> </td> <td> <asp:TextBox ID="Personnel_FKTextBox" runat="server" Text='<%# Bind("Personnel_FK") %>' /> </td> <td> <asp:TextBox ID="PreferenceTextBox" runat="server" Text='<%# Bind("Preference") %>' /> </td> </tr> </InsertItemTemplate> <ItemTemplate> <tr style=""> <td> <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" /> <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" /> </td> <td> <asp:Label ID="Preference_PKLabel" runat="server" Text='<%# Eval("Preference_PK") %>' /> </td> <td> <asp:Label ID="Personnel_FKLabel" runat="server" Text='<%# Eval("Personnel_FK") %>' /> </td> <td> <asp:Label ID="PreferenceLabel" runat="server" Text='<%# Eval("Preference") %>' /> </td> </tr> </ItemTemplate> <LayoutTemplate> <table runat="server"> <tr runat="server"> <td runat="server"> <table id="itemPlaceholderContainer" runat="server" border="0" style=""> <tr runat="server" style=""> <th runat="server"> </th> <th runat="server"> Preference_PK </th> <th runat="server"> Personnel_FK </th> <th runat="server"> Preference </th> </tr> <tr id="itemPlaceholder" runat="server"> </tr> </table> </td> </tr> <tr runat="server"> <td runat="server" style=""> </td> </tr> </table> </LayoutTemplate> <SelectedItemTemplate> <tr style=""> <td> <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" /> <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" /> </td> <td> <asp:Label ID="Preference_PKLabel" runat="server" Text='<%# Eval("Preference_PK") %>' /> </td> <td> <asp:Label ID="Personnel_FKLabel" runat="server" Text='<%# Eval("Personnel_FK") %>' /> </td> <td> <asp:Label ID="PreferenceLabel" runat="server" Text='<%# Eval("Preference") %>' /> </td> </tr> </SelectedItemTemplate> </asp:ListView> </form> </body> </html>

Question

  • How can I insert values into my database using this Listview ?
  • What am I doing wrong ?
  • Please spell this out for me explicitly. I am not a programmer, so with me, you cannot be too simplistic.

--------------Solutions-------------

Try this insert statement instead. You don't include the identity key in the insert statement.

InsertCommand="INSERT INTO [Preferences] ( [Preference], [Preference_PK]) VALUES (@Preference, @Preference_PK)"

Category:asp.net Time:2012-03-26 Views:0

Related post

  • Insert Data Into Listview And Save To SQL Server 2010-08-17

    am using C#, VS-2005 am new for listview Control in VS-2005. I know how to Insert Records into Listview by typing on Outer Textbox. But don't know how to directly insert Records on Empty ListView control as it not Focus It's Self. If any other way or

  • ListView not updating after inserting data through an Android DialogBox? 2011-09-16

    hi all Am having listview which displays list of items from sqlite db. Using DialogBox am inserting data in DB. Insertion done successfully. But if i entered data on DialogBox and click ok button, the data inserted in db but the inserted data is not

  • Insert data DBGrid to TlistItem 2009-10-12

    How do I insert data DbGrid to TlistItem with Delphi? --------------Solutions------------- Data in a TdbGrid can can only be stored in a TDataSet decendant. So I suspect what your asking is how to get the information in a TDataset into a TListView. B

  • Display dynamic data in listview, gridview 2010-11-19

    I recives a dictionary <string, string> from an API. I have to show this data on my WPF form in in grid view as Name and Value as two columns <ListView Name="LstCustomProperties" ItemsSource="{Binding CustomPropertyTable}"> <ListView.V

  • unable to insert data into sqlite database for android device 2011-12-19

    I've met an unknown error while inserting data into the database. The LogCat had been display "unable to insert data", however, the code that I've used is copied from another activity (which works). weird enough, when I placed the code in the onStart

  • Cannot insert Item to listview 2012-03-05

    Here are all my classes. MainForm = listview, CustomerFrame = textboxes When I compile my program, my MainForm appears with an empty listview, and when I press on the add button to insert an item, my CustomerFrame class appears. When writing in the t

  • What is the fastest way to insert data into an Oracle table? 2008-09-27

    I am writing a data conversion in PL/SQL that processes data and loads it into a table. According to the PL/SQL Profiler, one of the slowest parts of the conversion is the actual insert into the target table. The table has a single index. To prepare

  • MySQL inserting data only if table doesn't exist 2008-11-14

    Using strictly SQL (no PHP or anything else), is it possible to create a table and insert default data into that table only if that table doesn't exist? --------------Solutions------------- Use the CREATE TABLE ... SELECT format: create table if not

  • How would I insert data into an Access table using vb.net? 2008-12-03

    I want to insert a new row into an Access database. I'm looking at doing something like: oConnection = new Connection("connectionstring") oTable = oCennection.table("Orders") oRow = oTable.NewRow oRow.field("OrderNo")=21 oRow.field("Customer") = "ABC

  • Problem with inserting date - Oracle and C# 2009-03-30

    I have Date Var in Oracle, and I try to insert Data from my C# program sql = "insert into Table(MyDate) values (" + convert.todatetime(txt) + ")"; I get an Error, what can i do ? --------------Solutions------------- Use parameters. It's going to solv

  • Can CakePHP insert data into an model using an unrelated `$this->Model->query()` method? 2009-04-06

    In CakePHP, Is it possible to insert into another table with the other model not related to it? For example, I have a model Post .is it possible for me to use $this->Post->query('insert into tablename(col1) values (' . "$formname" . ')'); Sugge

  • Inserting data in Microsoft Dynamics C5 from C# 2009-04-28

    I need to transfer some data from our CRM system into the Microsoft Dynamics C5 accountance system. Is this possible? Writing directly in a SQL database and such hacks will be fine for me as well. --------------Solutions------------- Inserting data d

  • How to insert data into two table from linq 2 sql with stored Procedure 2009-05-15

    I using linq 2 sql with the asp.net mvc. I am having two table schedular and schdulerhistory and I am having a stored procedure which will insert data in this two table with one transaction. I want to use that stored procedure with the linq. How i ca

  • parse XML and insert data in MySQL table 2009-06-17

    I am trying to parse a xml file that i have (using javascript DOM). and then i want to insert this data in a mysql table (using php). I know how to parse xml data. But i am unable to figure out how to use php and javascript together and insert data i

  • Error inserting data using SqlBulkCopy 2009-06-21

    I'm trying to batch insert data into SQL 2008 using SqlBulkCopy. Here is my table: IF OBJECT_ID(N'statement', N'U') IS NOT NULL DROP TABLE [statement] GO CREATE TABLE [statement]( [ID] INT IDENTITY(1, 1) NOT NULL, [date] DATE NOT NULL DEFAULT GETDATE

  • How do I insert data into a Postgres table using PowerShell or VBScript? 2009-06-29

    I need to periodically query the event logs on a handful of servers and insert specific events into a Postgres table. I am having trouble figuring out how I can insert data into a table via ODBC using PowerShell and/or VBScript. I'm reasonably famili

  • Concurrent process inserting data in database 2009-07-14

    Consider following schema in postgres database. CREATE TABLE employee ( id_employee serial NOT NULL PrimarKey, tx_email_address text NOT NULL Unique, tx_passwd character varying(256) ) I have a java class which does following conn.setAutoComit(false)

  • Inserting date in sqlite db using adobe air and javascript 2009-07-23

    I am trying to insert a date value in sqlite db using air and javascript. The value gets inserted but when I try and view it, it says null. Later I found that SQLite stores date using julian format. How to convert a javascript date object to julian f

  • Error while inserting data with LINQ to SQL 2009-07-23

    When I insert data with the following code, I see the exception. What should I do? Code: Movie_List_DBDataContext Movie_list1 = new Movie_List_DBDataContext(); Actor act = new Actor(); act.Actor_Name = Acttxt.Text; Movie_list1.Actors.InsertOnSubmit(a

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.325 (s). 13 q(s)