Can't quite get DetailsView Insert to work

[joelwenzel]

Hi,

I think I'm very close to getting the DetailsView insert/update/delete to work but I haven't quite got it. Does anyone know what's wrong? Delete and update don't do anything. Insert adds a new row of data in my database, but the values are NULL except for the identity column.

Here is my code. Users2 is a simple table that just contains the columns UserID, FirstName, and LastName.



<script runat="server">
Protected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs)
DetailsView1.DataBind()
End Sub
</script>

<asp:SqlDataSource ID="userSqlDataSource" runat="server"
ProviderName="<%$ ConnectionStrings:GolfConnectionString.ProviderName %>"
ConnectionString="<%$ ConnectionStrings:GolfConnectionString %>"
SelectCommand="SELECT * FROM Users2"
InsertCommand="INSERT INTO Users2(FirstName, LastName) VALUES(@FirstName, @LastName)"
DeleteCommand="DELETE FROM Users2 WHERE UserID = @UserID "
UpdateCommand="UPDATE Users2 SET FirstName = @FirstName, LastName = @LastName WHERE UserID = @UserID"
>
<InsertParameters>
<asp:ControlParameter ControlID="DetailsView1" Name="FirstName" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="DetailsView1" Name="LastName" PropertyName="DataItem" Type="String" />
</InsertParameters>
<DeleteParameters>
<asp:ControlParameter ControlID="DetailsView1" Name="UserID" PropertyName="SelectedValue" />
</DeleteParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="DetailsView1" Name="UserID"
PropertyName="SelectedValue" Size="200" Type="Int32" />
<asp:ControlParameter ControlID="DetailsView1" Name="FirstName"
PropertyName="SelectedValue" Size="200" Type="String" />
<asp:ControlParameter ControlID="DetailsView1" Name="LastName"
PropertyName="SelectedValue" Size="200" Type="String" />
</UpdateParameters>

</asp:SqlDataSource>

<aspetailsView OnItemUpdated="DetailsView1_ItemUpdated" ID="DetailsView1"
runat="server" Height="50px" Width="125px"

DataSourceID="userSqlDataSource" AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True" AutoGenerateInsertButton="True" AutoGenerateRows="False"
AllowPaging="True">
<Fields>
<asp:TemplateField HeaderText="UserID" InsertVisible="False">
<ItemTemplate>
<asp:Label ID="lblUserID" Text='<%# Eval("UserID") %>' runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="FirstName">
<InsertItemTemplate>
<asp:TextBox ID="txtFirstName" Text='<%# Bind("FirstName") %>' runat="server"></asp:TextBox>
<br />
<asp:RequiredFieldValidator ID="rfvFirstName" runat="server" ControlToValidate="txtFirstName"
ErrorMessage="Invalid"></asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="lblFirstName" Text='<%# Eval("FirstName") %>' runat="server"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="LastName">
<InsertItemTemplate>
<asp:TextBox ID="txtLastName" Text='<%# Bind("LastName") %>' runat="server"></asp:TextBox>
<br />
<asp:RequiredFieldValidator ID="rfvLastName" runat="server" ErrorMessage="Invalid" ControlToValidate="txtLastName"></asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="lblLastName" Text='<%# Eval("LastName") %>' runat="server"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditLastName" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Fields>

</aspetailsView>

 

[jbenson001]

Is this code being generated by VS?

Also, are you getting errors, or are you saying you click Update and delete and nothing happens at all,just a refresh?

Jim

 

[joelwenzel]

The code is generated by VS..and by me. It is the same either way. No errors. When I click update and delete, I just get a refresh. Nothing else happens.

 

[jbenson001]

What code are you writing? I tried a quick test page and let VS create the code, everything worked fine.

 

[joelwenzel]

i added the template fields. Could you post the code for your test page? I would like to try it on my system and see if it works. That would probably solve my problem.

 

[jbenson001]

Here is my HTML:

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]

