How to pass varchar as WHERE field IN param
Hi,
I am trying to pass a varchar string from C# to MySQL stored procedure to be used in WHERE xx IN (param). I tried it in the basic way below but it's not working and giving no result.
can any one help please.
this is my C#:
sql_connection = new MySqlConnection(serverClass.connectionstring("BlueFile", "BlueFile", "Server"));
sql_connection.Open();
sql_command = new MySqlCommand("sp_populate_memo_country_companies", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.AddWithValue("param_country", Convert.ToString(cboToCountry.EditValue)).MySqlDbType = MySqlDbType.VarChar;
// Create data adapter object
sql_adapter = new MySqlDataAdapter();
sql_adapter.SelectCommand = sql_command;
// Create a dataset object and fill with data using data adapter's Fill method
data_set = new DataSet();
sql_adapter.Fill(data_set, "companies");
DataViewManager dataview_manager = new DataViewManager(data_set);
DataView main_dataview = dataview_manager.CreateDataView(data_set.Tables["companies"]);
cboToCompany.Properties.ValueMember = "location_id";
cboToCompany.Properties.DisplayMember = "company_name";
cboToCompany.Properties.DataSource = main_dataview;
DevExpress.XtraEditors.Controls.LookUpColumnInfo("department_name", 50, "department_name"));
cboToCompany.Properties.PopupSizeable = false;
cboToCompany.EditValue = null;
and this is my stored procedure:
SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
FROM locations
JOIN country ON country.country_code_alpha2 = locations.country_code
JOIN companies ON companies.company_id = locations.company_id
LEFT JOIN payroll ON payroll.location_id = locations.location_id
WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
GROUP BY locations.location_id
ORDER BY companies.company_name;
Thanks,
Jassim