Error in stored procedure call [Answered]RSS

• • Reply
  VictorZaslav...

  None

  0 Points

  3 Posts

  Error in stored procedure call

  Jun 19, 2016 09:41 PM|VictorZaslavsky|LINK

  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.

• • Reply
  oned_gk

  All-Star

  52262 Points

  15556 Posts

  Re: Error in stored procedure call

  Jun 20, 2016 01:49 AM|oned_gk|LINK

  Try change this

  SelectCommand="GetPersonAndLock" SelectCommandType="StoredProcedure"

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  VictorZaslav...

  None

  0 Points

  3 Posts

  Re: Error in stored procedure call

  Jun 20, 2016 02:40 AM|VictorZaslavsky|LINK

  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.

• • Reply
  Cole Wu

  Contributor

  2570 Points

  501 Posts

  Re: Error in stored procedure call

  Jun 20, 2016 07:36 AM|Cole Wu|LINK

  Hi VictorZaslavsky,

  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

• • Reply
  VictorZaslav...

  None

  0 Points

  3 Posts

  Re: Error in stored procedure call

  Jun 20, 2016 09:56 AM|VictorZaslavsky|LINK

  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.

• • Reply
  Cole Wu

  Contributor

  2570 Points

  501 Posts

  Re: Error in stored procedure call

  Jul 06, 2016 08:09 AM|Cole Wu|LINK

  Hi VictorZaslavsky,

  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