Using IF Exist in a Query

[djasy3]

hi, i'm developping a website under Asp.Net C# with an access database, but i'm having a trouble to use a query that need to verify if some informations exists in the database, if not then insert into the table, but i have the code in sql which is not accepted in access. i want to know if there's another way to make it or if access can use if exist, else if statement: here is the code

CREATE PROCEDURE ShoppingCartAjoutProduits
(@CartID char(36),
@ProduitID int,
@Attributs char(255))
As
IF EXISTS
(SELECT CartID
 FROM ShoppingCart
 WHERE ProduitID = @ProduitID AND CartID = @CartID)
UPDATE ShoppingCart
SET Quantite = Quantite + 1
WHERE ProduitID = @ProduitID AND CartID = @CartID
ELSE
IF EXISTS (SELECT titre FROM livres WHERE NumLivre = @NumLivre)
INSERT INTO ShoppingCart (CartID, ProduitID, Attributs, Quantite, DateAjoute)
VALUES (@CartID, @ProduitID, @Attributs, 1, GETDATE())

i want to know if they can be another to write it.
thanks !

 

[CJ_London]

You can do this sort of thing in Access using VBA and recordsets but is ShoppingCart a table in an Access db or one on the web?

 

[djasy3]

ShoppingCart is an Access db, i wanted to create a query which will help me to access to the database through the C# code when creating parameters. !

 

[CJ_London]

This would be an equivalent - note you have a parameter called NumLivre which is not declared in your code

Sub ShoppingCartAjoutProduits(CartID as string, ProduitID as integer, Attributs as string, NumLivre as Integer)
Dim rs as recordset
 
    set rs=openrecordset(SELECT CartID FROM ShoppingCart WHERE ProduitID = " & ProduitID & " AND CartID = '" & CartID & "'")
    if not rs.eof then
        currentdb.execute "Update ShoppingCart SET Quantite = Quantite + 1 WHERE ProduitID = " & ProduitID & " AND CartID = '" & CartID & "'"
    Else
        set rs=currentdb.openrecordset("SELECT titre FROM livres WHERE NumLivre = " & NumLivre)
        if not rs.eof then currentdb.execute "INSERT INTO ShoppingCart (CartID, ProduitID, Attributs, Quantite, DateAjoute) VALUES ('" & CartID & '", " & ProduitID & ", " & Attributs & ", 1, " & date())
    end if
    set rs=nothing
 
end sub

 

[djasy3]

Thanks CJ_London, the thing is i'm using C#.
Or, IS there any other way to write the querry in access with the :" WHERE EXISTS" clause?

 

[CJ_London]

In short, no, Access has an exists function - see below - but you do not have the SQL query structure you are looking for

"Update ShoppingCart SET Quantite = Quantite + 1 
WHERE ProduitID = " & ProduitID & " AND CartID = '" & CartID & "'"

will update if the record exists and fail if it doesn't - the same as

"Update ShoppingCart SET Quantite = Quantite + 1 
WHERE itID = " & ProduitID & " AND CartID = '" & CartID & "' AND Exists(SELECT * FROM ShoppingCart WHERE  ProduitID = " & ProduitID & " AND CartID = '" & CartID & "'")

Suggest move your access db to SQL Server then you can do what you require

 

[djasy3]

thank you! CJ