locked
Error in stored procedure call RRS feed

  • Question

  • User-1778304615 posted

    Environment: IIS 7 + MySQL 5.7.13.

    Stored procedure "GetPersonAndLock" should receive parameter of type INT.

    Data source configuration:

                            <asp:SqlDataSource  ID="RundomPerson"
                                                runat="server"
                                                ConnectionString="<%$ ConnectionStrings:MySQLConnectionString %>"
                                                ProviderName="<%$ ConnectionStrings:MySQLConnectionString.ProviderName %>"
                                                SelectCommand="call GetPersonAndLock"
                                                SelectCommandType="StoredProcedure"
                                              >
                                <SelectParameters>
                                    <asp:CookieParameter CookieName="UserId" Type="Int32" />
                                </SelectParameters>
                            </asp:SqlDataSource>

    Error:

    ERROR [HY000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.13-log]Incorrect number of arguments for PROCEDURE election phone book.GetPersonAndLock; expected 1, got 0

    Any advice?

    P.S. By unknown reason the "Stored Procedure" radio button of the source configuration wizard is disabled. Data source configuration was edited manually.

    Sunday, June 19, 2016 9:41 PM

Answers

  • User1559292362 posted

    Hi VictorZaslavsky,

    I just tried it and unfortunately it fails in the same way: missing parameter when the command is "call GetPersonAndLock" and invalid syntax when the command is "GetPersonAndLock".

    Based on your description, I create a simple demo as below. it works well, please check it. For policy reason, we couldn't use MySQL, I use localdb, it's similar with MySQL. 

    #store procedure.

    USE [AspDotNetDemo]
    GO
    
    /****** Object: SqlProcedure [dbo].[Customers_SearchCustomers] Script Date: 7/6/2016 4:04:21 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE PROCEDURE [dbo].[Customers_SearchCustomers] 
    
          @ContactName NVARCHAR(30)
    
    AS
    
    BEGIN
    
          SET NOCOUNT ON;
          SELECT CustomerId
                ,ContactName
                ,Country
          FROM Customer
          WHERE ContactName LIKE @ContactName + '%'
    
    END
    

    #ASP

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SqlDataSourceWithSPAndSessionParameter.aspx.cs" Inherits="ADONETDEMO.GridViewDemo.SqlDataSourceWithSPAndSessionParameter" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerId" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" ReadOnly="True" SortExpression="CustomerId" />
                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
                    <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AspDotNetDemoConnectionString %>" SelectCommand="Customers_SearchCustomers" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:CookieParameter CookieName="UserId" Name="ContactName" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    

    #Code Behind.

    using System;
    using System.Web;
    
    namespace ADONETDEMO.GridViewDemo
    {
        public partial class SqlDataSourceWithSPAndSessionParameter : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    HttpCookie myCookie1 = new HttpCookie("UserId");
                    myCookie1.Value = "a";
                    Response.Cookies.Add(myCookie1);
                }
            }
        }
    }

    In addition, please check if the parameter name is the same with SqlDataSource CookieParameter's name.

    If the issue still exist, could you please provide the store procedure.

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 6, 2016 8:09 AM

All replies

  • User-1716253493 posted

    Try change this

    SelectCommand="GetPersonAndLock" SelectCommandType="StoredProcedure"

    Monday, June 20, 2016 1:49 AM
  • User-1778304615 posted

    Hello oneg_gk,

    I've tried that initially. In that case I get another error:

    ERROR [42000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.13-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GetPersonAndLock' at line 1

    The stored procedure itself works correctly, I've tested it using MySQL Workbench.

    Also, another stored procedure which does not have parameters works correctly when invoked from asp.net using the same connection string.

    Monday, June 20, 2016 2:40 AM
  • User1559292362 posted

    Hi VictorZaslavsky,

    ERROR [42000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.13-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GetPersonAndLock' at line 1

    Based on you code, it seems that you need to parameter name on your sqldatasource control.

    <asp:SqlDataSource  ID="RundomPerson" 
                        runat="server" 
                        ConnectionString="<%$ ConnectionStrings:MySQLConnectionString %>" 
                        ProviderName="<%$ ConnectionStrings:MySQLConnectionString.ProviderName %>" 
                        SelectCommand="GetPersonAndLock"
                        SelectCommandType="StoredProcedure"
                                               >
       <SelectParameters>
          <asp:CookieParameter CookieName="UserId" Name="UserId" Type="Int32" />
       </SelectParameters>
    </asp:SqlDataSource>

    Best regards,

    Cole Wu

    Monday, June 20, 2016 7:36 AM
  • User-1778304615 posted

    Hello Cole,

    Thanks for your reply.

    I just tried it and unfortunately it fails in the same way: missing parameter when the command is "call GetPersonAndLock" and invalid syntax when the command is "GetPersonAndLock".

    Regards,

    Victor.

    Monday, June 20, 2016 9:56 AM
  • User1559292362 posted

    Hi VictorZaslavsky,

    I just tried it and unfortunately it fails in the same way: missing parameter when the command is "call GetPersonAndLock" and invalid syntax when the command is "GetPersonAndLock".

    Based on your description, I create a simple demo as below. it works well, please check it. For policy reason, we couldn't use MySQL, I use localdb, it's similar with MySQL. 

    #store procedure.

    USE [AspDotNetDemo]
    GO
    
    /****** Object: SqlProcedure [dbo].[Customers_SearchCustomers] Script Date: 7/6/2016 4:04:21 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE PROCEDURE [dbo].[Customers_SearchCustomers] 
    
          @ContactName NVARCHAR(30)
    
    AS
    
    BEGIN
    
          SET NOCOUNT ON;
          SELECT CustomerId
                ,ContactName
                ,Country
          FROM Customer
          WHERE ContactName LIKE @ContactName + '%'
    
    END
    

    #ASP

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SqlDataSourceWithSPAndSessionParameter.aspx.cs" Inherits="ADONETDEMO.GridViewDemo.SqlDataSourceWithSPAndSessionParameter" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerId" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" ReadOnly="True" SortExpression="CustomerId" />
                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
                    <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AspDotNetDemoConnectionString %>" SelectCommand="Customers_SearchCustomers" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:CookieParameter CookieName="UserId" Name="ContactName" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    

    #Code Behind.

    using System;
    using System.Web;
    
    namespace ADONETDEMO.GridViewDemo
    {
        public partial class SqlDataSourceWithSPAndSessionParameter : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    HttpCookie myCookie1 = new HttpCookie("UserId");
                    myCookie1.Value = "a";
                    Response.Cookies.Add(myCookie1);
                }
            }
        }
    }

    In addition, please check if the parameter name is the same with SqlDataSource CookieParameter's name.

    If the issue still exist, could you please provide the store procedure.

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 6, 2016 8:09 AM