Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error while executing MySQL Stored Procedure

Posted on 2008-11-02
8
Medium Priority
?
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22865015
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:Umesh
ID: 22865036
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 180 total points
ID: 22865108
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Expert Comment

by:Paramhans
ID: 22865830
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
 
LVL 8

Author Comment

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

Accepted Solution

by:
Umesh earned 195 total points
ID: 22865893
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
ID: 22874064
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:Umesh
ID: 22874094
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

664 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