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>

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

-Scott


"David C" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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>
>
>

But the Type is Int32.
-David
"Scott M." <s-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Could it be that it's because the SSN parameter needs an Int32 and you are
> giving it a string?
>
> -Scott
>
>
> "David C" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> But the Type is Int32.
> -David
> "Scott M." <s-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Could it be that it's because the SSN parameter needs an Int32 and you
>> are giving it a string?
>>
>> -Scott
>>
>>
>> "David C" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>>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,
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." <s-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> But the Type is Int32.
>> -David
>> "Scott M." <s-(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Could it be that it's because the SSN parameter needs an Int32 and you
>>> are giving it a string?
>>>
>>> -Scott
>>>
>>>
>>> "David C" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>>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>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"David C" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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." <s-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> But the Type is Int32.
>>> -David
>>> "Scott M." <s-(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Could it be that it's because the SSN parameter needs an Int32 and you
>>>> are giving it a string?
>>>>
>>>> -Scott
>>>>
>>>>
>>>> "David C" <(E-Mail Removed)> wrote in message
>>>> news:%(E-Mail Removed)...
>>>>>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>
>>>>>

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 M." <s-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "David C" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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." <s-(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> But the Type is Int32.
>>>> -David
>>>> "Scott M." <s-(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> Could it be that it's because the SSN parameter needs an Int32 and you
>>>>> are giving it a string?
>>>>>
>>>>> -Scott
>>>>>
>>>>>
>>>>> "David C" <(E-Mail Removed)> wrote in message
>>>>> news:%(E-Mail Removed)...
>>>>>>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>
>>>>>>
>
> 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,
> 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

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.

"David C" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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>