update problem

hi all,
i have a problem with an update command.

this is my code:


sqlstmt = "SELECT color_code, description from colors "
connessione = search_conness("Colors")
lSuccess = sqlexec(connessione,sqlstmt,"ColorsData")
IF !check_query(lSuccess)
RETURN .F.
ENDIF

-- until here it works fine because i have my cursor colorsData full with
the records from sql table Colors

for xx=1 to 14
Esegui = "UPDATE Lista_Rep SET Lista_Rep.Des_Col"+ALLTRIM(STR(xx))+" =
ALLTRIM(NVL(ColorsData.descrizion,'')) WHERE
alltrim(Lista_Rep.Col"+ALLTRIM(STR(xx))+") = alltrim(ColorsData.color_code)
"
&Esegui
Esegui = "UPDATE Lista_Rep SET Lista_Rep.D_cd_Col"+ALLTRIM(STR(xx))+" =
ALLTRIM(NVL(ColorsData.descrizion,'')) WHERE
alltrim(Lista_Rep.cd_Col"+ALLTRIM(STR(xx))+") =
alltrim(ColorsData.color_code) "
&Esegui
next xx


-- lista_rep is a cursor with data that I use for printing.
I expect lista_rep.des_col to have the value of description but it does not
work.
Because i am new to fox I wonder if i am missing something...like select ,
use or other keyword.
any idea?
TIA

Carsten
Tue Jul 05 01:30:09 CDT 2005

Ilkaos,

looks like you are missing the text-delimiters.
A simple statement should look like this:

lcNewVal= "Hello"
lcUpd= [UPDATE someTable SET cFieldName = '] + lcNewVal + [' WHERE nID =
123]

&lcUpd
or ExecScript( lcUpd )
or _VFP.DoCmd( lcUpd )

--
Cheers
Carsten
_______________________________

"ilkaos" <12wre12wq2@katamail.it> schrieb im Newsbeitrag
news:dabl27$iav$1@domitilla.aioe.org...
> hi all,
> i have a problem with an update command.
>
> this is my code:
>
>
> sqlstmt = "SELECT color_code, description from colors "
> connessione = search_conness("Colors")
> lSuccess = sqlexec(connessione,sqlstmt,"ColorsData")
> IF !check_query(lSuccess)
> RETURN .F.
> ENDIF
>
> -- until here it works fine because i have my cursor colorsData full with
> the records from sql table Colors
>
> for xx=1 to 14
> Esegui = "UPDATE Lista_Rep SET Lista_Rep.Des_Col"+ALLTRIM(STR(xx))+" =
> ALLTRIM(NVL(ColorsData.descrizion,'')) WHERE
> alltrim(Lista_Rep.Col"+ALLTRIM(STR(xx))+") =
alltrim(ColorsData.color_code)
> "
> &Esegui
> Esegui = "UPDATE Lista_Rep SET Lista_Rep.D_cd_Col"+ALLTRIM(STR(xx))+" =
> ALLTRIM(NVL(ColorsData.descrizion,'')) WHERE
> alltrim(Lista_Rep.cd_Col"+ALLTRIM(STR(xx))+") =
> alltrim(ColorsData.color_code) "
> &Esegui
> next xx
>
>
> -- lista_rep is a cursor with data that I use for printing.
> I expect lista_rep.des_col to have the value of description but it does
not
> work.
> Because i am new to fox I wonder if i am missing something...like select ,
> use or other keyword.
> any idea?
> TIA
>
>

ilkaos
Tue Jul 05 04:51:50 CDT 2005


>
> looks like you are missing the text-delimiters.

(cut)

> Cheers
> Carsten


thanks for your answer.
it is not a problem of text-delimiters but it is connected with the cursor
that I use.
In fact if I use this loop:

----
select lista_rep
GO top
scan
for xx=1 to 14
SELECT colori
GO top
scan

Esegui = "UPDATE Lista_Rep SET Lista_Rep.Des_Col"+ALLTRIM(STR(xx))+" =
ALLTRIM(NVL(colori.descrizion,'')) WHERE
alltrim(Lista_Rep.Col"+ALLTRIM(STR(xx))+") = alltrim(colori.cd_colore) and
alltrim(Lista_Rep.cd_tcart) = ALLTRIM(colori.cd_cart)"
&Esegui

endscan
next xx
endscan

----
it works!
so i am missing something with the nature of the cursor in VFP...
if i was working with two tables in standrd sql there would be no reason to
llop through them and i wolul have
solved my problem with the only update statement. workin with VFP cursor it
looks like i have to select - every single
record of the cursor - and the update command ONLY updates that record.
is this the way of working with cursor in VFP?
if so, is there a way to avoid this and use just one update command?
thanks and bye.

Jack
Tue Jul 05 11:20:41 CDT 2005

On Tue, 5 Jul 2005 11:51:50 +0200, "ilkaos" <12wre12wq2@katamail.it>
wrote:

