Alaska Software Inc. - Re: Sentece DELETE with ODBC (SqlExpress or not but I work with SqlExpress).
Username: Password:
AuthorTopic: Re: Sentece DELETE with ODBC (SqlExpress or not but I work with SqlExpress).
César Calvo Sentece DELETE with ODBC (SqlExpress or not but I work with SqlExpress).
on Sat, 04 Jun 2016 13:01:05 +0200
Hello guys.

I need delete recordrs of a table and I get ir record by record.

-------------------------------------------------------------------
cSql1 := "SELECT VBSEGS1.DOC, VBSEGS1.CR2, VBSEGS1.CT, VBSEGS1_1.CR2, 
VBSEGS1_1.CT "
cSql1 += "FROM VBSEGS1 INNER JOIN VBSEGS1 AS VBSEGS1_1 ON VBSEGS1.DOC = 
VBSEGS1_1.DOC "
cSql1 += "WHERE (((VBSEGS1.CR2)='A78923125') AND ((VBSEGS1.CT)='40') AND 
((VBSEGS1_1.CR2)='A82018474') AND ((VBSEGS1_1.CT)='50'));"

oCur1 := SQLDataSet():new(cSQL1,oCon1)
oCur1:execute()

DO WHILE !oCur1:EOF()

     cDoc := oCur1:FieldGet("DOC")

      cSql1 := "DELETE VBSEGS1.DOC FROM VBSEGS1 WHERE (((VBSEGS1.DOC)=" + 
"'" + cDoc + "'" + "));"
      oStmt1:SQLString := cSQL1
      oStmt1:Execute()

      oCur1:Skip()

ENDDO


oCur1:close()

------------------------------------------------------------------

I would desire not go record by record, I mean with SqlStatetment

VBSEG1 and VBSEG1_1 are the same table.

Does anybody the way for do it?

Thanks.
César.
Andreas Gehrs-Pahl
Re: Sentece DELETE with ODBC (SqlExpress or not but I work with SqlExpress).
on Sat, 04 Jun 2016 19:47:36 -0400
César,

>I need delete recordrs of a table and I get ir record by record.

If there aren't a lot of records, you could delete them all at once with 
this:

cSQL := "DELETE FROM VBSEGS1 WHERE DOC IN ("
while .not. oCur1:EoF()
   cSQL += "'" + oCur1:FieldGet("DOC") + "',"
   oCur1:Skip()
enddo

oStmt1:SQLString := left(cSQL, len(cSQL) - 1) + ");"
oStmt1:Execute()

Hope that helps,

Andreas
Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC
César Calvo Re: Sentece DELETE with ODBC (SqlExpress or not but I work with SqlExpress).
on Sun, 05 Jun 2016 21:02:20 +0200
Thanks dear Andreas, it works fine
Kinds regards.
César.

"Andreas Gehrs-Pahl" escribió en el mensaje de 
noticias:1pc5pcz8o907h.qzrc9vs15lcp$.dlg@40tude.net...

César,

>I need delete recordrs of a table and I get ir record by record.

If there aren't a lot of records, you could delete them all at once with
this:

cSQL := "DELETE FROM VBSEGS1 WHERE DOC IN ("
while .not. oCur1:EoF()
   cSQL += "'" + oCur1:FieldGet("DOC") + "',"
   oCur1:Skip()
enddo

oStmt1:SQLString := left(cSQL, len(cSQL) - 1) + ");"
oStmt1:Execute()

Hope that helps,

Andreas
Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC