0

I would like to execute multiple query using C# and mysql, this is my code

MySqlConnectionStringBuilder connString = new MySqlConnectionStringBuilder();
            connString.Server = "10.16.106.77";
            connString.Database = "pln";
            connString.UserID = "root";
            connString.Password = "xxxxx";
            connString.IgnorePrepare = false;

            using(MySqlConnection conn = new MySqlConnection(connString.ToString())){
                string cmdText = @"SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(keterangan = ''',keterangan,''', talenta, NULL)) AS ', QUOTE(keterangan)) ORDER BY keterangan ASC) INTO @sql FROM `data`;
                                    SET @sql = CONCAT('SELECT nip, nama, jabatan,`tanggal jabatan definitif`, unit, jenjang_jabatan, grade_terakhir, tanggal_grade,', @sql, ' FROM data GROUP BY nip');
                                    PREPARE stmt FROM @sql;
                                    EXECUTE stmt";                    

                MySqlCommand cmd = new MySqlCommand(cmdText, conn);
                cmd.Connection.Open();
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        listBox1.Items.Add(reader.GetString("tanggal_grade"));
                    }
                }
            }

It throws an error :

enter image description here

@sql must be define.

enter image description here

Any help please ?

Arief Grando
  • 209
  • 2
  • 12
  • The Error Message says it all. You have to define the @sql parameter in the query. Like `INTO @sql FROM `data``, `SET @sql = CONCAT`, `PREPARE stmt FROM @sql` – Mohit S Oct 17 '16 at 02:03
  • But I have define it in SET @sql = ..... ?? How to define it in a proper way ??? – Arief Grando Oct 17 '16 at 02:06
  • When you put `@` sign in the sql statement it becomes the variable which ask for a value at the run time. So better you can take out the `@` sign from the query. I am not sure what and why you have inserted the `@` sign – Mohit S Oct 17 '16 at 02:08
  • Try not using the '@' symbol. E.g. `SET sql=...`, `INTO sql FROM data`, etc. Using that symbol usually denotes passing in an external variable. – Keyur PATEL Oct 17 '16 at 02:08
  • I have delete the @ symbol, now it turns error "SQL error syntax near 'sql from `data` " – Arief Grando Oct 17 '16 at 02:17
  • 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 'sql FROM data; SET sql = CONCAT('SELECT nip' at line 1 – Arief Grando Oct 17 '16 at 02:46
  • Thank You @Shadow, Drew fix it with connString.AllowUserVariables = true; without removing the @ symbol – Arief Grando Oct 17 '16 at 03:52

0 Answers0