Solved

Error while executing MySQL Stored Procedure

Posted on 2008-11-02
8
283 Views
Last Modified: 2012-05-05
When I try to execute following code, it gives error. See the image. Though the stored procedure is already present in the database. What's the problem?

OpenConnection();

MySqlCommand cmdStockHistoryReport = new MySqlCommand("procStockHistoryReport",myCN);

 

cmdStockHistoryReport.CommandType = CommandType.StoredProcedure;

cmdStockHistoryReport.Parameters.AddWithValue("ForDate", dtpDate.Value.ToString("yyyy-MM-dd"));
 

cmdStockHistoryReport.ExecuteNonQuery();

cmdStockHistoryReport.Dispose();

CloseConnection();

Open in new window

sp-error2.JPG
0
Comment
Question by:rpkhare
8 Comments
 
LVL 26

Expert Comment

by:Anurag Thakur
Comment Utility
the exception clearly states that the sp is not found
it means that either you connection string is pointing to a different database or the user with which you are authenticating the connection string does not have permissions to execute you stored procedure
0
 
LVL 26

Expert Comment

by:ushastry
Comment Utility
Agree with Prev comment...

Try this and see where is that routine first.. if you get any records stating "procStockHistoryReport" is in some schema then just prefix that schema to procStockHistoryReport
SELECT ROUTINE_NAME,ROUTINE_SCHEMA

  FROM information_schema.ROUTINES

 WHERE ROUTINE_NAME like "procStockHistoryReport";

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 60 total points
Comment Utility
eventually a case sensitivity issue?
or the user you connect with has no permissions to that procedure?
or the procedure is not in the database you connected to, ie you connected to the wrong db...
0
 

Expert Comment

by:Paramhans
Comment Utility
hv u checked the owner of the stored procedure in your db.

if you are connecting the db with default 'sa' user then your code should work else you need to use the user name while calling the procedure.

eg. UName.procStockHistoryReport



hope it works for you.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 8

Author Comment

by:rpkhare
Comment Utility
Attached below is the image of the output received with the query given by: ushastry.
query-result.JPG
0
 
LVL 26

Accepted Solution

by:
ushastry earned 65 total points
Comment Utility
Gr8...  its a case issue...   your schema name is "stockist" abut error shows that procedure cannot be found in "Stockist" ... check where use are using "Stockist"  and replace that with "stockist"...




OpenConnection();

MySqlCommand cmdStockHistoryReport = new MySqlCommand("stockist.procStockHistoryReport",myCN);

 

cmdStockHistoryReport.CommandType = CommandType.StoredProcedure;

cmdStockHistoryReport.Parameters.AddWithValue("ForDate", dtpDate.Value.ToString("yyyy-MM-dd"));

 

cmdStockHistoryReport.ExecuteNonQuery();

cmdStockHistoryReport.Dispose();

CloseConnection();

Open in new window

0
 
LVL 8

Author Comment

by:rpkhare
Comment Utility
Correct. It is a case-sensitivity issue. MySQL is objecting on the database name. It is not considering 'Stockist'? It is considering 'stockist'.

Funny. It has no objection with the case of variables and stored procedure name.
0
 
LVL 26

Expert Comment

by:ushastry
Comment Utility
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
visual Basic Net vs c# net 8 54
.net VBA word safemode 1 23
C# Reverse int in fast ways 6 27
MSSQL 2008 with mySQL webservers 7 23
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now