ADO Error

[hbez]

I have one StringGrid, DataSource, ADOConnection and an ADOQuery on my form. I am trying to update an Access db:

adoQ.SQL.Text := 'SELECT PLAYERS.COUNTRY .... {abbreviated}
adoQ.Active := True;

Then

adoQ.SQL.Text := 'UPDATE GUESTHOUSES... {abbreviated}
adoQ.Active := True;

The error is 'Current provider does not support returning multiple recordsets from a single execution'.
I would have thought these are two separate executions. What am I doing wrong?
Hannes

 

[whosrdaddy]

please provide the full code for the SELECT query

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[hbez]

This forum is really incredible - thkx for quick reply.

Actually, the SELECT statement on its own works just fine, it's the UPDATE that gives the error:

{
adoQ.SQL.Text := 'SELECT PLAYERS.COUNTRY, PLAYERS.PLAYER, GUESTHOUSES.GNAME FROM GUESTHOUSES INNER JOIN PLAYERS ON GUESTHOUSES.HOUSEID = PLAYERS.GUESTHOUSE WHERE (((PLAYERS.COUNTRY) Like "Angola" Or (PLAYERS.COUNTRY) Like "Egypt") AND ((GUESTHOUSES.GNAME)<>"Bluebean Manor"));';

adoQ.Active := True;
}

//I use a MessageDlg here so user can choose to do this Update step:

adoQ.SQL.Text := 'UPDATE GUESTHOUSES INNER JOIN PLAYERS ON GUESTHOUSES.HOUSEID = PLAYERS.GUESTHOUSE SET GUESTHOUSES.GNAME = "BlueBean Manor" WHERE (((PLAYERS.COUNTRY) Like "Angola" Or (PLAYERS.COUNTRY) Like "Egypt") AND ((GUESTHOUSES.GNAME)<>"Bluebean Manor"));';

adoQ.Active := True;

All my SQL generated in Access 2003 works fine, but here's another one that gives an 'unknown error', yet it works in Access as-is (whether or not I use '=' or 'Like'):

adoQ.SQL.Text := 'SELECT PLAYERS.PLAYER, PLAYERS.COUNTRY FROM PLAYERS WHERE (((PLAYERS.POSITION)="GS"));';

adoQ.Active := True;

Hannes

 

[lespaul]

I don't know if it's the same for ADO, but for UPDATE and INSERT queries I have to use the ExecSQL command, not set Active := True;




Leslie

Have you met Hardy Heron?

 

[whosrdaddy]

Agreed with lespaul, and for the unknown error, you must use single quotes for strings in queries:

'SELECT PLAYERS.PLAYER, PLAYERS.COUNTRY FROM PLAYERS WHERE PLAYERS.POSITION=''GS'''

remark that '' used in a string will result in '.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[lespaul]

yeah, i couldn't tell if those were [tt]"[/tt] or [tt]''[/tt]

Leslie

Have you met Hardy Heron?

 

[hbez]

The ExecSQL did the trick with UPDATE. On the other problem I use " instead of '' in many other places without a problem. I did find out from the net that Access treats Position as a key word, so I changed the last bit to

WHERE PLAYERS.[POSITION]="GS";';

and now it works!! Maybe one should put this up as a tip in this forum?

Many thkx to you all again.
Hannes