very strange proble with access queyr and parameter

A

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
 
T

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
 
A

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("ConnectionString"))
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(parameterLanguageID)

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top