asked on 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 ) ) )
C#Microsoft SQL Server 2008SQL
Last Comment
r3nder
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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() + '"
ASKER
SQL injection is not an issue - this is all internal - it will always be a string and I will be searching all of the fields
ASKER
Thank you ewangoya! worked like a champ
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.