Issue with SQLDataSource and FilterExpression

I'm not sure exactly how the FilterExpression works. I have a sqldatasource, stored procedure, and GridView. My stored procedure basicly a select statement to populate my gridview, it included the fields I want to filter on. In my codebehind file I build a WHERE Clause based on the entries a user makes. Then I add the my FilterExpr variable to the SqlDataSource1.FilterExpression = FilterExpr. My SqlDataSource has a number of control parameters that match the textboxes a users enters into.

My question, I guess is does my stored procedure need the variables matching my controlparameters for my sqldatasource? Or how does this work? My GridView is returning all rows no matter what I enter into the filter textboxes (first, last, etc...)

MY SQLSDATASOURCE

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="ClientSearch" SelectCommandType="StoredProcedure"> <FilterParameters> <asp:ControlParameter ControlID="SearchLastName" Name="LastName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchFirstName" Name="FirstName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchEmail" Name="Email" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchAddress" Name="Address" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchComment" Name="Comment" PropertyName="Text" ConvertEmptyStringToNull="true" /> </FilterParameters> </asp:SqlDataSource>

MY CODEBEHIND

       Dim FilterExprAs String
        If SearchLastName.Text =""And _
            SearchFirstName.Text =""And _
            SearchEmail.Text =""And _
            SearchComment.Text =""And _
            SearchAddress.Text =""Then
            lblMessage.Text ="You didn't enter any search parameters. Please try Again."
            Me.GridViewSearch.DataSourceID = ""
            Exit Sub
        Else
            Me.GridViewSearch.DataSourceID = "SqlDataSource1"
        End If
        FilterExpr = ""
        If SearchLastName.Text <> "" Then
            FilterExpr = FilterExpr & "LastNameLike'" & _
            SearchLastName.Text &"%" &"' AND "
        End If
        If SearchFirstName.Text <> "" Then
            FilterExpr = FilterExpr & "FirstNameLike'" & _
            SearchFirstName.Text &"%" &"' AND "
        End If
        If SearchEmail.Text <> "" Then
            FilterExpr = FilterExpr & "Like'" & _
             SearchEmail.Text &"%" &"' AND "
        End If
        If SearchComment.Text <> "" Then
            FilterExpr = FilterExpr & "[Comments]Like'" & "%" & _
             SearchComment.Text &"%" &"' AND "
        End If
        If SearchAddress.Text <> "" Then
            FilterExpr = FilterExpr & "[Address]Like'" & "%" & _
             SearchAddress.Text &"%" &"' AND "
        End If
        If Right(FilterExpr, 4) = "AND "Then
            FilterExpr = Left(FilterExpr, Len(FilterExpr) - 4)
       End If
        Try
            Me.SqlDataSource1.FilterExpression = FilterExpr
           Me.SqlDataSource1.DataBind()
           Me.GridViewSearch.DataBind()
           Me.lblMessage.Text =Me.SqlDataSource1.FilterExpression
       Catch objExceptionAs SqlException
           Dim objErrorAs SqlError
           For Each objErrorIn objException.Errors
                Response.Write(objError.Message)
           Next
        End Try
    End Sub 

MY SPROC

GOALTER PROCEDURE [dbo].[ClientSearch]ASSELECT C.ClientID, C.FirstName, C.LastName, A.Address, C.Comments, C.EMailFROM tblClient CINNERJOIN tblClientAddresses AON C.ClientID = A.ClientID

Remove all the <FilterParameters> and it should work fine.

Issue with getting values from child controls in a gridview, to use for the update using a

Hi all,

I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:

<

asp:GridViewID="GridEditSettlement"runat="server"AutoGenerateColumns="False"BackColor="Navy"BorderColor="IndianRed"BorderStyle="Solid"Font-Names="Verdana"Font-Size="X-Small"DataSourceID="SqlDataSource_grid"AllowPaging="True"AllowSorting="True"ForeColor="White"DataKeyNames="legid"><Columns><asp:CommandFieldShowEditButton="True"CancelImageUrl="~/App_Graphics/quit.gif"CancelText=""EditImageUrl="~/App_Graphics/EditGrid.GIF"EditText=""UpdateImageUrl="~/App_Graphics/save.gif"UpdateText=""ButtonType="Image"/><asp:BoundFieldDataField="StartDate"HeaderText="Start Date"ReadOnly="True"/><asp:BoundFieldDataField="EndDate"HeaderText="End Date"ReadOnly="True"/><asp:BoundFieldDataField="CounterpartDealRef"HeaderText="CP Deal Ref"ReadOnly="True"/>

