OLE DB Text Parameter syntax

[sharonniles]

I'm using Sybase as a datasource and connecting through OLEDB. SSRS (Visual Studio 2008 R2) works fine. My report is coming up blank with either of these parameters' syntax. Any ideas are welcome. Thank you.

doesn't work:
WHERE CCCE.CODE_ID =('?')

WHERE CCCE.CODE_ID =('" & ? & "')

works:
WHERE CCCE.CODE_ID =('123456')

 

[kjv1611]

When you say doesn't work, are you saying it gives no results, or gives an error. Sounds like you're saying you're getting no results. That sounds to me like you're not looking for the right thing.

Also, the one that does work - is it a varchar/string/text field, or is it a numeric field? If numeric field, you are doing your database engine a disservice by putting quotes around the numeric value - it'll affect performance. And if that is a numeric field, why would you ever expect it to contain a question mark or ampersands and question mark?

Maybe those standards work on Sybase, but not on MS SQL in Reporting Services. You'll need to make sure where the query is taking place, and code to that.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[sharonniles]

Hi Kiv,

Thanks for getting back to me. The variable I'm seeking is a text field. In Sybase, it's unicode.

Let's say the field is called CCCE.Code_ID (text field)

The parameter is @Code_ID (text also)

My query is
SELECT CCCE.*
FROM CCCE
WHERE CCCE.Code_ID = (@Code_ID) syntax error
WHERE CCCE.Code_ID = ('@Code_ID') no results
WHERE CCCE.Code_ID = ('+@Code_ID+') no results
WHERE CCCE.Code_ID = (+CHR(39)+Parameters!Code_ID.Value+CHR(39)+) no results
WHERE CCCE.Code_ID = ('123456') record is returned

Any help is appreciated.

 

[kjv1611]

You know... Your first try at a WHERE.. why do you have parentheses at all? You should just need:

WHERE CCCE.Code_ID = @Code_ID

Or if that doesn't work, and you have to do something else, here's a crazy idea that should also work:

WHERE CCCE.Code_ID = CAST(@Code_ID AS varchar)

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

And of course, I'm at the moment thinking strictly SQL. I forget that sometimes the text parameters work differently in Reporting Services / Report Builder. So if that doesn't work, then keep digging - I'm sure there's a simple answer/solution somewhere, it's not like that's a super complex WHERE clause.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57