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;Technology News @ www.JassimRahma.com
-
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;Technology News @ www.JassimRahma.com
Split your country string into a temp table and use a join or (select country from newtable)
Never underestimate the power of human stupidity RAH
-
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;Technology News @ www.JassimRahma.com
After looking at your code, are you sure the value of the "param_country" is a quoted, comma separated list of values ? For example an IN clause would look like this: "select * from myTable where location in ('USA','CANADA','MEXICO') Check the value of the sql_command.Parameters("param_country") right after you populate it with values. BTW: There may be limitations to the number of values you can have in an "IN" clause. I remember from a while ago Oracle had a 1000 item limit, just something to think about. david