<asp:TemplateFieldHeaderText="Preliminary Settlement Price"><ItemTemplate><asp:LabelID=lblPreliminaryrunat=serverText='<%# Bind("PrimarySettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtPrimaryPriceText='<%# Bind("PrimarySettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:TemplateFieldHeaderText="Agreed Settlement Price"><ItemTemplate><asp:LabelID=lblAgreedrunat=serverText='<%# Bind("AgreedSettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtAgreedPriceText='<%# Bind("AgreedSettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:BoundFieldDataField="Volume"HeaderText="Volume"ReadOnly="True"/><asp:BoundFieldDataField="Price"HeaderText="Price"ReadOnly="True"/><asp:BoundFieldDataField="TotalVolume"HeaderText="Total Volume"ReadOnly="True"/><asp:BoundFieldDataField="InstrumentName"HeaderText="Instrument"ReadOnly="True"/><asp:BoundFieldDataField="NominalValue"HeaderText="Nominal Value"ReadOnly="True"/><asp:BoundFieldDataField="Strike"HeaderText="Strike"ReadOnly="True"/><asp:BoundFieldDataField="DeliveryDate"HeaderText="Delivery Date"ReadOnly="True"/><asp:TemplateFieldHeaderText="LegId"SortExpression="LegId"><ItemTemplate><asp:LabelID="lblLegID"runat="server"Text='<%# Bind("LegId") %>'></asp:Label></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtLegIDText='<%# Bind("LegId") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

</Columns><RowStyleBackColor="#FFFF66"ForeColor="#333333"/><EditRowStyleBackColor="#FFFF66"Font-Names="Verdana"Font-Size="X-Small"ForeColor="#333333"/><PagerStyleForeColor="White"/><AlternatingRowStyleBackColor="White"ForeColor="#333333"/></asp:GridView> <br/>

<asp:SqlDataSourceID="SqlDataSource_grid"runat="server"ConnectionString="<%$ ConnectionStrings:DealCaptureDev %>"SelectCommand="sp_get_single_deal"SelectCommandType="StoredProcedure"UpdateCommand="Update trDealLeg Set PrimarySettlementPrice=@.primarysettlement, AgreedSettlementprice=@.agreedsettlement, LastUpdate=GetDate(), LastUpdateBy=Session('userid') Where LegID=@.legid"EnableCaching="True"ConflictDetection="CompareAllValues"ProviderName="System.Data.SqlClient"><SelectParameters><asp:QueryStringParameterDefaultValue=""Name="dealnum"QueryStringField="deal"Type="String"/></SelectParameters><UpdateParameters><asp:ControlParameterControlID="txtLegId"PropertyName="Text"Name="legId"/><asp:ControlParameterControlID="txtPrimarySettlement"Name="primarysettlement"PropertyName="Text"/><asp:ControlParameterControlID="txtAgreedSettlement"Name="agreedsettlement"PropertyName="Text"/><asp:SessionParameterDefaultValue=""Name="userid"SessionField="userid"/></UpdateParameters></asp:SqlDataSource>

As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work).

So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event.

What is the best approach? Anyone come up against this issue before?

Thanks,

KB

You could try this way:

code in gridview: <asp:TemplateField HeaderText="company name" SortExpression="camcompany"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("camcompany")%>'></asp:Label> </ItemTemplate> </asp:TemplateField>code in sqlDataSource: <UpdateParameters> <asp:Parameter Name="camcompany" Type="String" /> ......... </UpdateParameters>

You can specify a row and access the controls within that row:

Dim lastNameAsString = selectRow.Cells(1).Text

You can access the individual cells of theGridViewRow object by using theCells property. If a cell contains other controls, you can retrieve a control from the cell by using theControls collection of the cell. You can also use theFindControl method of the cell to find the control, if the control has anID specified.

seehttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridviewrow.aspx for details.

Hope it helps.

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.