Digital Workshop

Hi there,

Using the following script:

var db = new Database("FILEDSN=F:\\Cassandra\\Development\\Taromatic\\taromatic2;")
var RSet = db.ExecuteSQL("SELECT card.cardname, cardcontext.cardcontextdesc FROM card, cardcontext WHERE card.cardno = " + randomcard + " and card.cardno = cardcontext.cardno;")

meaningtext2.SetSelection(0, -1)

meaningtext2.ReplaceSelection(RSet.cardname + ": " + RSet.cardcontextdesc)


The two tables are card (parent) and cardcontext (child) and the foreign key is cardno (on both tables). randomcard is a variable with a number that corresponds to cardno.

The field cardname is on the table card. This is correctly returned and displayed in meaningtext2.

The field cardcontextdesc is from the table cardcontext and is not returned - the word "null" is concatenated to the cardname in meaningtext.

Does anyone know why this inner join isn't working?

Thanks very much

Melanie

You should be able to get this to work by having another field in the select list for cardcontext table
try
var RSet = db.ExecuteSQL("SELECT card.cardname, cardcontext.cardno, cardcontext.cardcontextdesc FROM card, cardcontext WHERE card.cardno = " + randomcard + " and card.cardno = cardcontext.cardno;")

The problem is that cardcontextdesc is a variable length field (memo in Access) and if variable length fields are the only ones selected for a table the the values don't get returned correctly. To solve it just select another field that is not a variable length one and it should work.

Thanks Tony,

Yes, cardcontextdesc is a memo field... I tried this as you suggested:

var RSet = db.ExecuteSQL("SELECT card.cardname, cardcontext.cardno, cardcontext.cardcontextdesc FROM card, cardcontext WHERE card.cardno = " + randomcard + " and card.cardno = cardcontext.cardno;")
meaningtext2.ReplaceSelection(RSet.cardname + ": " + RSet.cardcontextdesc)

and it still doesn't work!

However, splitting the query into 2 does work:

var Rdesc = db.ExecuteSQL("SELECT cardno,cardcontextdesc FROM cardcontext WHERE cardno = " + randomcard + ";")
var Rcard = db.ExecuteSQL("SELECT cardname FROM card WHERE cardno = " + randomcard +";")
meaningtext2.ReplaceSelection(Rcard.cardname + ": " + Rdesc.cardcontextdesc)

Also using '*' in the first query works:

var Rdesc = db.ExecuteSQL("SELECT * FROM cardcontext WHERE cardno = " + randomcard + ";")
var Rcard = db.ExecuteSQL("SELECT cardname FROM card WHERE cardno = " + randomcard +";")
meaningtext2.ReplaceSelection(Rcard.cardname + ": " + Rdesc.cardcontextdesc)


Using '*' in the join query DOESN'T work:

var RSet = db.ExecuteSQL("SELECT * FROM card, cardcontext WHERE card.cardno = " + randomcard + " and card.cardno = cardcontext.cardno;")
meaningtext2.ReplaceSelection(RSet.cardname + ": " + RSet.cardcontextdesc)

So far only using 2 separate queries works This is odd and a bit forboding as I'm looking forward to lots of complex SQL queries in my current publication...

Eek!

Melanie

I have had another look at this problem and you can not get memo fields using a join query. I will look at trying to get it to work in a future version but for now you will have to do separate queries when memo fields are involved.

Thanks for confirming that for me Tony.

Are there any other known caveats or limitations when using ExecuteSQL that we should know about?

Thanks

Melanie

There are no other problems I am aware of.