Solved

SQL search query

Posted on 2013-01-04
5
334 Views
Last Modified: 2013-01-04
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 ) ) ) 

Open in new window

0
Comment
Question by:r3nder
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 38744725
just add the field at the end of your select staement

........
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 ) ) )
AND YourSearchField = YourSearchText
0
 
LVL 13

Expert Comment

by:MrBullwinkle
ID: 38744729
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.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 38744732
if you have the select statement in a string variable, it would be of the sort

SQLText = SQLText + " AND YourSearchField = '" + txtSearch.ToString() + '"
0
 
LVL 6

Author Comment

by:r3nder
ID: 38744743
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
0
 
LVL 6

Author Closing Comment

by:r3nder
ID: 38744759
Thank you ewangoya! worked like a champ
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
What .NET website keeps me current? 9 32
Sql query for filter 12 22
Mssql SQL query 14 28
Delete Trigger in SQL Server2008R2 5 12
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now