Board index » VBA

All times are UTC



 Union query gives 'Too few parameters' 
Author Message
Chris
#1 / 11
 Union query gives 'Too few parameters'

Hi,

I am trying to call an update query in MSAccess from a VB6 program using an
ADODB command object.
The technique I am using works fine for 'normal' queries, but if my query
contains UNIONs then I get the message:
  'Run-time error '-2147217904' [Microsoft][ODBC Microsoft Access Driver]
Too few parameters
  expected n'

I have noticed that the number of parameters expected (n) is the same as the
number of different UNIONS in my query.
note that in the examples below, I have stripped the queries down to the
bare minimum that still exhibits the problem.

If anyone has got any idea how to get round this I will be most grateful.

Thanks in anticipation of your response

Chris.

VBCode:
======
Private Sub Command1_Click()
Dim conAccess as ADODB.Connection
Dim cmdDoIt As New ADODB.Command
Dim strAccessConnect As String
strAccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                "Dbq=plu.mdb;" & _
                "DefaultDir=C:\tmp;" & _
                "Uid=Admin;Pwd=0861;"

conAccess.ConnectionString = strAccessConnect
conAccess.Open
cmdDoIt.ActiveConnection = conAccess
cmdDoIt.CommandType = adCmdText
cmdDoIt.CommandText = "execute Append_PLUs"
cmdDoIt.Execute

Access Queries:
===========
This works fine.

select *
from mytable
where myfield="1"

This fails with 'Too few parameters, expected 2':

  select *
  from mytable
  where myfield="1"
union all
  select *
  from mytable
  where myfield="2"



Sun, 29 Aug 2004 02:03:12 GMT  
Manxma
#2 / 11
 Union query gives 'Too few parameters'

Quote:
>This works fine.

>select *
>from mytable
>where myfield="1"

>This fails with 'Too few parameters, expected 2':

>  select *
>  from mytable
>  where myfield="1"
>union all
>  select *
>  from mytable
>  where myfield="2"

why a union when you just need an "or"  ?

select *
from mytable
where ((myfield="1") or (myfield="2"))

----------------
Richard "ManxMan" Killey

www.comeandread.com/access

visit my site for tips



Sun, 29 Aug 2004 03:20:48 GMT  
Chris
#3 / 11
 Union query gives 'Too few parameters'
Richard,

I'm not that thick, honest   8-)

But it's not that simple,
These queries are only examples to keep things simple. In the real ones, the
SELECT statements are different in each part of the UNION,
eg:

  select mid(myfieldb,2,4) as outputfield
  from mytable
  where myfield="1"
union all
  select mid(myfieldc,4,6) as outputfield
  from mytable
  where myfield="2"

Thanks tho,

Chris



Quote:

> >This works fine.

> >select *
> >from mytable
> >where myfield="1"

> >This fails with 'Too few parameters, expected 2':

> >  select *
> >  from mytable
> >  where myfield="1"
> >union all
> >  select *
> >  from mytable
> >  where myfield="2"

> why a union when you just need an "or"  ?

> select *
> from mytable
> where ((myfield="1") or (myfield="2"))

> ----------------
> Richard "ManxMan" Killey

> www.comeandread.com/access

> visit my site for tips



Sun, 29 Aug 2004 04:54:11 GMT  
Chris
#4 / 11
 Union query gives 'Too few parameters'
I have at least got round the problem, if not solved it by taking each part
of the UNION query and putting them into separate queries:
UNQuery1, UNQuery2, UNQuery3...

Then created another query to UNION them all together:

  select *
  from UNQuery1
UNION
  select *
  from UNQuery2
UNION
  select *
  from UNQuery3

Don't ask me why it works this way and not as a single query. Maybe Bill
might know...?

8-)

Chris.



Sun, 29 Aug 2004 04:58:48 GMT  
JOSEPHDubi
#5 / 11
 Union query gives 'Too few parameters'


Quote:
> Hi,

> I am trying to call an update query in MSAccess from a VB6 program using
an
> ADODB command object.
> The technique I am using works fine for 'normal' queries, but if my query
> contains UNIONs then I get the message:
>   'Run-time error '-2147217904' [Microsoft][ODBC Microsoft Access Driver]
> Too few parameters
>   expected n'

> I have noticed that the number of parameters expected (n) is the same as
the
> number of different UNIONS in my query.
> note that in the examples below, I have stripped the queries down to the
> bare minimum that still exhibits the problem.

> If anyone has got any idea how to get round this I will be most grateful.

> Thanks in anticipation of your response

> Chris.