>
>>
>> looks like you are missing the text-delimiters.
>
>(cut)
>
>> Cheers
>> Carsten
>
>
>thanks for your answer.
>it is not a problem of text-delimiters but it is connected with the cursor
>that I use.
>In fact if I use this loop:
>
>----
>select lista_rep
>GO top
>scan
>for xx=1 to 14
> SELECT colori
> GO top
> scan
>
> Esegui = "UPDATE Lista_Rep SET Lista_Rep.Des_Col"+ALLTRIM(STR(xx))+" =
>ALLTRIM(NVL(colori.descrizion,'')) WHERE
>alltrim(Lista_Rep.Col"+ALLTRIM(STR(xx))+") = alltrim(colori.cd_colore) and
>alltrim(Lista_Rep.cd_tcart) = ALLTRIM(colori.cd_cart)"
> &Esegui
>
> endscan
>next xx
>endscan
>
>----
>it works!
>so i am missing something with the nature of the cursor in VFP...
>if i was working with two tables in standrd sql there would be no reason to
>llop through them and i wolul have
>solved my problem with the only update statement. workin with VFP cursor it
>looks like i have to select - every single
>record of the cursor - and the update command ONLY updates that record.

What is the UPDATE statement you would use with SQL server?

>is this the way of working with cursor in VFP?

No, UPDATE updates whatever records match the WHERE clause.

>if so, is there a way to avoid this and use just one update command?
>thanks and bye.
>
>

When the record you want to modify is the current record, you should
use REPLACE instead of UPDATE.

select lista_rep
scan
for xx=1 to 14
cFld = "Des_Col" + ALLTRIM(STR(xx))
SELECT colori
LOCATE FOR ALLTRIM(colori.cd_colore) == ALLTRIM(EVALUATE(cFld))
IF FOUND()
REPLACE (cFld) WITH ALLTRIM(NVL(colori.descrizion,'')) IN
lista_rep
ENDIF
next xx
endscan

Devers
Tue Jul 05 15:14:05 CDT 2005

Hi Ilkaos, I noticed you are using ALLTRIM() in your WHERE clause. Unless
color_code is always the same length, you might want to check SET EXACT and
SET ANSI to make sure that your last UPDATE is not overwriting a previous
update.

"ilkaos" wrote:

> hi all,
> i have a problem with an update command.
>
> this is my code:
>
>
> sqlstmt = "SELECT color_code, description from colors "
> connessione = search_conness("Colors")
> lSuccess = sqlexec(connessione,sqlstmt,"ColorsData")
> IF !check_query(lSuccess)
> RETURN .F.
> ENDIF
>
> -- until here it works fine because i have my cursor colorsData full with
> the records from sql table Colors
>
> for xx=1 to 14
> Esegui = "UPDATE Lista_Rep SET Lista_Rep.Des_Col"+ALLTRIM(STR(xx))+" =
> ALLTRIM(NVL(ColorsData.descrizion,'')) WHERE
> alltrim(Lista_Rep.Col"+ALLTRIM(STR(xx))+") = alltrim(ColorsData.color_code)
> "
> &Esegui
> Esegui = "UPDATE Lista_Rep SET Lista_Rep.D_cd_Col"+ALLTRIM(STR(xx))+" =
> ALLTRIM(NVL(ColorsData.descrizion,'')) WHERE
> alltrim(Lista_Rep.cd_Col"+ALLTRIM(STR(xx))+") =
> alltrim(ColorsData.color_code) "
> &Esegui
> next xx
>
>
> -- lista_rep is a cursor with data that I use for printing.
> I expect lista_rep.des_col to have the value of description but it does not
> work.
> Because i am new to fox I wonder if i am missing something...like select ,
> use or other keyword.
> any idea?
> TIA
>
>
>

ilkaos
Wed Jul 06 05:03:08 CDT 2005


>
> What is the UPDATE statement you would use with SQL server?
>

something like this (looping through to give value to xx of course):

UPDATE Lista_Rep SET Lista_Rep.Des_Col"+ALLTRIM(STR(xx))+" =
ALLTRIM(NVL(colori.descrizion,''))

-- in this line with SQL i would write: FROM COLORI but in the foxpro
UPdate it is not possible

WHERE
alltrim(Lista_Rep.Col"+ALLTRIM(STR(xx))+") = alltrim(colori.cd_colore) and
alltrim(Lista_Rep.cd_tcart) = ALLTRIM(colori.cd_cart)"


what i would like to obtain is to update the field "colour description" in
the lista_rep cursor taking
the value from the Colori cursor.


> >is this the way of working with cursor in VFP?
>
> No, UPDATE updates whatever records match the WHERE clause.
>

but it does not seem to me...if I do not loop with the scan through the
colori cursor it does not work
(it updates the lista_rep cursor only for the first record of the colori
cursor...)

so what i do not understand is the reason why I have to loop even through
the
colori cursor to obtain my result. it should be (and with standard Sql -
IS - )
enough to use only one uodate command (for evry color(xx) value of course).
i am still missing something, i guess.