DataSource sp problem

[David C]

I am troubleshooting a SQLDataSource tied to a stored procedure to return
records in a GridView. The stored procedure is setup to return matches on
either a SSN or a name. When I enter a name I get results, but when I enter
a SSN it returns nothing. Below are the 2 parameters linked to the 2
TextBox controls. When I run the stored procedure from SSMS, and enter only
the SSN I get results back fine. The stored proc requires a NULL in the name
if the SSN is entered. Can anyone help on this? Thanks.

David

<asp:SqlDataSource ID="SqlPeopleSearch" runat="server"
ConnectionString="<%$ ConnectionStrings:MCFICoreConnectionString %>"
SelectCommand="mc_selPeopleSearchNew"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtFindSSN" Name="SSN"
PropertyName="Text" Type="Int32" DefaultValue="0" />
<asp:ControlParameter ControlID="txtFindName" Name="SearchText"
PropertyName="Text"
Type="String" DefaultValue=""
ConvertEmptyStringToNull="true" />
</SelectParameters>
</asp:SqlDataSource>

 

[Scott M.]

Could it be that it's because the SSN parameter needs an Int32 and you are
giving it a string?

-Scott

 

[David C]

But the Type is Int32.
-David

Could it be that it's because the SSN parameter needs an Int32 and you are
giving it a string?

-Scott

 

[Scott M.]

But you are getting it from a textbox and no matter what you get out of a
Textbox, it will always be a string. Try converting the value in the
textbox to an Int32 and use that value as the parameter for the SP.

-Scott

 

[David C]

Scott,
I am confused so please help me understand. I use parameters all the time
from TextBox controls and they update, insert, etc. just fine as long as I
set the "Type=Int32". Why is this different?
Also, where would I "convert" the value? Thanks.

David

 

[Scott M.]

Scott,
I am confused so please help me understand. I use parameters all the time
from TextBox controls and they update, insert, etc. just fine as long as I
set the "Type=Int32". Why is this different?
Also, where would I "convert" the value? Thanks.

David

The only thing I can see that could be a problem, since the SP works when
called from SSMS, is that the data you are passing is incorrect, especially
since you aren't getting an error, just no results.

Now, since you only have two parameters and they are both coming from
Textboxes, it seems that the one that requires an Int32 should be examined
further, since your aren't actually giving the underlying parameter an
Int32, you are giving it a string. And, for that matter, you said the when
the SSN is supplied that the other parameter should be null. Now, a textbox
isn't going to return null, it can return a String with no characters in it,
but that's not the same thing as null. This could also be the
problem...your query returns no results because it's lookng for a specific
SSN along with a name of "", rather than understanding that there is NO
name.

To solve these 2 potential problems, I would change the parameters so that
they are not tied directly to your Textbox controls, but rather have them
take their values from variables which have been, in the case of the SSN
been converted from a String to an Int32 and in the case of the Name, set
the variable to null when no text is present in the Textbox.

-Scott

 

[David C]

The only thing I can see that could be a problem, since the SP works when
called from SSMS, is that the data you are passing is incorrect,
especially since you aren't getting an error, just no results.

Now, since you only have two parameters and they are both coming from
Textboxes, it seems that the one that requires an Int32 should be examined
further, since your aren't actually giving the underlying parameter an
Int32, you are giving it a string. And, for that matter, you said the
when the SSN is supplied that the other parameter should be null. Now, a
textbox isn't going to return null, it can return a String with no
characters in it, but that's not the same thing as null. This could also
be the problem...your query returns no results because it's lookng for a
specific SSN along with a name of "", rather than understanding that there
is NO name.

To solve these 2 potential problems, I would change the parameters so that
they are not tied directly to your Textbox controls, but rather have them
take their values from variables which have been, in the case of the SSN
been converted from a String to an Int32 and in the case of the Name, set
the variable to null when no text is present in the Textbox.

-Scott

Scott,
What event would I set these parameters in? I am doing a DataBind on the
TextChanged event of the TextBoxes.

Also, if I set a default value for the name search text box then the SSN
search works fine.
What else can I provide that will help resolve this?

David

 

[Scott M.]

Scott,
What event would I set these parameters in? I am doing a DataBind on the
TextChanged event of the TextBoxes.

Also, if I set a default value for the name search text box then the SSN
search works fine.
What else can I provide that will help resolve this?

David

Based on that, it seems that the culprit is the null vs. empty string
values.

You can set these parameter values in whatever event handler you're already
working with to perform the SP.

-Scott

 

[Paul Shapiro]

When you want to run the Select with a null parameter you need to include
CancelSelectOnNullParameter="false"
in your SQLDataSource parameters. To verify this, you can run SQL Server
Profiler to see the commands being sent to the db by the web server.