SELECT QUERY ISSUE

I THERE,
I never came across this weird issue...
I have a product table ProdMaster with a field ProdCode Nvarchar (50), ProdCode are all of different formats like numeric or alphanumeric and of different lenghts.

I recently create through my web interface few products which are showing properly on my page gridview, but when I tried the following select statement directly on the server (Select ProdCode From ProdMaster Where ProdCode ='CFF40179') the ProdCode return blank????
I tried with the "like"word or by using LTRIM/RTRIM but same result.

I could only solved the problem after updating the ProdCode with the same value, and then the select statement returns the correct value.

My concern is to understand what caused this problem, because if I create new products in the future and if they don't show properly in select statement from stored procedure I'l  be in trouble.

Expert advice is required...
pascalmartinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
It is possible that from your front end you are inserting a control character.

If you can recreate the issue, then run a select, perhaps like so:

SELECT ProdCode,
       ASC(LEFT(ProdCode, 1)) AS Ascii1,
       ASC(RIGHT(ProdCode, 1)) AS Ascii2
FROM ProdMaster
WHERE ProdCode LIKE '%CFF40179%') -- make sure ProdCode = 'CFF40179' returns nothing

... just to see if there are any special ascii codes on the front or end. I've experienced a tab (ASC 9) in data a time or two, especially from a front end app that didn't handle tabbing to the next field. I've also hit an ASC 0 a time or two.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongCommented:
>>when I tried the following select statement directly on the server
What tool are you using to execute the select statement? Do you have a screenshot of it?

it's weird since you have a condition of  Where ProdCode ='CFF40179'
0
dsackerContract ERP Admin/ConsultantCommented:
On my query, please remove the last parenthesis. That was a typo. Also I quickly code ASC instead of ASCII. Here is a corrected version:

SELECT ProdCode,
       ASCII(LEFT(ProdCode, 1)) AS Ascii1,
       ASCII(RIGHT(ProdCode, 1)) AS Ascii2
FROM ProdMaster
WHERE ProdCode LIKE '%CFF40179%' -- make sure ProdCode = 'CFF40179' returns nothing

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post a screen shot of what's happening?
0
pascalmartinAuthor Commented:
Hi Thanks for the feedback, I actually can't reproduce the error as I have updated the ProdCode and now I can do my select statement.

As for the screenshot, it is only a query that return noting on Server Management Studio query pane.
0
dsackerContract ERP Admin/ConsultantCommented:
If you're no longer having the problem, it may have been a control character mixed in the field value.
0
pascalmartinAuthor Commented:
Right, but it is the first time it happens in more than 10 years I am using SQL server....
I worry that it happens again when I create a new product.
0
dsackerContract ERP Admin/ConsultantCommented:
If you ever can recreate the problem again, first use my select query (above) to see if there are any ascii characters at the beginning or end. Don't update the table until you data-mine/analyze it next time. :)
0
pascalmartinAuthor Commented:
I've requested that this question be deleted for the following reason:

No solution given.
0
dsackerContract ERP Admin/ConsultantCommented:
A solution was given, specifically, how to search the field for characters that may have caused this.
0
pascalmartinAuthor Commented:
A solution was given but as I said I couldn't recreate the error because I retyped the Product codes that was not returning properly in my query; therefore I was not able to try the solution given and confirm that it was right.

I can wait and see if the problem occurs again and if the solution is proven to be the right one I have no problem to accept it.
0
pascalmartinAuthor Commented:
Hi Again,

The problem occurs again this morning with another ProdCode :  8000001 (7 digits)

If I apply dsaker solution, here his what the query returns:

ProdCode      Ascii1      Ascii2
80000011      56              49
80000013      56              51
80000014      56              52
80000015      56              53
80000018      56              56
80000017      56              55

It found all other prodcodes which have a digit after the "1" and are in 8 digits fromat
0
Ryan ChongCommented:
>>The problem occurs again this morning with another ProdCode :  8000001 (7 digits)

