Devart Forums

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.

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.

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.

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.

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.