> VBCode:
> ======
> Private Sub Command1_Click()
> Dim conAccess as ADODB.Connection
> Dim cmdDoIt As New ADODB.Command
> Dim strAccessConnect As String
> strAccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
>                 "Dbq=plu.mdb;" & _
>                 "DefaultDir=C:\tmp;" & _
>                 "Uid=Admin;Pwd=0861;"

> conAccess.ConnectionString = strAccessConnect
> conAccess.Open
> cmdDoIt.ActiveConnection = conAccess
> cmdDoIt.CommandType = adCmdText
> cmdDoIt.CommandText = "execute Append_PLUs"
> cmdDoIt.Execute

> Access Queries:
> ===========
> This works fine.

> select *
> from mytable
> where myfield="1"

> This fails with 'Too few parameters, expected 2':

>   select *
>   from mytable
>   where myfield="1"
> union all
>   select *
>   from mytable
>   where myfield="2"



Wed, 15 Sep 2004 01:09:17 GMT  
JOSEPHDubi
#6 / 11
 Union query gives 'Too few parameters'


Quote:
> Hi,

> I am trying to call an update query in MSAccess from a VB6 program using
an
> ADODB command object.
> The technique I am using works fine for 'normal' queries, but if my query
> contains UNIONs then I get the message:
>   'Run-time error '-2147217904' [Microsoft][ODBC Microsoft Access Driver]
> Too few parameters
>   expected n'

> I have noticed that the number of parameters expected (n) is the same as
the
> number of different UNIONS in my query.
> note that in the examples below, I have stripped the queries down to the
> bare minimum that still exhibits the problem.

> If anyone has got any idea how to get round this I will be most grateful.

> Thanks in anticipation of your response

> Chris.

> VBCode:
> ======
> Private Sub Command1_Click()
> Dim conAccess as ADODB.Connection
> Dim cmdDoIt As New ADODB.Command
> Dim strAccessConnect As String
> strAccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
>                 "Dbq=plu.mdb;" & _
>                 "DefaultDir=C:\tmp;" & _
>                 "Uid=Admin;Pwd=0861;"

> conAccess.ConnectionString = strAccessConnect
> conAccess.Open
> cmdDoIt.ActiveConnection = conAccess
> cmdDoIt.CommandType = adCmdText
> cmdDoIt.CommandText = "execute Append_PLUs"
> cmdDoIt.Execute

> Access Queries:
> ===========
> This works fine.

> select *
> from mytable
> where myfield="1"

> This fails with 'Too few parameters, expected 2':

>   select *
>   from mytable
>   where myfield="1"
> union all
>   select *
>   from mytable
>   where myfield="2"



Wed, 15 Sep 2004 01:49:20 GMT  
JOSEPHDubi
#7 / 11
 Union query gives 'Too few parameters'


Quote:



> > Hi,

> > I am trying to call an update query in MSAccess from a VB6 program using
> an
> > ADODB command object.
> > The technique I am using works fine for 'normal' queries, but if my
query
> > contains UNIONs then I get the message:
> >   'Run-time error '-2147217904' [Microsoft][ODBC Microsoft Access
Driver]
> > Too few parameters
> >   expected n'

> > I have noticed that the number of parameters expected (n) is the same as
> the
> > number of different UNIONS in my query.
> > note that in the examples below, I have stripped the queries down to the
> > bare minimum that still exhibits the problem.

> > If anyone has got any idea how to get round this I will be most
grateful.

> > Thanks in anticipation of your response

> > Chris.

> > VBCode:
> > ======
> > Private Sub Command1_Click()
> > Dim conAccess as ADODB.Connection
> > Dim cmdDoIt As New ADODB.Command
> > Dim strAccessConnect As String
> > strAccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
> >                 "Dbq=plu.mdb;" & _
> >                 "DefaultDir=C:\tmp;" & _
> >                 "Uid=Admin;Pwd=0861;"

> > conAccess.ConnectionString = strAccessConnect
> > conAccess.Open
> > cmdDoIt.ActiveConnection = conAccess
> > cmdDoIt.CommandType = adCmdText
> > cmdDoIt.CommandText = "execute Append_PLUs"
> > cmdDoIt.Execute

> > Access Queries:
> > ===========
> > This works fine.

> > select *
> > from mytable
> > where myfield="1"

> > This fails with 'Too few parameters, expected 2':

> >   select *
> >   from mytable
> >   where myfield="1"
> > union all
> >   select *
> >   from mytable
> >   where myfield="2"



