SQL help INSERT and SELECT

[opposite99]

How to combine both SELECt and INSERT statement together?
I know the code below is wrong

sql = "SELECT * FROM Orderitem WHERE InvoiceNumber = '" & invoiceid & "'" & " AND ItemNumber = " & itemid
sql = sql & " AND INSERT INTO OrderItem(Quantity) VALUES(" & quantity & ")"

Invoiceid is a string and the rest of the variables are long
Ur help is deeply appreciated. Thanks

[parksie]

Try:

INSERT INTO ...; SELECT ...

Use a semicolon to separate the statements? It works on most DBs.

[Clunietp]

Then use the NextRecordset method of your recordset object to switch to the next recordset returned from that query

[opposite99]

Originally posted by Clunietp
Then use the NextRecordset method of your recordset object to switch to the next recordset returned from that query

Sorry Clunietp, I dun get what u mean. Can u show me the code? Thanks

[parksie]

What Tom means:

Dim rst as Recordset
Set rst = db.OpenRecordset("INSERT ...; SELECT ...")
' It has executed both, and is holding results for
' the INSERT query. Time to move on to SELECT:
rst.NextRecordset

[opposite99]

Thanks for ur reply parksie. Sorry, I done my code this way
If i put semicolon, an error msg will pop out and tell me there's an extra character, f i dun put, the error msg tells me there's a missing ;

Dim de as demain 'demain is dataenvironment

Set de = New demain

sql = "INSERT INTO OrderItem(Quantity) VALUES(" & quantity & ");"
sql = sql & " SELECT * FROM Orderitem WHERE InvoiceNumber = '" & invoiceid & "'" & " AND ItemNumber = " & itemid

de.conordersys.Open
de.conordersys.Execute sql
de.conordersys.Close

'conordersys is connection


[Edited by opposite99 on 09-17-2000 at 01:16 PM]

[parksie]

It complains because .Execute queries cannot return a recordset. For that, you'd have to use the Recordset object. What DB interface are you using? (DAO/ADO/RDO/other)

[opposite99]

Originally posted by parksie
It complains because .Execute queries cannot return a recordset. For that, you'd have to use the Recordset object. What DB interface are you using? (DAO/ADO/RDO/other)

I'm using Dataenvironment only