AW: [pro_vb] ADO RS using Union Query
----- Original Message ----- 
From: A. Pajarillo 
To: rds 
Sent: Thursday, September 26, 2002 10:16 AM
Subject: Re: [pro_vb] ADO RS using Union Query


The ms access query (named qryUnion) is:

PARAMETERS subj Text ( 15 ), EntryDate DateTime;
SELECT TrackSheet.student_no, 1 AS First,0 AS Second,0 AS Third
FROM TrackSheet
WHERE (((TrackSheet.subject)=[subj]) AND ((Month([assign_dt]))=Month(DateAdd("m",-2,[EntryDate]))) AND
((Year([assign_dt]))=Year(DateAdd("m",-2,[EntryDate]))))
GROUP BY TrackSheet.student_no

UNION SELECT TrackSheet.student_no, 0 AS First,1 AS Second,0 AS Third
FROM TrackSheet
WHERE (((TrackSheet.subject)=[subj]) AND ((Month([assign_dt]))=Month(DateAdd("m",-1,[EntryDate]))) AND
((Year([assign_dt]))=Year(DateAdd("m",-1,[EntryDate]))))
GROUP BY TrackSheet.student_no;
.....
there is a third table which looks exactly like the 1st and 2nd except for the date. by the way, this works fine if run in ms
access.

the calling module is:
.....connection

Set rsUnion = New ADODB.Recordset
Set objComm = New ADODB.Command
    
With objComm
    Set .ActiveConnection = oConn
    .CommandText = "qryUnion"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("paramSubject", _
        adChar, adParamInput, 15)
    .Parameters("paramSubject") = cboSubject
        
    .Parameters.Append .CreateParameter("paramDate", _
        adDate, adParamInput)
    .Parameters("paramDate") = dtCurrentMo
End With
rsUnion.Open objComm

if i cut the union select... and run the first select only, this module works fine.
  ----- Original Message ----- 
  From: rds 
  To: 'A. Pajarillo' 
  Sent: Wednesday, September 25, 2002 4:25 PM
  Subject: AW: [pro_vb] ADO RS using Union Query


  Send me a few pice of code ... 

  mfg Kilian 

  -----Ursprüngliche Nachricht----- 
  Von: A. Pajarillo [mailto:aloyp@m...] 
  Gesendet: Mittwoch, 25. September 2002 09:57 
  An: professional vb 
  Betreff: [pro_vb] ADO RS using Union Query 



  i'm trying to create a recordset using an ADO command object which points to an MS Access union query with parameters. It runs ok
when executing the query in access but when i use the ado command object it gives me this error: "[Microsoft][ODBC Microsoft Access
Driver]Too few parameters. Expected Error 4."

  any help or pointers for a workaround will be appreciated. 

  aloy 




  --- 
  Visual C# - A Guide for VB6 Developers 
  This book will make it easy to transfer your skills 
  from Visual Basic 6 to C#, the language of choice 
  of the .NET Framework. 
  http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059 

  ---