0

I'm trying to create an application with CheckBoxComboBox. So the user can select more than 1 item in ComboBox.

I followed this article to create this custom component for the CheckBoxComboBox tool.

CheckBox ComboBox Extending the ComboBox Class and Its Items

I have no problem with connecting the Datatable from CheckBoxComboBox.

My problem is when I select multiple items in ComboBox I'm getting a conversion error.

Conversion failed when converting the nvarchar value '415073,415072' to data type int.

But when I select 1 item and using the same query it will show the data to DataGridView.

This is my SQL Query

SELECT TOP 500 npt.ID, l.name, nt.taskname,  FORMAT(npt.reportingDate, 'MMM-dd-yyy') AS [PHT Reporting Date],  FORMAT(npt.estReportingDate, 'MMM-dd-yyy') AS [EST Reporting Date], 
npt.remarks, FORMAT(npt.startDate, 'h:mm tt') AS [PHT Start Date], FORMAT(npt.endDate, 'h:mm tt') AS [PHT End Date], FORMAT(npt.estStartDate, 'h:mm tt') AS [EST Start Date], 
FORMAT(npt.estEndDate, 'h:mm tt') AS [EST End Date], npt.duration, npt.estDuration, npt.status
FROM tbl_DT_NonProcessingTime npt
    INNER JOIN tbl_DT_NPTTask nt ON nt.ID = npt.taskID
    INNER JOIN tbl_DT_Login l ON l.workdayID = npt.workdayID
WHERE npt.workdayID IN (@workdayIDList)
    ORDER BY npt.id DESC

And this is my code to get the value of ComboBox and remove the unwanted characters and insert to a variable string.

 string split = Regex.Replace(checkBoxComboBox1.Text, "[A-Za-z ()ñ.+-]", "");
 objNPTBEL.workdayidlist = "'" + split.Replace(",", "','") + "'";

eg. '415073','415072'

After I got the variable I will pass that to the parameter and call the stored procedure.

enter image description here

I already tried this but I'm still getting conversion errors.

  • I'm about 90% sure that you can't pass a comma separated list like you have as a sql parameter. Dapper includes magic to do this, but I don't think you can do this the way you are trying. – Flydog57 Nov 11 '21 at 17:13
  • `npt.workdayID IN (@workdayIDList)` This does not do what you think or expect. Erland discusses [this topic](https://www.sommarskog.se/arrays-in-sql.html) in great detail. The link you "tried" also shows you how to do this using a TVP. But you simply cannot use a scalar string variable (i.e., parameter) as a replacement for a hard-coded list of CSV values contained in an IN clause. – SMor Nov 11 '21 at 18:16

0 Answers0