<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource1" Height="50px"
            Width="125px" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical" AllowPaging="True" HorizontalAlign="Justify" AutoGenerateRows="False">
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <EditRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="Gainsboro" />
            <Fields>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
                <asp:BoundField DataField="ResponseTypeID" HeaderText="ResponseTypeID" InsertVisible="False"
                    ReadOnly="True" SortExpression="ResponseTypeID" />
                <asp:CheckBoxField DataField="ActiveFlg" HeaderText="ActiveFlg" SortExpression="ActiveFlg" />
                <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
                <asp:BoundField DataField="CreatedDT" HeaderText="CreatedDT" SortExpression="CreatedDT" InsertVisible="False" />
                <asp:BoundField DataField="ModifiedDT" HeaderText="ModifiedDT" SortExpression="ModifiedDT" InsertVisible="False" />
            </Fields>
        </asp:DetailsView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
            ConnectionString="<%$ ConnectionStrings:PatientManagementConnectionStringLOCAL %>"
            DeleteCommand="DELETE FROM [ResponseTypes] WHERE [ResponseTypeID] = @original_ResponseTypeID AND [Description] = @original_Description AND [ActiveFlg] = @original_ActiveFlg AND [CreatedDT] = @original_CreatedDT AND [ModifiedDT] = @original_ModifiedDT"
            InsertCommand="INSERT INTO [ResponseTypes] ([Description], [ActiveFlg]) VALUES (@Description, @ActiveFlg )"
            OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [ResponseTypes]"
            UpdateCommand="UPDATE [ResponseTypes] SET [Description] = @Description, [ActiveFlg] = @ActiveFlg, [CreatedDT] = @CreatedDT, [ModifiedDT] = @ModifiedDT WHERE [ResponseTypeID] = @original_ResponseTypeID AND [Description] = @original_Description AND [ActiveFlg] = @original_ActiveFlg AND [CreatedDT] = @original_CreatedDT AND [ModifiedDT] = @original_ModifiedDT">
            <DeleteParameters>
                <asp:Parameter Name="original_ResponseTypeID" Type="Int32" />
                <asp:Parameter Name="original_Description" Type="String" />
                <asp:Parameter Name="original_ActiveFlg" Type="Boolean" />
                <asp:Parameter Name="original_CreatedDT" Type="DateTime" />
                <asp:Parameter Name="original_ModifiedDT" Type="DateTime" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="Description" Type="String" />
                <asp:Parameter Name="ActiveFlg" Type="Boolean" />
                <asp:Parameter Name="CreatedDT" Type="DateTime" />
                <asp:Parameter Name="ModifiedDT" Type="DateTime" />
                <asp:Parameter Name="original_ResponseTypeID" Type="Int32" />
                <asp:Parameter Name="original_Description" Type="String" />
                <asp:Parameter Name="original_ActiveFlg" Type="Boolean" />
                <asp:Parameter Name="original_CreatedDT" Type="DateTime" />
                <asp:Parameter Name="original_ModifiedDT" Type="DateTime" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="Description" Type="String" />
                <asp:Parameter Name="ActiveFlg" Type="Boolean" />
                <asp:Parameter Name="CreatedDT" Type="DateTime" />
                <asp:Parameter Name="ModifiedDT" Type="DateTime" />
            </InsertParameters>
        </asp:SqlDataSource>
        &nbsp;
    
    </div>
    </form>
</body>
</html>

 

[joelwenzel]

thanks for your help. I really really appreciate it.

anyway, your code seems to be having the same problem as mine. The edit/delete just do a refresh...and insert tries to insert a blank record (UserName is set to null even if I type something into the text box). First, I should say that I am using a MySQL database. Here is the code you game me, modified for a table consisting of UserID, UserName columns. For some reason MySQL didn't like the '[' and ']' syntax so I had to remove it.

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

<!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 id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<aspetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource1" Height="50px"
Width="125px" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3"
GridLines="Vertical" AllowPaging="True" HorizontalAlign="Justify" AutoGenerateRows="False">
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<EditRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="Gainsboro" />
<Fields>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
<asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False"
ReadOnly="True" SortExpression="UserID" />
<asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
</Fields>
</aspetailsView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:GolfConnectionString %>"
ProviderName="<%$ ConnectionStrings:GolfConnectionString.ProviderName %>"

DeleteCommand="DELETE FROM Users2 WHERE UserID = @original_UserID"
InsertCommand="INSERT INTO Users2 (UserName) VALUES (@UserName )"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM Users2"
UpdateCommand="UPDATE Users2 SET UserName = @UserName WHERE UserID = @original_UserID"
>

<DeleteParameters>
<asparameter Name="original_UserID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="UserName" Type="String" />
<asparameter Name="original_UserID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="UserName" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
&nbsp;

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

 

[joelwenzel]

Ok, I finally figured out the problem. In my select, Update, and delete commands I was using @parameterName but I should have been using ? instead (since I'm using MySQL).

 

[jbenson001]

Ahh.. glad you got it working...

Jim