SQLTeam.com | Weblogs | Forums

Need help with insert/update statement(Appeon Powerbuilder)

Hello, hope someone can help me out because I am looking at this problems for days and I'm close to the breaking point. Please help!

**I'm a starting developer in Appeon Powerbuilder. I develop applications for mobile data collection. We use Appeon workspaces on Android devices. **

I HAVE CHECKT ALL THE PARAMETERS IN THE FOLLOWING SCRIPT BUT CAN NOT GET THE DATA TO BE SAVED. I HAVE CLEANED THE SCRIPT UP A BIT, SO ITS POSSIBLE I'M MISSING AN END IF. BUT ORIGINAL CODE SAVES FINE.

SCRIPT TO MOVE ANIMAL FROM 1 SHED TO ANOTHER
THIS IS THE CODE UNDER THE 'SAVE BUTTON'. INPUT COMES FROM RFID SCANNER (ANIMALNUMBER AND CURRENT SHED) AND MANUAL CHOICE OF TARGET SHED.

//Connect met SQL database // Profile SQL

SQL = create nvo_tran
SQL.dbms = "ODBC"
SQL.autocommit = false
SQL.dbparm = "ConnectString='DSN=SQL05"
connect using SQL;

// CHECK IF ANIMAL SHET IS OCCUPAID BEFORM MOVE

li_checkkraamhok=0

IF li_stal_nw = 32 THEN
SELECT count(*)
into :li_checkkraamhok
FROM SQL.DIER
where stal = 32
and afdeling = :li_afdeling_nw
and hok = :li_hok_nw
and dat_afvoer is null
and (status = 32 or status = 30 or status = 31)
using SQL;

IF li_checkkraamhok > 0 THEN
	select labnr 
	into :ls_zeugaanwezig
	from SQL.DIER
	where 	 stal = 32
			and afdeling = :li_afdeling_nw
			and hok = :li_hok_nw
			and dat_afvoer is null
			and (status = 32 or status = 30 or status = 31)
	using SQL;
			
	IF f_yesno("Hok is nog bezet" + '~n~r' + "Zeug " + ls_zeugaanwezig + "ligt in afd: " +string(li_afdeling_nw) + "  en hok: " + string(li_hok_nw)  + "~n~r~n~rDoorgaan?") = 1 THEN
	ELSE		
		goto annuleer
	END IF

END IF

IF ddlb_stal.text ="" or ddlb_afdeling.text="" or ddlb_hok.text="" then
IF isnull(li_stal_nw) or isnull(li_afdeling_nw) or isnull(li_hok_nw) then
messagebox ('LET OP', 'Kies stal, afdeling en hok')
ELSE
IF li_stal_nw > 30 and li_afdeling_nw > 0 and li_hok_nw > 0 THEN
ld_vandaag=today()

CHECK IF THERE A MOVE ALREADY IN THE SYSTEM

select count(*)
into :li_verplaatsing
from SQL.verplaatsing
where diernr=:ls_diernr
and datum = :ld_vandaag
using SQL;

	if li_verplaatsing = 0 THEN
		insert 
		into SQL.verplaatsing(kd_instal, bdrnr, diernr, datum, stal, afdeling, hok, txt_verpl)
		values (310002, 1, :ls_diernr, ':ld_vandaag', :li_stal_nw, :li_afdeling_nw, :li_hok_nw, ls_medewerker)
		using SQL;


if SQL.sqlcode < 0 then
			MessageBox ("DataBase fout!", sqlca.sqlerrtext, exclamation!)
			return
		end if 
	else 
		update SQL.verplaatsing
		SET stal=:li_stal_nw, afdeling=:li_afdeling_nw, hok=:li_hok_nw
		where diernr = :ls_diernr
		and datum = :ld_vandaag
		using SQL;

		if SQL.sqlcode < 0 then
			MessageBox ("DataBase fout!", sqlca.sqlerrtext, exclamation!)
			return
		end if 
	end if
	
	UPDATE SQL.dier
	SET stal=:li_stal_nw, afdeling=:li_afdeling_nw, hok=:li_hok_nw
	WHERE diernr=:ls_diernr
	USING SQL;

	if SQL.sqlcode < 0 then
		MessageBox ("DataBase fout!", sqlca.sqlerrtext, exclamation!)
		rollback using SQL;
		return
	ELSE
		commit;
		
		END IF
	END IF

hi

this is a MicroSoft SQL Server Forum

please google search for Appeon or PowerBuilder Forums
and post there .. you stand a good chance for a solution in those forums