Parameters in Subqueries - Visual C# 2008 EE

[chipboard]

Hi all,

I am writing a SQL query in the TableAdapter Query Configuration Wizard and it includes a Subquery.

I have some parameters in my main query, and would like to have a parameter in the Subquery also. My query works up until the point where I add a parameter to the Subquery, when it stops returning any data. It returns data when I hard-code a value in place of the Subquery's parameter, so the rest of the query must be working fine. It just doesn't like the parameter in the Subquery.

Am I missing something here? Is it even possible to have parameterised Subqueries in this version of SQL?

FYI I am querying a .mdb Access 2000 database if that makes any difference.

Thanks for any help,

 

[jmeckley]

code? sql? without it we can't help.

there isn't anything fancy about sub-queries. that said maybe Access has some quirks since it doesn't abide by TSQL standards.

from ADO.Net a parameter is just a parameter and sql is just a string the command has no way of knowing where or how it's used. that's the database's job. so you should be able to do this

command.CommandText = @"
select *
from   table1
where  column1 = @foo
 and   column2 in (
               select column3
               from   table2 
               where  column4 = @bar
        )";

var foo = command.CreateParameter("foo");
foo.Value = something;
command.Parameters.Add(foo);

var bar = command.CreateParameter("bar");
bar.Value = something else;
command.Parameters.Add(bar);

I would also recommend migrating away from Access as the database. it's lacking in features: transactions being #1. you can use SqlExpress, SqlLite, Firebird, etc. All of which can be used free of charge.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[chipboard]

Thanks for the reply Jason - code is as follows:

SELECT     

Table1.Field1, 
Table1.Field2,
Table2Derived.Field3
FROM         

(SELECT
Table2.Field1,
SUM(Table2.Field4) AS Field3
FROM
Table2
WHERE
Table2.Field6 IN (?,?,?,?)
GROUP BY Table2.Field1)

Table2Derived
INNER JOIN Table1 ON Table1.Field5 = Table2Derived.Field1
WHERE     (Table1.Field1 = ?) 
AND (Table1.Field2 LIKE ?)
ORDER BY Table2Derived.Field3 DESC, Table1.Field1 ASC

Hopefully this is more clear - it's the line "Table2.Field6 IN (?,?,?,?)" which is giving me trouble. If I remove the parameters and give some hard-coded values it returns the correct data.

 

[jmeckley]

try converting the 'in' to a series of 'or' statements and see if that makes a difference.

WHERE    Table2.Field6  = ?
  or     Table2.Field6  = ?
  or     Table2.Field6  = ?
  or     Table2.Field6  = ?

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[chipboard]

Hi Jason, thanks for the suggestion - I tried that but it didn't give any different results.

Anyway, I've found a different way to structure the query using GROUP BY clauses. It's giving the right results but just doesn't seem as elegant as a sub-query!

Thanks for your help,