Inserting data using a dropdownlist

johan_st · January 20th, 2007, 07:45 AM

Hi,

i've been browsing through an extensive array of topics these last few hours but i can't seem to find the solution for my problem.

I've build a little app in which our projects and relating data should be organized (using visual Web Developer and Sql Server Express). I used scripts to import data into the DB and i am able to view that data. By example; I have a Projects-page where i can view, using a dropdownlist, projects from one customer and then drill down to see the hours worked for that project or the tasks that need to be done. So far so good.

Now i've build a page to add new projects to the database. I included 3 dropdownlists with which i can select the type, category and client for the project. When i enter the remaining data and press "insert" i get an error:

Cannot insert the value NULL into column 'CLIENT', table 'TrAction.dbo.PROJECT'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Since the designated field is NOT-NULL a value is required. Apparently the SEQ for the Client doesn't get loaded into the new record, allthough i think it should do so since it is the value-field for my dropdownlist. Am i missing something in the InsertItemTemplate?

As many of you will have guessed by now, I'm rather new to ASP and .Net :-D

Greetz, and many thanks for now.

Johan

P.S. Here's the full code for the page:

[code]

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="AddProject.aspx.vb" Inherits="AddProject" %>

<!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>Add Project</title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <asp:FormView ID="FormView1" runat="server" DataKeyNames="SEQ" DataSourceID="SqlDataSource_AddProject" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">
 <InsertItemTemplate>
 PROJECT_TYPE:
 <asp:DropDownList ID="InsertCategoryTypeDropDownList"
 SelectedValue='<%# Bind("SEQ") %>'
 DataSourceID="SqlDataSource_GetProjectType"
 DataTextField="Title"
 DataValueField="SEQ"
 RunAt="Server" /> 
 PROJECT_CATEGORY:
 <asp:DropDownList ID="InsertProjectCategoryDropDownList"
 SelectedValue='<%# Bind("SEQ") %>'
 DataSourceID="SqlDataSource_GetProjectCategory"
 DataTextField="Title"
 DataValueField="SEQ"
 RunAt="Server" /> 
 CLIENT:
 <asp:DropDownList ID="InsertClientDropDownList"
 SelectedValue='<%# Bind("SEQ") %>'
 DataSourceID="SqlDataSource_GetClient"
 DataTextField="Name"
 DataValueField="SEQ"
 RunAt="Server" /> 
 TITLE:
 <asp:TextBox ID="TITLETextBox" runat="server" Text='<%# Bind("TITLE") %>'>
 </asp:TextBox> 
 DESCRIPTION:
 <asp:TextBox ID="DESCRIPTIONTextBox" runat="server" Text='<%# Bind("DESCRIPTION") %>'>
 </asp:TextBox> 
 LOC_TOWN:
 <asp:TextBox ID="LOC_TOWNTextBox" runat="server" Text='<%# Bind("LOC_TOWN") %>'>
 </asp:TextBox> 
 LOC_STREET:
 <asp:TextBox ID="LOC_STREETTextBox" runat="server" Text='<%# Bind("LOC_STREET") %>'>
 </asp:TextBox> 
 LOC_NUMBER:
 <asp:TextBox ID="LOC_NUMBERTextBox" runat="server" Text='<%# Bind("LOC_NUMBER") %>'>
 </asp:TextBox> 
 LOC_CADASTER:
 <asp:TextBox ID="LOC_CADASTERTextBox" runat="server" Text='<%# Bind("LOC_CADASTER") %>'>
 </asp:TextBox> 
 ACTIVE:
 <asp:CheckBox ID="ACTIVECheckBox" runat="server" Checked='<%# Bind("ACTIVE") %>' /> 
 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
 Text="Insert">
 </asp:LinkButton>
 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
 Text="Cancel">
 </asp:LinkButton>
 </InsertItemTemplate>
 <ItemTemplate>
 CODE:
 <asp:Label ID="CODELabel" runat="server" Text='<%# Bind("CODE") %>'></asp:Label> 
 PROJECT_TYPE:
 <asp:Label ID="PROJECT_TYPELabel" runat="server" Text='<%# Bind("PROJECT_TYPE") %>'>
 </asp:Label> 
 PROJECT_CATEGORY:
 <asp:Label ID="PROJECT_CATEGORYLabel" runat="server" Text='<%# Bind("PROJECT_CATEGORY") %>'>
 </asp:Label> 
 CLIENT:
 <asp:Label ID="CLIENTLabel" runat="server" Text='<%# Bind("CLIENT") %>'></asp:Label> 
 TITLE:
 <asp:Label ID="TITLELabel" runat="server" Text='<%# Bind("TITLE") %>'></asp:Label> 
 DESCRIPTION:
 <asp:Label ID="DESCRIPTIONLabel" runat="server" Text='<%# Bind("DESCRIPTION") %>'>
 </asp:Label> 
 LOC_TOWN:
 <asp:Label ID="LOC_TOWNLabel" runat="server" Text='<%# Bind("LOC_TOWN") %>'></asp:Label> 
 LOC_STREET:
 <asp:Label ID="LOC_STREETLabel" runat="server" Text='<%# Bind("LOC_STREET") %>'>
 </asp:Label> 
 LOC_NUMBER:
 <asp:Label ID="LOC_NUMBERLabel" runat="server" Text='<%# Bind("LOC_NUMBER") %>'>
 </asp:Label> 
 LOC_CADASTER:
 <asp:Label ID="LOC_CADASTERLabel" runat="server" Text='<%# Bind("LOC_CADASTER") %>'>
 </asp:Label> 
 ACTIVE:
 <asp:CheckBox ID="ACTIVECheckBox" runat="server" Checked='<%# Bind("ACTIVE") %>'
 Enabled="false" /> 
 <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
 Text="New">
 </asp:LinkButton>
 </ItemTemplate>
 <FooterStyle BackColor="#CCCC99" />
 <EditRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
 <RowStyle BackColor="#F7F7DE" />
 <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
 <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
 </asp:FormView>
 <asp:SqlDataSource ID="SqlDataSource_AddProject" runat="server" ConnectionString="<%$ ConnectionStrings:TrActionConnectionString %>"
 DeleteCommand="DELETE FROM [PROJECT] WHERE [SEQ] = @SEQ" InsertCommand="DECLARE @NewDat datetime#13;#10;DECLARE @NewUser int#13;#10;DECLARE @NewCode numeric(4)#13;#10;#13;#10;SET @NewDat = (SELECT getdate())#13;#10;SET @NewUser = (SELECT SEQ FROM TEAM WHERE NAME = 'Admin')#13;#10;SET @NewCode = (SELECT max(code)+1 from project)#13;#10;#13;#10;INSERT INTO [PROJECT] ([CREDAT], [CREUSR], [CODE], [PROJECT_TYPE], [PROJECT_CATEGORY], [CLIENT], [TITLE], [DESCRIPTION], [LOC_TOWN], [LOC_STREET], [LOC_NUMBER], [LOC_CADASTER], [ACTIVE]) VALUES (@NewDat, @NewUser, @NewCode, @PROJECT_TYPE, @PROJECT_CATEGORY, @CLIENT, @TITLE, @DESCRIPTION, @LOC_TOWN, @LOC_STREET, @LOC_NUMBER, @LOC_CADASTER, @ACTIVE)"
 SelectCommand="SELECT * FROM [PROJECT] ORDER BY [CODE] DESC" UpdateCommand="UPDATE [PROJECT] SET [CREDAT] = @CREDAT, [CREUSR] = @CREUSR, [UPDDAT] = @UPDDAT, [UPDUSR] = @UPDUSR, [CODE] = @CODE, [PROJECT_TYPE] = @PROJECT_TYPE, [PROJECT_CATEGORY] = @PROJECT_CATEGORY, [CLIENT] = @CLIENT, [TITLE] = @TITLE, [DESCRIPTION] = @DESCRIPTION, [LOC_TOWN] = @LOC_TOWN, [LOC_STREET] = @LOC_STREET, [LOC_NUMBER] = @LOC_NUMBER, [LOC_CADASTER] = @LOC_CADASTER, [ACTIVE] = @ACTIVE WHERE [SEQ] = @SEQ">
 <DeleteParameters>
 <asp:Parameter Name="SEQ" Type="Int32" />
 </DeleteParameters>
 <UpdateParameters>
 <asp:Parameter Name="CREDAT" Type="DateTime" />
 <asp:Parameter Name="CREUSR" Type="String" />
 <asp:Parameter Name="UPDDAT" Type="DateTime" />
 <asp:Parameter Name="UPDUSR" Type="String" />
 <asp:Parameter Name="CODE" Type="Decimal" />
 <asp:Parameter Name="PROJECT_TYPE" Type="Int32" ConvertEmptyStringToNull="false" />
 <asp:Parameter Name="PROJECT_CATEGORY" Type="Int32" ConvertEmptyStringToNull="false" />
 <asp:Parameter Name="CLIENT" Type="Int32" ConvertEmptyStringToNull="false" />
 <asp:Parameter Name="TITLE" Type="String" />
 <asp:Parameter Name="DESCRIPTION" Type="String" />
 <asp:Parameter Name="LOC_TOWN" Type="String" />
 <asp:Parameter Name="LOC_STREET" Type="String" />
 <asp:Parameter Name="LOC_NUMBER" Type="String" />
 <asp:Parameter Name="LOC_CADASTER" Type="String" />
 <asp:Parameter Name="ACTIVE" Type="Boolean" />
 <asp:Parameter Name="SEQ" Type="Int32" />
 </UpdateParameters>
 <InsertParameters>
 <asp:Parameter Name="CREDAT" Type="DateTime" />
 <asp:Parameter Name="CREUSR" Type="String" />
 <asp:Parameter Name="UPDDAT" Type="DateTime" />
 <asp:Parameter Name="UPDUSR" Type="String" />
 <asp:Parameter Name="CODE" Type="Decimal" />
 <asp:Parameter Name="PROJECT_TYPE" Type="Int32" />
 <asp:Parameter Name="PROJECT_CATEGORY" Type="Int32" />
 <asp:Parameter Name="CLIENT" Type="Int32" />
 <asp:Parameter Name="TITLE" Type="String" />
 <asp:Parameter Name="DESCRIPTION" Type="String" />
 <asp:Parameter Name="LOC_TOWN" Type="String" />
 <asp:Parameter Name="LOC_STREET" Type="String" />
 <asp:Parameter Name="LOC_NUMBER" Type="String" />
 <asp:Parameter Name="LOC_CADASTER" Type="String" />
 <asp:Parameter Name="ACTIVE" Type="Boolean" />
 </InsertParameters>
 </asp:SqlDataSource>
 <asp:SqlDataSource ID="SqlDataSource_GetClient" runat="server" ConnectionString="<%$ ConnectionStrings:TrActionConnectionString %>"
 SelectCommand="SELECT [SEQ], [NAME] FROM [CLIENT] ORDER BY [NAME]"></asp:SqlDataSource>
 <asp:SqlDataSource ID="SqlDataSource_GetProjectCategory" runat="server" ConnectionString="<%$ ConnectionStrings:TrActionConnectionString %>"
 SelectCommand="SELECT [SEQ], [TITLE] FROM [PROJECT_CATEGORY] ORDER BY [CODE]"></asp:SqlDataSource>
 <asp:SqlDataSource ID="SqlDataSource_GetProjectType" runat="server" ConnectionString="<%$ ConnectionStrings:TrActionConnectionString %>"
 SelectCommand="SELECT [SEQ], [TITLE] FROM [PROJECT_TYPE] ORDER BY

Code:

"></asp:SqlDataSource>

Code:

        &nbsp;&nbsp;

    </div>
    </form>
</body>
</html>