Wed, 15 Sep 2004 02:40:58 GMT  
JOSEPHDubi
#8 / 11
 Union query gives 'Too few parameters'


Quote:



> > Hi,

> > I am trying to call an update query in MSAccess from a VB6 program using
> an
> > ADODB command object.
> > The technique I am using works fine for 'normal' queries, but if my
query
> > contains UNIONs then I get the message:
> >   'Run-time error '-2147217904' [Microsoft][ODBC Microsoft Access
Driver]
> > Too few parameters
> >   expected n'

> > I have noticed that the number of parameters expected (n) is the same as
> the
> > number of different UNIONS in my query.
> > note that in the examples below, I have stripped the queries down to the
> > bare minimum that still exhibits the problem.

> > If anyone has got any idea how to get round this I will be most
grateful.

> > Thanks in anticipation of your response

> > Chris.

> > VBCode:
> > ======
> > Private Sub Command1_Click()
> > Dim conAccess as ADODB.Connection
> > Dim cmdDoIt As New ADODB.Command
> > Dim strAccessConnect As String
> > strAccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
> >                 "Dbq=plu.mdb;" & _
> >                 "DefaultDir=C:\tmp;" & _
> >                 "Uid=Admin;Pwd=0861;"

> > conAccess.ConnectionString = strAccessConnect
> > conAccess.Open
> > cmdDoIt.ActiveConnection = conAccess
> > cmdDoIt.CommandType = adCmdText
> > cmdDoIt.CommandText = "execute Append_PLUs"
> > cmdDoIt.Execute

> > Access Queries:
> > ===========
> > This works fine.

> > select *
> > from mytable
> > where myfield="1"

> > This fails with 'Too few parameters, expected 2':

> >   select *
> >   from mytable
> >   where myfield="1"
> > union all
> >   select *
> >   from mytable
> >   where myfield="2"



Wed, 15 Sep 2004 12:26:20 GMT  
JOSEPHDubi
#9 / 11
 Union query gives 'Too few parameters'


Quote:



> > Hi,

> > I am trying to call an update query in MSAccess from a VB6 program using
> an
> > ADODB command object.
> > The technique I am using works fine for 'normal' queries, but if my
query
> > contains UNIONs then I get the message:
> >   'Run-time error '-2147217904' [Microsoft][ODBC Microsoft Access
Driver]
> > Too few parameters
> >   expected n'

> > I have noticed that the number of parameters expected (n) is the same as
> the
> > number of different UNIONS in my query.
> > note that in the examples below, I have stripped the queries down to the
> > bare minimum that still exhibits the problem.

> > If anyone has got any idea how to get round this I will be most
grateful.

> > Thanks in anticipation of your response

> > Chris.

> > VBCode:
> > ======
> > Private Sub Command1_Click()
> > Dim conAccess as ADODB.Connection
> > Dim cmdDoIt As New ADODB.Command
> > Dim strAccessConnect As String
> > strAccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
> >                 "Dbq=plu.mdb;" & _
> >                 "DefaultDir=C:\tmp;" & _
> >                 "Uid=Admin;Pwd=0861;"

> > conAccess.ConnectionString = strAccessConnect
> > conAccess.Open
> > cmdDoIt.ActiveConnection = conAccess
> > cmdDoIt.CommandType = adCmdText
> > cmdDoIt.CommandText = "execute Append_PLUs"
> > cmdDoIt.Execute

> > Access Queries:
> > ===========
> > This works fine.

> > select *
> > from mytable
> > where myfield="1"

> > This fails with 'Too few parameters, expected 2':

> >   select *
> >   from mytable
> >   where myfield="1"
> > union all
> >   select *
> >   from mytable
> >   where myfield="2"



Wed, 15 Sep 2004 12:30:29 GMT  
 
 Page 1 of 1
  [ 11 post ] 

 Relevant Pages 

1. ODBC Failed with 'UNION' in query

2. FindFirst Problem - 'Too few parameters'

3. Yargh! Sorting UNION'd SQL query

4. HELP: Parametrized union queries don't work with Jet 4.0

5. Too few parameters..What's this?

6. Passing Parameters to stored parameter queries using VB 5's Data Controls

7. Where clause with and statement giving error too few parameters expected 1

8. problem of converting C's union type to VB's a data type

9. problem of converting C's union type to VB's a data type

10. '|' in sql query gives - Error 3075 - 'Invalid use of vertical bars...'

11. '|' in sql query gives - Error 3075 - 'Invalid use of vertical bars...'

12. Parameter Query as a Form's RecordSource

  

 
Powered by phpBB® Forum Software