what's the sql statement you run to get above results (for comment: ID: 40850035) ?

is that mean you will get 6 records returned if you run this query?

>>SELECT ProdCode,
       ASCII(LEFT(ProdCode, 1)) AS Ascii1,
       ASCII(RIGHT(ProdCode, 1)) AS Ascii2
FROM ProdMaster
WHERE ProdCode = '8000001'
0
pascalmartinAuthor Commented:
The SQL statement that returns the 6 records is :

SELECT ProdCode,
        ASCII(LEFT(ProdCode, 1)) AS Ascii1,
        ASCII(RIGHT(ProdCode, 1)) AS Ascii2
 FROM ProdMaster
 WHERE ProdCode = '%8000001%'

If I remove the % signs then it returns nothing
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post your original query that returns nothing?
0
pascalmartinAuthor Commented:
SELECT ProdCode FROM ProdMaster  WHERE ProdCode = '8000001'
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Wondering why you want to return the ProdCode if you already have it in the WHERE clause.
Anyway, that is an exact search ('=') so by the above posts you don't have any ProdCode = '8000001' .

You may want to change it a little bit:
SELECT ProdCode FROM ProdMaster  WHERE ProdCode LIKE '8000001%' 

Open in new window

0
pascalmartinAuthor Commented:
If you look at my initial post I already mentioned that the query returns nothing either with "like" or "=" operator.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
But the LIKE needs "%". Did you mentioned that also?
0
pascalmartinAuthor Commented:
No I did not.
It means that every time I create a query with a Nvarchar parameter value with the "Like" operator I need to insert "%" before and after the value?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
'%' is a wildcard. You don't need to insert it before and after but only in the places that you want to be replaced by 'any character or characters'. In your case I just used in the last because I think you want everything started by '8000001'.

But you could by instance want something like ''8%1'  (beginning with 8 and ending with 1) - This is only an example, of course.

So '=' is used for exact words and LIKE is used to find records with wildcards.
0
pascalmartinAuthor Commented:
Ok I et your point.
Initially I wanted to return the prodcode '8000001' nothing else, but the query returned nothing whether I used "="or wildcard. The record was there and I still can't understand why my query didn't work.

Using the wild card just enabled to return other records starting with '8000001' and ending with another digit.

There is still no explanation why the query returns nothing....
0
Ryan ChongCommented:
1. Do you have records where ProdCode equals to 8000001 (7 digits)? From your last comment, you said Yes?
2. or Is that all the ProdCode are standardized and saved with 8 digit characters? >>No?

To conclude this, is that mean you have a record with ProdCode equals to 8000001 (7 digits) and when you using query below, it returns nothing?

SELECT ProdCode FROM ProdMaster  WHERE ProdCode = '8000001'

3+ what's the data type of the field ProdCode? Make sure you not saving extra chars into the field..

4. Can you do a length count for ProdCode? like:

SELECT ProdCode, len(ProdCode) l,
        ASCII(LEFT(ProdCode, 1)) AS Ascii1,
        ASCII(RIGHT(ProdCode, 1)) AS Ascii2
 FROM ProdMaster
 WHERE ProdCode = '%8000001%'

will this give you extra clues to solve your problem?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you didn't get anything returned by SELECT ProdCode FROM ProdMaster  WHERE ProdCode = '8000001' that only means that a record with ProdCode = '8000001' doesn't exist. What you want to do when nothing is returned?
0
pascalmartinAuthor Commented:
NO, the record exists because if I query SELECT * FROM ProdMaster  all records in cluding the one with ProdCode  '8000001'  return... that's why there is something very strange.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry but that's hard to believe. There's must be something else.
Can you post a screenshot where that record exists?
0
pascalmartinAuthor Commented:
Seems to be the answer to the problem even if I can't verify.
0
dsackerContract ERP Admin/ConsultantCommented:
Thanks, but your difficulty at verifying should not be taken out on me with a grade reduction to B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.