Issue inserting null value into a formview/gridview control

Hi,

My formview or gridview control stops updating or deleting a record once the record has a null value.

I have table tblTest with the following

pkID int NOT NULL **IDENTITY COLUMN**
string1 varchar(30)
string2 varchar(30)

I then create a SqlDataSource with the statement:

Select * From [tblTest]

I have the insert, update and delete statements generated, and choose optimistic concurrency. I add a couple records of dummy data.

I then drag a Formview control onto the page, and bind it to the SqlDataSource I just created. I then fire it up in my browser, and I can then update, insert and delete records. However, as soon as I update a record with a null value, I can no longer update or delete that record.

So, if I had a record in my FormView like:

string1: foo
string2: bar

I can update and delete normally. And when I update to:

string1: foo
string2:

the database correctly inserts a null value into string2. However, once that null is in the record, I can't change anything about the record. If I try to delete the record, the FormView will then display the previous record, but I can still page to the record that should have been deleted, and it still exists in the db. If I try to update the record, the edits I make will not keep and the process will fail silently.

What am I doing wrong? Should i be binding to a different object?

Regards,

Chris

Do you set your DataKeyNames for the formview? ( I thought you did). Anyway, here is a working copy and you may find your isseue by yourself.

<asp:FormView ID="fv1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="id"
AllowPaging="true" >

<EditItemTemplate>

col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></asp:TextBox><br />

<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"

Text="Update"
</asp:LinkButton
<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"
</asp:LinkButton
</EditItemTemplate>
<InsertItemTemplate
col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></asp:TextBox><br /

<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
id:

<asp:Label ID="idLabel" runat="server" Text='<%# Eval("id") %>'></asp:Label><br /
col1:

<asp:Label ID="col1_Label" runat="server" Text='<%# Bind("col1") %>'></asp:Label><br />
col2:
<asp:Label ID="col2_Label" runat="server" Text='<%# Bind("col2") %>'></asp:Label><br /
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete"

Text="Delete"
</asp:LinkButton
<asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"

Text="Edit"
</asp:LinkButton
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="New"

Text="New"
</asp:LinkButton
</ItemTemplate>

</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conn1 %>"
DeleteCommand="DELETE FROM [test1] WHERE [id] = @.id"
InsertCommand="INSERT INTO [test1] ( [col1], [col2]) VALUES ( @.col1, @.col2)"
SelectCommand="SELECT [id], [col1], [col2] FROM [test1]"
UpdateCommand="UPDATE [radios] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.id">
<DeleteParameters>
<asp:Parameter Name="id" Type="Int32" />
</DeleteParameters>
<UpdateParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
<asp:Parameter Name="id" Type="Int32" />
</UpdateParameters>
<InsertParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

|||

Ok, our SqlDataSources differ somewhat. Was your auto-generated, or did you create it manually? The following code was generated for me:

============================================================================

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2" InsertCommand="INSERT INTO [tblTest] ([col1], [col2]) VALUES (@.col1, @.col2)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [tblTest]" UpdateCommand="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2"> <DeleteParameters> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> </InsertParameters> </asp:SqlDataSource>

=============================================================================================

 Specifically, my update and delete statements have different, more complicated WHERE clauses. When I change the WHERE clause so that it only uses the ID, the formview control works as it should:

 DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id"

UpdateCommand
="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id"

Am I generating my statements incorrectly? It's bizarre that the SqlDataSource auto-generated command statements choke once they start working with null fields...

Regards,

Chris

|||

Hello,

Both of the codes are generated by the IDE, but you checked the "Use Optimistic Concurrency" to get the more complicated where clause. You may need find more information to use this. If you don't check this one, you will have the same code as I have.

HTH.

|||

That did it, thanks so much!

Regards,

Chris

|||The wizard portion that generates SQL Strings for optimistic queries has that bug. You can still use the wizard to generate the basic query, but you'll need to go in and modify it if any of the fields can be null.