mysqlvb.netmysql-helper

How to get dataset from MySql Query using variables

I have a query like this:

SET @a = (SELECT GROUP_CONCAT(Id) FROM MyTable1 WHERE Id < 10);
SELECT * FROM MyTable2  WHERE find_in_set(IdLite, @a); 
SELECT * FROM MyTable3  WHERE find_in_set(IdLite, @a);
SELECT * FROM MyTable4  WHERE find_in_set(IdLite, @a);

I've tryed to use this code to get resut:

Using ds As DataSet = MySqlHelper.ExecuteDataset(CnStr, SqlStr)

but I get error:

Fatal error encountered during command execution.

Error message is:

Parameter '@a' must be defined.

I've also tryed:

SELECT * FROM MyTable2  WHERE find_in_set(IdLite, 
     @a := (SELECT GROUP_CONCAT(Id) FROM MyTable1 WHERE Id < 10)); 
SELECT * FROM MyTable3  WHERE find_in_set(IdLite, @a);
SELECT * FROM MyTable4  WHERE find_in_set(IdLite, @a);

but I get the same error.
What's the correct way to get result into a DataSet?

Solution

  • The error is in the connection string.

    The solution is to add ;Allow User Variables=True to the database name.

    This way:

    CnStr = "datasource=" + Server_Name + _
";username= " + UserDB + _
";password=" + Password +  _
";database=" + Database_Name + ";Allow User Variables=True"