very strange proble with access queyr and parameter

[Andrew]

hi,
here is my code :
szQry = "SELECT SubCategory_ID , " & _
" Loc1.Description as Descrizione " & _
" FROM C_SubCategories " & _
" LEFT OUTER JOIN D_Localizations Loc1 " & _
" ON ( C_SubCategories.Description_ID = Loc1.Localization_ID
AND Loc1.Language_ID = @LanguageID ) " & _
" WHERE Category_ID = @CategoryID " & _
" AND StateActive = 1 " & _
" ORDER BY SubCatOrder "

now i set the szqry to the command and i set the parameter ....
if set first the @languageID paramater and then the @CategoryID then command
returns always 0 record.
but if i change the order and put first the @CategoryID and the @languageID
all work great !!!! Why ?
Does Access convert my query ? It's a problem of the outer join ????
very strange .......

any suggestion?
tnx in advance
andrew

[Teemu Keiski]

Hi,

I assume you are using named parameters and you use OleDbConnection and
OleDbCommand in your code. Point is that in .NET they don't support named
parameters but parameters are specified based on their position on query.
Means you set first the value for the first parameter, then for the second
and so on e.g it is only by parameter's position. Note that for
System.Data.OleDb classes '?' (question mark) is the parameter placeholder
in queries

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist
http://blogs.aspadvice.com/joteke
"Andrew" <as*@for.it> wrote in message news:2p************@uni-berlin.de...

hi,
here is my code :
szQry = "SELECT SubCategory_ID , " & _
" Loc1.Description as Descrizione " & _
" FROM C_SubCategories " & _
" LEFT OUTER JOIN D_Localizations Loc1 " & _
" ON ( C_SubCategories.Description_ID = Loc1.Localization_ID AND Loc1.Language_ID = @LanguageID ) " & _
" WHERE Category_ID = @CategoryID " & _
" AND StateActive = 1 " & _
" ORDER BY SubCatOrder "

now i set the szqry to the command and i set the parameter ....
if set first the @languageID paramater and then the @CategoryID then command returns always 0 record.
but if i change the order and put first the @CategoryID and the @languageID all work great !!!! Why ?
Does Access convert my query ? It's a problem of the outer join ????
very strange .......

any suggestion?
tnx in advance
andrew

[Andrew]

here is the code ...
it's doesn't work ....!!!
if i change the order of the parameter it works

why ???

szQry = "SELECT SubCategory_ID , " & _
" Loc1.Description as Descrizione " & _
" FROM C_SubCategories " & _
" LEFT OUTER JOIN D_Localizations Loc1 " & _
" ON ( C_SubCategories.Description_ID = Loc1.Localization_ID
AND Loc1.Language_ID = @LanguageID ) " & _
" WHERE Category_ID = @CategoryID " & _
" AND StateActive = 1 " & _
" ORDER BY SubCatOrder "

' Create Instance of Connection and Command Object
Dim myConnection As New
OleDbConnection(HttpContext.Current.Application("C onnectionString"))
Dim myCommand As New OleDbDataAdapter(szQry, myConnection)

' Mark the Command as a Text
myCommand.SelectCommand.CommandType = CommandType.Text

' Add Parameters to QRY
Dim parameterLanguageID As New OleDbParameter("@LanguageID",
OleDbType.BigInt)
parameterLanguageID.Value = m_iLanguageID
myCommand.SelectCommand.Parameters.Add(parameterLa nguageID)

' Add Parameters to QRY
Dim parameterID As New OleDbParameter("@CategoryID",
OleDbType.BigInt)
parameterID.Value = iSectionID
myCommand.SelectCommand.Parameters.Add(parameterID )