Of Multiple Parameters, SqlDataSource and Text boxes

Hi,
Hope if someone can help me here. Keep in mind I an fairly new to .NET and SQL and am learning to break my MS Access habit :)

I have a web form that is using a SqlDataSource and a FormView control. In addition to this I have 2 text boxes. What I am trying to do is display results in the FormView based on what a user types into one of the Text Boxes (one or the other…Not both)

The SELECT statement in the SqlDataSource looks like this in concept.

SELECT Field1, Field2, Field3, Field4
FROM dbo.MYTABLE
WHERE (Field1 = @.Field1) AND (Field2 IS NULL)
OR (Field2 = @.Field2) AND (Field1 IS NULL)

I have the two text boxes pointing at the parameters (@.Field1 and @.Field2) so in theory I would expect that when a user populates one of the text boxes and clicks a button to databind the FormView it would display a record matching that criteria…. But it's not all I get is a blank/missing FormView.

I tried different variations on the SQL statement and tried using = '' instead of IS NULL but still the same results.
However, if I populate one text box with a value that I know is not in my table and populate the other with a value of which I know exists in my table is…It works.
What am I missing?

SELECT Field1, Field2, Field3, Field4
FROM dbo.MYTABLE
WHERE (Field1 = @.Field1) AND (Field2 IS NULL)
OR (Field2 = @.Field2) AND (Field1 IS NULL)

Should be:

SELECT Field1, Field2, Field3, Field4
FROM dbo.MYTABLE
WHERE (Field1 = @.Field1) AND (@.Field2 IS NULL)
OR (Field2 = @.Field2) AND (@.Field1 IS NULL)

However, I suspect that there are other issues you are having, as your original statement would have only worked if you actually had a NULL in one of your search fields. Please copy and paste the SqlDataSource control from the .ASPX page, as I'm guessing that it's not really a logical problem, more like an oops, I knew that problem.

|||

Opps yeah...That was a typo on my part. Well the field in the text box is blank and I have "ConvertEmptyString ToNull" in the parameters advanced properties set to true. But, your right it is behaving as though the query not seeing null.

Is there syntax to set the default value of the parameter to null in the Configure Data Source/Query Builder pop up ? (Using VS 2005)

|||

Here is the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:QualityConnectionString %>"

SelectCommand="SELECT WWID, WONumber, WorkWeek, ID FROM dbo.WorkOrderInfo WHERE (ID = @.ID) AND (@.WONumber = '') OR (@.ID = '') AND (WONumber = @.WONumber)" EnableCaching="True">

<FilterParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID" PropertyName="Text" />

</FilterParameters>

<SelectParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID" PropertyName="Text" />

<asp:ControlParameter ControlID="TextBox2" Name="WONumber" PropertyName="Text" />

</SelectParameters>

</asp:SqlDataSource>

|||

Remove the filter parameters section, it isn't needed, and would cause some queries to fail when they shouldn't.

Also, you either need to change ConvertEmptyStringsToNull (Real property name should be close) to false, OR change the ='' parts of your query to IS NULL, since the empty string parameters are being converted to NULL. That should take care of it for you.

|||

OK have this now and still not working. Could it have something to do with VB passing NULL as Nothing?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:QualityConnectionString %>"

SelectCommand="SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE (ID = @.ID) AND (WONumber IS NULL) OR (ID IS NULL) AND (WONumber = @.WONumber)">

<SelectParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID"

PropertyName="Text" Type="Int32" DefaultValue="" />

<asp:ControlParameter ControlID="TextBox2" Name="WONumber" PropertyName="Text"

Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

|||

took out the default values...same result

OK have this now and still not working. Could it have something to do with VB passing NULL as Nothing?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:QualityConnectionString %>"

SelectCommand="SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE (ID = @.ID) AND (WONumber IS NULL) OR (ID IS NULL) AND (WONumber = @.WONumber)">

<SelectParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID"

PropertyName="Text" Type="Int32" />

<asp:ControlParameter ControlID="TextBox2" Name="WONumber" PropertyName="Text"

Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

|||

I'm pretty sure you have it right. Try making a new page, then copy/paste the SqlDataSource on it. Drag 2 NEW textboxes on the new page, and drag a NEW gridview on the page. Configure the grid to display data from the sqldatasource, and run the page and see if it works.

|||Tried from scratch same result. The empty text boxes must not be passing a Null. I found a work around by setting the default paramater values to a negative integer and leaving out the @.parameter IS NULL ...But this is driving me insane!|||

try this instead:

SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE ((ID = @.ID) OR (@.ID IS NULL)) AND ((WONumber = @.WONumber) OR (@.WONumber IS NULL))

The parameters should have no defaults, and convertemtystringtonull should be true.

|||

That didn't work either. Setting ConvertEmptyString ToNull toFalse and the below SQL statement works though. I thought I tried that before but must have missed a parameter property setting.

SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE (ID = @.ID) OR (WONumber = @.WONumber)

This is odd because I have another page with a reportviewer that uses a table adaptor etc. and several text boxes for passing parameters to a stored procedure to generate data for the report. Any one of them can be null and it works great. Not sure what is going on here though.

|||I also changed to parameter type to "Empty" (default) not int32...|||

Agh!! I was wrong it actualy does not work the way I wanted. Seems the empty string (blank text box) for the WONumber parameter thinks it is a zero too. So for any WONumber that is 0 and the text box being empty, it will retireve that record.

Theonlyway I can get to work is a stored procedure.

CREATE PROCEDURE dbo.SelectWO(@.ID int,
@.WONumber int )
AS
If @.ID = ''
SET @.ID = Null
If @.WONumber = ''
SET @.WONumber = Null
SELECT ID, WWID, WONumber,FROM dbo.WorkOrderInfo
WHERE (ID = @.ID AND @.WONumber IS NULL) OR (WONumber = @.WONumber AND @.ID IS NULL)

OR( @.ID IS NULL AND @.WONumber IS NULL)

ORDER BY ID
GO

Had to leave ConvertEmptyStringtoNull to false and convert in my SP. setting it to True in VS breaks the SP.

Anyone ...try to build a web form with 2 search parameters getting thier values from to text boxes see if you can get it working cause I could not.

|||

/sigh

CREATE PROCEDURE dbo.SelectWO(@.ID int,
@.WONumber int )
AS
SELECT ID, WWID, WONumber,FROM dbo.WorkOrderInfo
WHERE (ID = @.ID AND @.WONumber IS NULL) OR (WONumber = @.WONumber AND @.ID IS NULL)OR( @.ID IS NULL AND @.WONumber IS NULL)
ORDER BY ID
GO

Is the SP you want. Change the ConvertEmptyStringToNull's on both parameters to true, and explicitly set the parameter types to "int"/"integer". Also make sure you set the sqldatasouce property "CancelOnNullParameter" to false.

It'll work.

The problem is that yes, if you leave ConvertEmptyStringToNull false, the paramters will be converted to 0. Your checks IF @.ID='' will never be true, but @.ID is an int, and int's can never be an empty string. They can only be an integer or NULL.