I have a sql query - somewhere in here I have to add a search feature - i have a text box I call txtSearch - i want to pass this string value into this and have it return those results from this query can anyone help please
SELECT DISTINCT qci.id AS qcuid, t.uid AS taskuid, t.polinekey, cf.factoryuid, Cast(Cast(ponumber AS INT) AS VARCHAR) AS ponumber, Upper(Isnull(custid, '')) AS custid, Upper(COALESCE(NULLIF(custname, ''), shiptoname)) AS custname, Isnull(t.selectedtask, 1) AS selectedtask, CASE WHEN t.qctestsampleuid > 0 THEN (SELECT Upper(COALESCE(masitemno, uniqueid)) FROM ideas WHERE ideauid = (SELECT ideauid FROM task WHERE uid = st.parentuid)) ELSE Upper(itemid) END AS itemid, CASE WHEN t.qctestsampleuid > 0 THEN (SELECT Upper(itemdesc) FROM ideas WHERE ideauid = (SELECT ideauid FROM task WHERE uid = st.parentuid)) ELSE Upper(vpo.description) END AS itemdescription, Cast(COALESCE(rh.taskuid, 0) AS BIT) AS Uploaded, dbo.Kkgetallowsubmitbypass(t.uid) AS enableBypass, Cast(COALESCE(t.resultsid, 0) AS BIT) AS uploadcompeted, t.resultsid, CASE WHEN t.qctestsampleuid > 0 THEN Upper( COALESCE (st.designer, '')) ELSE Upper(factory) END AS factory, companyid, NULLIF (promisedate, '01/01/1900') AS POShipdate, vpo.polinekey, vpo.pokey, purchamt, qtyleft, sumqtytoload, sumqtyloaded, qtyord, qtyopentorcv, companyid, qci.id AS qcInspectionUID, qci.customeritem, qci.customerpo, qci.custbatchdatecode, CASE Rtrim(Ltrim(qci.batchcode)) WHEN '' THEN Cast (Cast(vpo.ponumber AS INT) AS VARCHAR) + '/' + vpo.itemid + '/' + Cast (Datepart(wk, vpo.promisedate) AS VARCHAR) ELSE Rtrim(Ltrim(qci.batchcode)) END AS batchcode, qci.label_needsilversticker, qci.label_needbranding, qci.label_needcarbsticker, qci.label_needfabriclabel, qci.label_needenginewarning, t.designer AS Inspectorname, t.comments AS inspectorinstructions, t.designeruid AS inspectorUID, t.daterequested, NULLIF (t.datetobecomplete, '01/01/1900') AS inspectiondate, dbo.Kktaskdisablewithoutsignoff(t.polinekey, t.parentuid, t.datetobecomplete) AS TaskDisableWithoutSignOff, qci.label_needflyer, NULLIF (t.completeddate, '01/01/1900') AS completeddate, NULLIF (t.downloaddate, '01/01/1900') AS DownloadDate, t.successfuldownload, t.isxmlreleased, Isnull(uc.elevateflag, 0) AS elevateflag, Isnull(uc.commentuid, 0) AS commentuid, Isnull((SELECT 1 FROM task WHERE uid = ANY (SELECT TOP 1 uid FROM task (nolock) t2 INNER JOIN historytaskstatus AS hts (nolock) ON hts.taskuid = t2.uid INNER JOIN designertaskstatus dts (nolock) ON dts.designerstatusuid = hts.taskstatusuid WHERE t2.uid = t.uid AND closetask <> 0 AND wascloned = 0)), 0) AS cloneneeded, Cast(CASE WHEN qci.xpsreport IS NULL THEN 0 ELSE 1 END AS BIT) AS uploadcompeted FROM task (nolock) t INNER JOIN qcinspection (nolock) qci ON qci.taskuid = t.uid LEFT JOIN qcfinalinspection (nolock) qfi ON qfi.qcinspectionid = qci.id LEFT JOIN vpoalldetailswithcust (nolock) vpo ON t.polinekey = vpo.polinekey LEFT JOIN sampledetail (nolock)sd ON sd.sampleuid = t.qctestsampleuid LEFT JOIN task (nolock)st ON st.uid = sd.taskuid LEFT JOIN pofactory (nolock) pof ON vpo.polinekey = pof.polinekey LEFT JOIN factoryinfo (nolock) cf ON pof.factoryuid = cf.factoryuid LEFT JOIN vfactorymgr (nolock) fm ON cf.factoryuid = fm.factoryuid LEFT JOIN [resultshistory] rh ON rh.taskuid = t.uid AND rh.notes = 'Inspection uploaded' LEFT JOIN usercomments (nolock) AS uc ON uc.commentuid = (SELECT TOP 1 commentuid FROM usercomments WHERE usercomments.typeuid = t.uid AND type = 35 AND Isnull(usercomments.commentuid, 0) <> 0 AND ( private = 0 OR ( private <> 0 AND userid = 406 ) ) AND Isnull(activecmt, 0) > 0 ORDER BY elevateflag DESC) WHERE Isnull(t.uid, 0) > 0 AND t.tasktype = 35 AND ( ( Isdate(t.completeddate) = 1 AND ( Datediff(dd, '01/01/1900', t.datetobecomplete) > 0 ) ) OR ( ( Isdate(t.completeddate) = 0 OR ( Datediff(dd, '01/01/1900', t.datetobecomplete) = 0 ) ) AND ( vpo.polstatus = 2 OR vpo.postatus IN ( 2, 3, 4 ) OR t.qctestsampleuid > 0 ) ) )
What would you be expected to put into this textbox? Would it always be applied to the same field to filter on?
For example, would it always be ponumber where the user just enters a PO number?
Is there any concern about SQL injection?
Although, not the most efficient, the easiest way given that this is a large query would be to populate a DataTable with this query. Create a DataView with a source of the datatable and setting your output control's ControlSource as the DV. Then use the Filter property of the DV to apply the filter, and the control (I assume a DataGridView) will automatically be updated.
Ephraim Wangoya
if you have the select statement in a string variable, it would be of the sort
For example, would it always be ponumber where the user just enters a PO number?
Is there any concern about SQL injection?
Although, not the most efficient, the easiest way given that this is a large query would be to populate a DataTable with this query. Create a DataView with a source of the datatable and setting your output control's ControlSource as the DV. Then use the Filter property of the DV to apply the filter, and the control (I assume a DataGridView) will automatically be updated.