CODEWITHSUNDEEP
sqlsql-server-2008
Joe Tyman
Joe Tyman

Reputation: 1417

Query not working

I have SQL statement:

SELECT        ASIN, Date, DateSoldActualized, FNSKU, ItemName, MaxShipmentDateAllSkus,    MaxShipmentDateThisSKU, MerchantID, MerchantSKU, SoldIn30Days, TotalQty
FROM            AmazonSKUs
WHERE        (MerchantSKU LIKE @MerchantSKU)

I have a large database with 20,000 records. 72 of those record have a MerchantSKU that start with MEA_. When I search by MEA_% I get nothing. I have roughly 100 records that start withe ACD_. When I query by ACD_% I get 10 records Whether I used my program I am writing to display this data or run the query against my db directly.

To execute this I call a function from my GUI:

s.createdisplay(txtSellerSku.Text.Trim()+"_%");

which in turns calls this function which build a couple of List<string>'s:

inventory.AmazonSKUsDataTable builder = i.GetbySKU(sellersku);

which call this function from my bll which actually runs my query:

    [System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
    public inventory.AmazonSKUsDataTable GetbySKU(string SKU)
    {
        return Adatper.GetbySKU(SKU);
    }

So here is my question why does that query not work?

Edit: Here are a couple rows of my data

MerchantID MerchantSKU Date TotalQty SoldIn30Days ASIN SELECT ASIN, Date, DateSoldActualized FNSKU ItemName MaxShipmentDateAllSkus MaxShipmentDateThisSKU ItemName Rank Price Amazon

A1B7M9EQGNCLQA MEA_89930_C6.39 6/3/2011 6:35:07 PM 47 6/3/2011 6:35:07 PM 0 B0020OWVDS 6/3/2011 6:35:07 PM 6/3/2011 6:35:07 PM X0006J5C9F NULL NULL Medela TheraShells Breast Shells #89930 [Baby Product] 8,902 Baby 24.99 F

A1B7M9EQGNCLQA MEA_89973_C2.60 5/10/2011 12:00:00 AM 69 5/15/2011 12:00:00 AM 37 B00006FWVO 5/15/2011 12:00:00 AM 5/8/2011 12:00:00 AM B00006FWVO NULL NULL Medela Disposable Nursing Bra Pads - 30-pk [Baby Product] 2,603 Baby 9.07 F

A1B7M9EQGNCLQA MEA_89974 5/10/2011 12:00:00 AM 3 5/15/2011 12:00:00 AM 52 B00006FWVR 5/15/2011 12:00:00 AM 5/2/2011 12:00:00 AM B00006FWVR NULL NULL Medela Disposable Nursing Bra Pads - 60-pk. [Baby Product] 734 Baby 12.01 F

Upvotes: 2

Views: 145

Answers (3)

marc_s
marc_s

Reputation: 754398

The _ character is the "replace by one arbitrary character" wildcard in SQL - much like ? in DOS/Windows.

Try to do a .. WHERE MerchantSKU LIKE 'MEA%' - do you get anything back now?

If you must use the underscore, try putting it into square brackets:

.. WHERE MerchantSKU LIKE 'MEA[_]%'

Update: I cannot reproduce your problems - at least not on the SQL Server level - try this yourself:

DECLARE @merchant TABLE (MerchantID VARCHAR(50), MerchantSKU VARCHAR(50), TotalQty INT)

INSERT INTO @merchant
VALUES('A1B7M9EQGNCLQA', 'MEA_89930_C6.39', 47),
('A1B7M9EQGNCLQA', 'MEA_89973_C2.60', 69),
('A1B7M9EQGNCLQA', 'MEA_89974', 3),
('X1B7M9EQGNCLQA', 'MEB_89930_C6.39', 4711),
('X1B7M9EQGNCLQA', 'MEF_89930_C6.39', 42),
('X1B7M9EQGNCLQA', 'MEZ_89930_C6.39', 7)

SELECT *
FROM @merchant
WHERE MerchantSKU LIKE 'MEA[_]%'

According to my testing, the proper rows are all found and returned. I suspect there's something in the code that actually calls your SQL query that's "fishy" and causes these problems - the SQL statement as such is valid and works.

Upvotes: 5

Pankaj
Pankaj

Reputation: 10095

You can use parameterized queries like below structure. so no need to enclose underscore with square brackets or no need to join single quote with quote.

using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) { 
    con.Open(); 
    SqlCommand cmd = new SqlCommand(); 
    string expression = "Parameter value"; 
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.CommandText = "Your Stored Procedure"; 
    cmd.Parameters.Add("Your Parameter Name", 
                SqlDbType.VarChar).Value = expression;    
    cmd.Connection = con; 
    using (IDataReader dr = cmd.ExecuteReader()) 
    { 
        if (dr.Read()) 
        { 
        } 
    } 
}

Upvotes: 0

ibram
ibram

Reputation: 4569

'_' in LIKE comparison means in sql any single character. this could be the problem.

you could use it with escape:

where myvalue like 'MEA[_]%'

Look here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

Upvotes: 2

Related Questions