We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SqlDataSource and MySQL Procedure

Ryan Chong
Ryan Chong asked
on
Medium Priority
1,530 Views
Last Modified: 2012-05-06
I got this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
                                            ConnectionString="<%$ ConnectionStrings:connstring %>"
                                            ProviderName="MySql.Data.MySqlClient" SelectCommand="myproc"
                                            SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="false" >
                                            <SelectParameters>
                                                <asp:Parameter DefaultValue="" Name="_id" ConvertEmptyStringToNull="False" />
                                                <asp:Parameter DefaultValue="" Name="_filecourse_id"
                                                    ConvertEmptyStringToNull="False" />
                                                <asp:Parameter DefaultValue="" Name="_keyword"
                                                    ConvertEmptyStringToNull="False" />
                                                <asp:Parameter DefaultValue="" Name="_isactive"
                                                    ConvertEmptyStringToNull="False" />
                                            </SelectParameters>
                                        </asp:SqlDataSource>
                                       
                                       
CREATE PROCEDURE `myproc`(
   in _id varchar(20),
   in _filecourse_id varchar(20),
   in _keyword varchar (100),
   in _isactive varchar(1)
   )
BEGIN

  Set @sql = "Select * from myproc ";
  Set @where = " Where ";

        if _id<>"" then
        Set @where = CONCAT(@where, " id = ",_id," and ");
    end if;

    if _filecourse_id<>"" then
        Set @where = CONCAT(@where, " filecourse_id = ",_filecourse_id," and ");
    end if;

    if _keyword<>"" then
        Set @where = CONCAT(@where, " (title like '%",_keyword,"%' or filepath like '%",_keyword,"%') and ");
    end if;

    if _isactive<>"" then
       Set @where = CONCAT(@where, " isactive = ",_isactive," and ");
    end if;

    if @where<> " Where " then
       Set @where = SUBSTRING(@where,1,length(@where)-4);
       Set @sql = CONCAT(@sql, @where);
    end if;

    PREPARE s1 FROM @sql;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;


END $$

DELIMITER ;



Obviously there are 4 parameters for the procedures, but why everytime I run my script, I got error:

>>Incorrect number of arguments for PROCEDURE myproc; expected 4, got 3

If I doing my debugging in Watch window:

SqlDataSource1.SelectParameters.Count = 4


So weird, I can't get the clue! Any idea to resolve this?
Comment
Watch Question

Hi,

Well, you should always get: SqlDatSource1.SelectParameters.Count = 4 since You have specified 4 select parameters. What You need to verify I guess is that all parameters are getting populated with a value? Just for fun, if you specify a default value for each parameter, will it work then?

/Carl.
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Author

Commented:
>>if you specify a default value for each parameter, will it work then?
No luck, any ideas?
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Author

Commented:
Now even I add the SelectParameters from scripts, I will get same error of:

>>Incorrect number of arguments for PROCEDURE myproc; expected 4, got 3

always got "3"!!


SqlDataSource1.SelectParameters.Clear();
            SqlDataSource1.SelectParameters.Add("_idx", TypeCode.String, "1");
            SqlDataSource1.SelectParameters.Add("_filecourse_id", TypeCode.String, "1");
            SqlDataSource1.SelectParameters.Add("_isactive", TypeCode.String, "1");
            SqlDataSource1.SelectParameters.Add("_keyword", TypeCode.String, "1");
Hi again,

Not sure whether it will make any difference but it could be worth specifying the parameter direction if you haven't already tried that, i.e.:

                <asp:Parameter DefaultValue="" Name="_id" ConvertEmptyStringToNull="False" Direction="Input" />
                <asp:Parameter DefaultValue="" Name="_filecourse_id" ConvertEmptyStringToNull="False" Direction="Input" />
                <asp:Parameter DefaultValue="" Name="_keyword" ConvertEmptyStringToNull="False" Direction="Input" />
                <asp:Parameter DefaultValue="" Name="_isactive" ConvertEmptyStringToNull="False" Direction="Input" />

/Carl.
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Author

Commented:
I ended up with solution like this:


<asp:SqlDataSource ID="SqlDataSource1" runat="server"
                                            ConnectionString="<%$ ConnectionStrings:connstring %>"
                                            ProviderName="<%$ ConnectionStrings:connstring.ProviderName %>"
                                            SelectCommand="call myprod(?_id,?_filecourse_id,?_keyword,?_isactive)"
                                            >
                                            <SelectParameters>
                                                <asp:Parameter Name="?_id" DefaultValue="" Direction="Input" Type="String" ConvertEmptyStringToNull="False" />
                                                <asp:Parameter Name="?_filecourse_id" DefaultValue="" Direction="Input" Type="String" ConvertEmptyStringToNull="False" />
                                                <asp:Parameter Name="?_keyword" DefaultValue="" Direction="Input" Type="String" ConvertEmptyStringToNull="False" />                                                
                                                <asp:Parameter Name="?_isactive" DefaultValue="" Direction="Input" Type="String" ConvertEmptyStringToNull="False" />                                                
                                            </SelectParameters>
                                        </asp:SqlDataSource>


but this doesn't resolved my original question.
Commented:
Question PAQ'd, 500 points refunded, and stored in the solution database.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.