SqlDataSource and MySQL Procedure

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?
LVL 56
Ryan ChongAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ee_autoConnect With a Mentor Commented:
Question PAQ'd, 500 points refunded, and stored in the solution database.
0
 
carlnorrbomCommented:
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.
0
 
Ryan ChongAuthor Commented:
>>if you specify a default value for each parameter, will it work then?
No luck, any ideas?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Ryan ChongAuthor 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");
0
 
carlnorrbomCommented:
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.
0
 
Ryan ChongAuthor 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.
0
All Courses

From novice to tech pro — start learning today.