How to get dataset from MySql Query using variables

Question

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?

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

 DataSet mydataset =  new DataSet();
 MySqlConnection myConnection = new MySqlConnection();
 myConnection.ConnectionString = "************";
 myConnection.Open();
 string mySelectQuery = "SELECT * FROM table";
 MySqlCommand myCommand = new MySqlCommand(mySelectQuery,myConnection);
 MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
 adapter.Fill(mydataset, "table");
 dataGridView1.DataSource = mydataset;
 dataGridView1.DataMember = "table";
 myConnection.Close();

You can have a look in the following links:

http://forums.codeguru.com/showthread.php?448008-How-do-i-load-mysql-data-into-a-dataset-then-into-a-datagrid

http://www.dotnetheaven.com/article/how-to-load-data-from-database-into-datagridview-in-vb.net

If my answer is correct then please masrk as correct. Thank you

I've adapted your code to vb.net (it seems to be C code) but it doesn't seem to accept variables into the SQL string. Please try an SQL string like the one in my question. — genespos, Apr 08 '16 at 13:09

score 0 · Accepted Answer · answered Apr 08 '16 at 16:21

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"

How to get dataset from MySql Query using variables

2 Answers2