Solved

Problem with passing parameter to MySQL Stored Procedure

Posted on 2009-05-14
4
782 Views
Last Modified: 2013-12-25
I am using VWD 2005 Express and MySQL 5.1.34

I have tried some very simple stored procedures to test them in MySQL.

Stored Procedures with no parameters are OK, but when I try to execute one with a parameter it is throwing an exceptions as follows :

ex      {"ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.34]
Incorrect number of arguments for PROCEDURE vehiclereg.getRegistrationDetails;
expected 1, got 0"}      System.Exception

I am using ODBC 3.51 - I understand that MySQL Connector Net 5.2.6 does not work with VWD Express.

The SP works correctly with a parameter when I use the console in MySQL :
mysql> CALL getRegistrationDetails('ABC');

Any help gratefully received as this is driving me crazy !

Ross

VB Code
 
 

        Dim myPar As OdbcParameter

        Dim cmd As New OdbcCommand()
 

        cmd.CommandType = CommandType.StoredProcedure

        cmd.CommandText = "{ CALL getRegistrationDetails}"

        cmd.Connection = con
 

        myPar = cmd.CreateParameter()

        myPar.ParameterName = "passRegLetters"

        myPar.Value = "ABC"

        myPar.Direction = ParameterDirection.Input
 

        cmd.Parameters.Add(myPar)
 

        Try

            Dim rdr As OdbcDataReader = cmd.ExecuteReader()

            Return rdr

        Catch ex As Exception

            Console.WriteLine("Error: " & ex.ToString())

        End Try
 
 

MySQL Stored Procedure
 
 

select * from regLetters 

	where letters = passRegLetters

Open in new window

0
Comment
Question by:arossco
  • 3
4 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 24383504
Seen somewhere on another forum, I don't know if it can help you.
There pass this as parameter
cmd.Parameters.Add("?channelID", "2").Direction = ParameterDirection.Input

So can you try one of the following:
1. myPar = cmd.CreateParameter()
        myPar.ParameterName = "?passRegLetters"
        myPar.Value = "ABC"
        myPar.Direction = ParameterDirection.Input
Or
cmd.Parameters.Add("?passRegLetters","ABC")
0
 

Author Comment

by:arossco
ID: 24383693
Thanks - I've tried this and I get exactly the same result.  !

Ross
0
 

Author Comment

by:arossco
ID: 24383703
.. To clarify, I still get :

ex      {"ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.34]
Incorrect number of arguments for PROCEDURE vehiclereg.getRegistrationDetails;
expected 1, got 0"}      System.Exception

Ross
0
 

Accepted Solution

by:
arossco earned 0 total points
ID: 24384409
Delighted to say that I have solved the problem !

I said in my initial post "I am using ODBC 3.51 - I understand that MySQL Connector Net 5.2.6 does not work with VWD Express" - I guess I was misinformed !

I retried the MySQL Connector which now works - I didn't realise before that I should have been using
MySqlConnection, MySqlCommand etc instead of SqlConnection and SqlCommand etc.

 Ross
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 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

21 Experts available now in Live!

Get 1:1 Help Now