SQL search query
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 ) ) )
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.
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.
if you have the select statement in a string variable, it would be of the sort
SQLText = SQLText + " AND YourSearchField = '" + txtSearch.ToString() + '"
SQLText = SQLText + " AND YourSearchField = '" + txtSearch.ToString() + '"
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.