Characters added to my parameter in stored procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
JTCOO
Posts: 3
Joined: Wed 28 Jan 2009 22:01

Characters added to my parameter in stored procedure

Post by JTCOO » Wed 28 Jan 2009 22:40

I'm using the Express edition, version number 5.0.20.0.

I created a stored procedure like this:
CREATE DEFINER=`root`@`%` PROCEDURE `udpGetAddressRelatedData`(TheStreet varchar(999))
BEGIN
-- Create an alias for the column names returned because they are used for header text in the application
select n.NeighborhoodName as Neighborhood, c.DistrictID as 'District ID', c.Commissioner from Commissioners as c, Neighborhoods as n, AddrData_by_LSO_2 as a where Street = TheStreet and c.DistrictID = a.DistrictID and n.NeighborhoodID = a.NeighborhoodID;

END

I created a parameter like this:
... new MySqlParameter("@Street", MySqlType.VarChar, 81);
and assigned the value "1423 CATHERINE ST".

I added the parameter to the DataAdapter.SelectCommand.Parameters collection. I then called
...
NumRecords = this.DataAdapter.Fill(this.LocalDataTable);
...

This creates an exception with the text:

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 ':'1423 CATHERINE ST')' at line 1

I can't tell exactly what is being highlighted and I don't know where the colon and closing parenthesis are coming from. I have tried running the stored procedure from the basic query browser from MySQL and I don't get an error, it just returns nothing. Does anyone know what is happening here?

Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 30 Jan 2009 15:48

Try using "...new MySqlParameter("Street", MySqlType.VarChar, 81);" instead of "...new MySqlParameter("@Street", MySqlType.VarChar, 81);". Please notify us if you have resolved the problem.

JTCOO
Posts: 3
Joined: Wed 28 Jan 2009 22:01

To parm or not to parm, @ is the question.

Post by JTCOO » Tue 03 Feb 2009 19:34

Thanks for your response. I see that I have a discrepancy between how it's defined, and how it's used, but rather than removing the @, is it possible to define the parameter in the stored procedure with the @ character? I'd really like to make it so that the same query can work in a stored procedure and as a query in my application code.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 05 Feb 2009 15:36

You can set the name of the parameter with @, if this parameter (with @) is used in the CommandText property for CommandType.Text. As for CommandType.StoredProcedure, you may not to set its name at all. It is not allowed to declare its name with @ in this case. To know more how to use parameters please read our online documentation: http://www.devart.com/dotconnect/mysql/docs/ , the Using dotConnect for MySQL part, the Using Parameters section.

JTCOO
Posts: 3
Joined: Wed 28 Jan 2009 22:01

Removing @ worked

Post by JTCOO » Thu 05 Feb 2009 15:52

This is a little frustrating, because the SQL will have to change if changing between stored procedures and inline queries, but at least I can get it to work.

Post Reply