TROUBLE WRITING A COMPOUND SQL STATEMENT

[TNN]

Relatively new to SQL. Need help please.
Get Run-Time error message at cmd.Execute "CHARACTERS FOUND AFTER END OF SQL STATEMENT". I am trying to write a compound SQL statement and then use NextRecordset method to look at one recordset then the other.
I'm taking this right out of MSDN but can't make it work.

cmd.ActiveConnection = ado
cmd.CommandText = "SELECT * FROM BASSWAGE;SELECT * FROM BASSEARN" (all on one line)
cmd.CommandType = adCmdText
Set adoRsEvaluateWage = cmd.Execute

Debug.Print adoRsEvaluateWage!WAGENAME

Set adoRsEvaluateWage = adoRsEvaluateWage.NextRecordset

Debug.Print adoRsEvaluateWage!EMPNO

Thank You for any help
TNN, Tom
TNPAYROLL@AOL.COM


TOM

http://WWW.TNPAYROLL@AOL.COM

 

[foxdev]

I don't know anything about ADO, but is it possible that not having a semicolon after the last statement is a problem?

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Hi,

I don't think that is it. The semicolon in TSQL can be used as a statement separator.

I think the problem here is that you are attempting to return two recordsets with different columns, data types, etc. from within one recordset coming from the server, which isn't what you want. Instead, separate the selects such that they return results into two different recordset objects...

Also, in a production environment, NEVER NEVER use SELECT *. Always specify the columns you are selecting...

Hope this helps... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TNN]

Hi Tom,
I thought that is what I was doing with the compound statement, that is to create two separate and distinct recordsets. Not so??

How do I separate the selects as you say???
Thank You,
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[Guest_imported]

Hi Tom. I'm using mulitple RS's in the code I'm writing now and it works fine. It looks like it should work. However, I'm opening the database adopenstatic (I'm paging the results)...

Set oRS1 = Server.CreateObject("ADODB.Recordset&quot
oRS1.Cursorlocation = 3
oRS1.Open strQ, oConn, adOpenStatic

This shouldn't matter though.

mayby try turning the affected rows count off. type "SET NOCOUNT ON" before the select statements.

 

[TomSark]

I would SET NOCOUNT ON. This can cause problems if it is off in certain cases. I make a habit of this. This might be your problem.

But, back to my question, do the columns in the 2 tables match? If they differ, that chould be causing the problem. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TNN]

pathious,
What is "SET NOCOUNT ON" and how do I use it???

Tom,
The columns in the 2 tables do not match. Why should that matter?? In using the NextRecordset method I am trying to create 2 separate and distinct recordsets entirely unrelated in any way to each other and then calling them up with the NextRecordset method as needed.
TNN, Tom
TNPAYROLL@AOL.COM


TOM

http://WWW.TNPAYROLL@AOL.COM

 

[calahans]

I can understand why you should bring 2 record sets back at the same time. This could be hugely expensive in resources, and if someone has to maintain you code later - may cause issues for them (uderstanding the logic)

Do it in two steps, or failing that do a join...

Cal

 

[MarkSweetland]

From the ADO 2.1 Helpfile:

Dim rstCompound As ADODB.Recordset
Dim strCnn As String
Dim intCount As Integer

' Open compound recordset.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial
Catalog=pubs;User Id=sa;Password=; "
   
Set rstCompound = New ADODB.Recordset
rstCompound.Open "SELECT * FROM authors; " & _
    "SELECT * FROM stores; " & _
    "SELECT * FROM jobs", strCnn, , , adCmdText

' Display results from each SELECT statement.
intCount = 1
Do Until rstCompound Is Nothing
  Debug.Print "Contents of recordset #" & intCount
  Do While Not rstCompound.EOF
    Debug.Print , rstCompound.Fields(0), _
           rstCompound.Fields(1)
    rstCompound.MoveNext
  Loop
   
  Set rstCompound = rstcompound.NextRecordset
  intCount = intCount + 1
Loop

Try using the ADODB.Recordset Open method rather than the Command.Execute May make a difference...

Mark

 

[TNN]

Thank You Mark,
I will give it a try. I did try the OPEN method but still didn't work. I'll try again.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM