Query is successfully executed in SQL Server but not in .NET

Question

I wrote a query which is working fine in both .NET app and SQL Server.

But, when I was testing with wide parameters, I found that for that particular, it is not showing anything in .NET app but showing result in SQL Server.

I tried to google, no results with little strange. So, I am asking here.

This is my query:

 SELECT DISTINCT 
        tblCustomers.customerID AS Customer#, 
        tblCustomers.firstName + ' ' + tblCustomers.surname AS Name, 
        tblCustomers.street AS Street, 
        tblCustomers.suburb AS Suburb, 
        tblCustomers.postCode AS Postcode, 
        tblCustomers.state AS State, 
        tblCustomers.country AS Country, 
        tblCustomers.phone AS [Phone No.], 
        tblCustomers.fax AS Fax, 
        tblCustomers.mobilePhone AS [Mobile Phone], 
        tblCustomers.email AS [E-mail]
  FROM    
        tblCustomers 
  INNER JOIN
        tblProduct_Backorder ON tblCustomers.customerID = tblProduct_Backorder.customerId
  WHERE     
        (tblCustomers.customerID IN
              (SELECT     
                   customerId
                FROM          
                    tblProduct_Backorder AS tblProduct_Backorder_1
                WHERE      
                    (productId IN
                            (SELECT     
                                 productID
                             FROM 
                                 tblProducts
                             WHERE      
                                (skuCode = 76761)
                    )
                 )
           )
       )

This query is not working for skuCode = 76761, but this one working fine in SQL Server.

Thanks.

We don't have enough information here to really answer. Are you connecting to a different database? Did you actually execute the query? What code are you using? Blah blah — lc.
– lc., Commented Feb 12, 2013 at 2:09
Ok, My apologize for it. I simply connected my Gridview with SQL Data Source, and I mentioned parametrized query in itself. So, I didn't write any piece code to connect database. — Hakoo Desai
– Hakoo Desai, Commented Feb 12, 2013 at 2:20
Either your ASP.net code is bad or your connection string is wrong. Coincidentally, you have shown us neither of those things. — RBarryYoung
– RBarryYoung, Commented Feb 12, 2013 at 3:30

Sebastian Meine · Accepted Answer · 2013-02-12 02:20:19Z

1

You have really not enough information in you question for us to even start guessing what caused the problem. In the mean-time try this instead of your query:

SELECT c.customerID AS [Customer#],
        c.firstName + ' ' + tblCustomers.surname AS Name,
        c.street AS Street,
        c.suburb AS Suburb,
        c.postCode AS Postcode,
        c.state AS State,
        c.country AS Country,
        c.phone AS [Phone No.],
        c.fax AS Fax,
        c.mobilePhone AS [Mobile Phone],
        c.email AS [E-mail]
 FROM   dbo.tblCustomers c
 WHERE  EXISTS ( SELECT 1
                 FROM   dbo.tblProduct_Backorder b
                 JOIN   dbo.tblProduct p
                        ON b.productId = p.productId
                 WHERE  p.skuCode = 76761
                        AND b.customerId = c.customerId );

If I understand your table relationships correctly, it will produce the same result while doing a lot less work.

For your original question you should also post the .net code. Also, what does "no results" mean? An empty result? A timeout? An error?

answered Feb 12, 2013 at 2:20

Sebastian Meine

11.9k1 gold badge34 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hakoo Desai Over a year ago

I tried to provide information, as much as I can. and I'll try this query. Thanks, for your help.

Hakoo Desai Over a year ago

I tried this one, and giving same result. Can you tell me how this query work? Now, I'll try in .net app.

Hakoo Desai Over a year ago

I tried your query in Query Builder in asp.net and found some strange behaviour. When I am substituting direct value ( say skuCode = 76761), it is working fine for both query, but when trying with parameter (say skuCode = @s and @s=76761), that time your query returning result with NULL values and my query return no result.

xQbert Over a year ago

@s isn't defined as a string is it?

Hakoo Desai Over a year ago

@xQbert : I don't know as all this care taken by .net.

Hakoo Desai · Accepted Answer · 2013-02-15 01:49:23Z

0

I found solution by help of xQbert's Comment. I took time and and saw how query is executed in SQL Profiler. skuCode in Table tblProduts is nvarchar(6) and in Table tblProduct_BackOrder its Integer. So, I converted into Integer for same Parameter using Convert() function.

answered Feb 15, 2013 at 1:49

Hakoo Desai

3413 gold badges4 silver badges22 bronze badges

Collectives™ on Stack Overflow

Query is successfully executed in SQL Server but not in .NET

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related