List:General Discussion« Previous MessageNext Message »
From:J Trahair Date:December 17 2007 5:21pm
Subject:Re: Update but insert if not exist
View as plain text  
This is a question I want to know the answer to, as well! Is there any way of avoiding
looking up a specific record in a table to see if it exists, before deciding whether to
INSERT INTO or UPDATE, eg:

    mstrSQL = "SELECT * FROM Shops WHERE ShopReference = '" &
grd1.TextMatrix(numRowNo, 1) & "'"
    Set rsRecordset = New ADODB.Recordset
    gconn.CursorLocation = adUseServer
    rsRecordset.Open mstrSQL, gconn, adOpenDynamic, adLockOptimistic
    If rsRecordset.EOF = True Then
        mstrSQL = "INSERT INTO Shops (ShopNameInFull, ShopReference, TillNumber) VALUES
(strShopNameInFull, strShopReference, strTillNumber)"       
        mconn.Execute mstrSQL
    Else
        mstrSQL = "UPDATE Shops SET ShopNameInFull = 'strShopNameInFull', ShopReference =
'strShopReference', TillNumber = 'strTillNumber' WHERE ShopReference = '" &
grd1.TextMatrix(numRowNo, 1) & "'"
        mconn.Execute mstrSQL
    End If


....just thought I'd ask!

Jonathan Trahair


Thread
Update but insert if not existSteffan A. Cline16 Dec
  • Re: Update but insert if not existAfan Pasalic16 Dec
    • Re: Update but insert if not existRob Wultsch17 Dec
  • Re: Update but insert if not existJ Trahair17 Dec
    • Re: Update but insert if not existJay Pipes17 Dec