DetailsView is setting fields to null on update

[Cineno]

I have a DetailsView that shows a record from my database based on a queryString value "curEmp", for example "EmployeeProfile.aspx?curEmp=2"

This is showing the records ok, but when I click Edit, after changing a value I hit "Update" and everything except the primary key is set to null.

What am I missing here? I've followed some tutorials and don't think I missed anything they did. I'm using 4.0 and Visual Web Developer 2010 if that makes a difference.

Here is the code:

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
                                        AutoGenerateRows="False" DataKeyNames="ID" DataSourceID="SqlDataSource1">
                                        <Fields>
                                            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
                                                ReadOnly="True" SortExpression="ID" />
                                            <asp:BoundField DataField="ClientID" HeaderText="ClientID"
                                                SortExpression="ClientID" />
                                            <asp:BoundField DataField="UserName" HeaderText="UserName"
                                                SortExpression="UserName" />
                                            <asp:BoundField DataField="Password" HeaderText="Password"
                                                SortExpression="Password" />
                                            <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                                                SortExpression="FirstName" />
                                            <asp:BoundField DataField="LastName" HeaderText="LastName"
                                                SortExpression="LastName" />
                                            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
                                            <asp:BoundField DataField="JobID" HeaderText="JobID" SortExpression="JobID" />
                                            <asp:BoundField DataField="SupervisorID" HeaderText="SupervisorID"
                                                SortExpression="SupervisorID" />
                                            <asp:BoundField DataField="HireDate" HeaderText="HireDate"
                                                SortExpression="HireDate" />
                                            <asp:BoundField DataField="Status" HeaderText="Status"
                                                SortExpression="Status" />
                                            <asp:CommandField ShowEditButton="True" />
                                        </Fields>
                                    </asp:DetailsView>
                                    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                                        ConnectionString="<%$ ConnectionStrings:dbConnectionString %>"
                                        DeleteCommand="DELETE FROM [Users] WHERE [ID] = @ID"
                                        InsertCommand="INSERT INTO [Users] ([ClientID], [UserName], [Password], [FirstName], [LastName], [Email], [JobID], [SupervisorID], [HireDate], [Status]) VALUES (@ClientID, @UserName, @Password, @FirstName, @LastName, @Email, @JobID, @SupervisorID, @HireDate, @Status)"
                                        ProviderName="<%$ ConnectionStrings:dbConnectionString.ProviderName %>"
                                        SelectCommand="SELECT [ID], [ClientID], [UserName], [Password], [FirstName], [LastName], [Email], [JobID], [SupervisorID], [HireDate], [Status] FROM [Users] WHERE [ID] = @ID"
                                        UpdateCommand="UPDATE [Users] SET [ClientID] = @ClientID, [UserName] = @UserName, [Password] = @Password, [FirstName] = @FirstName, [LastName] = @LastName, [Email] = @Email, [JobID] = @JobID, [SupervisorID] = @SupervisorID, [HireDate] = @HireDate, [Status] = @Status WHERE [ID] = @ID">
                                        <SelectParameters>
                                            <asp:QueryStringParameter Name="ID" QueryStringField="curEmp" Type="Int64" />
                                        </SelectParameters>
                                        
                                        <UpdateParameters>
                                            <asp:Parameter Name="ClientID" Type="Int64" />
                                            <asp:Parameter Name="UserName" Type="String" />
                                            <asp:Parameter Name="Password" Type="String" />
                                            <asp:Parameter Name="FirstName" Type="String" />
                                            <asp:Parameter Name="LastName" Type="String" />
                                            <asp:Parameter Name="Email" Type="String" />
                                            <asp:Parameter Name="JobID" Type="Double" />
                                            <asp:Parameter Name="SupervisorID" Type="Double" />
                                            <asp:Parameter Name="HireDate" Type="DateTime" />
                                            <asp:Parameter Name="Status" Type="String" />
                                            <asp:Parameter Name="ID" />
                                        </UpdateParameters>
                                    </asp:SqlDataSource>

 

[jbenson001]

The problem is you are using DataSource controls. They are horrible. They are only good for the simplest of things, and even then I wouldn't use them.
Write your own data access layer and call stored procedures. Return a dataset or datatable and use that for binding. Why do it this way?
Because if you write a stored procedure the code exists only in one place. So you have maintainability and easier reuse. If you wind up using the same queries in multiple pages you have to maintain multiple sets of code. Another reason, security. Use a stored procedure with parameters and you won't be subject to SQL injection.

I know that every example that Microsoft puts out there, they use the datasource controls. But believe me, they are not the way to go. Look into using Microsoft's Enterprise Library:

http://msdn.microsoft.com/en-us/library/ff648951.aspx

 

[Cineno]

Thanks a lot. I'll look into that. Always good to know the better way. However I still don't understand why this isn't working. It's pretty simple, nothing too detailed and I followed examples I found exactly.

 

[jbenson001]

That's another problem about the data source controls, you can't debug them. You will have to use SQL Profiler to view the sql being sent to the sql server.