the equivalent sql procedure in access query

[djasy3]

hi, i'm using a sql procedure which i'd like to implement the equivalent in ms access. i'm not very good at access but i want to know if they can be any possibilities. this is my procedure

CREATE PROCEDURE CreateOrders
(@cartId char(36)) 
As
DECLARE @CmdID int
INSERT INTO Commande DEFAULT VALUES
SET @CmdID = @@IDENTITY
INSERT INTO DetailsCommande ( CmdID, ProduitID, ProduitTitre, Quantite, CoutUnitaire )
SELECT [@CmdID], livres.NumLivre, livres.titre, ShoppingCart.Quantite, livres.prix
FROM livres INNER JOIN ShoppingCart ON livres.NumLivre = ShoppingCart.ProduitID
WHERE ShoppingCart.CartID=[@CartID];
DELETE FROM ShoppingCart
WHERE CartID = @CartID
SELECT @CmdID;

 

[Cronk]

The short answer is that you can't, at least not in the same way.

Access queries would be a single sql statement such as
INSERT into YourTable (<field list>) SELECT ....

Sub queries can be embedded in SELECTs

Use VBA to generate or set values and to execute multiple queries.

Searching will give you countless examples of code.

 

[CJ_London]

It is possible but in Access a query can only do one thing (insert, update, delete, etc) so you would need to break this down into each action and run them together in VBA

Looks like your code is copying details from a record in the shoppingcart table to the detailscommand table, then deleting the item from the shopping kart, then going to the next item in the shopping cart

In VBA your code would look something like this

Sub CreateOrders
Dim CartRst as recordset
 
Set CartRst =currentdb.openrecordset("SELECT ShoppingCart.CmdID, livres.NumLivre, livres.titre, ShoppingCart.Quantite, livres.prix FROM livres INNER JOIN ShoppingCart ON livres.NumLivre = ShoppingCart.ProduitID")
While not CartRSt.EOF
    currentdb.execute("INSERT INTO DetailsCommande ( CmdID, ProduitID, ProduitTitre, Quantite, CoutUnitaire ) " _
& "Values (" & CartRst!CmdID & ", " & CartRst!NumLivre & ", " & CartRst!titre & ", " & CartRst!Quantite & ", " & CartRst!prix & ")")
     currentdb.execute("DELETE * FROM ShoppingCart WHERE CartID=" & RST!CartID)
    CartRst.Movenext
Next
set CartRst=Nothing

 

[djasy3]

OK! so how can i retreive the last generated number from a table in a querry?
i tried with this : select @@identity from Table_Name, but it gives me column with 15 zeros. cos 15 was the last generated number in my table.

 

[David R]

SELECT TOP 1 ....ORDER BY [Generated Field] DESC; ?

Alternatively you could use DMax().

 

[djasy3]

@David R, thanks, but if i execute the query, a prompt box appear asking to enter a value of request1.Expr1000. but it gives me the result any way even if i don't put something in it!

 

[David R]

Read CJ London's reply again. Where are you calling this procedure from? You should be able to calculate that top value and then use it in your query (might be helpful to stash it in an unbound field on your switchboard/form, otherwise you'll have to build the VBA by hand each time)

 

[djasy3]

@David, i'm calling the procedure in a C# file, through a method. what i want to do is to save the query in access so that i may be able to call it from the c# file and retreive the value from there and then pass it to another query. the application is with Asp.net that's why i cant write the vb code in access

 

[David R]

Ah, I've got no help for you then. I know you can call a Macro in Access at startup, but not sure how you'd get the value back out to a C# file, short of writing it to a .txt file temporarily which I'm sure isn't the best way...

 

[djasy3]

ok, it's fine! thanks anyway for your replies. if i can have the value with the querry, it's already good(with the select top...) it's time now to deal with the C# side! thanks!

 

[djasy3]

@david: i use the Dmax and it worked perfectly! thanx buddy

 

[David R]

Glad you got it working.