473,322 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Loop problem

i am new to asp....

i am facing a problem in for loop
what i want is, if the record does not exist loop should execute insert query else it should go to update query ..
while i try to execute the code...if the record is not there it will insert all records after that it goes to the update query also ...and then it will exit from the loop........


for x = 1 to 4
prev = "prev" & x
codeva = "code" & x
curva= "cur" & x
curvalue=trim(Request.Form(curva))
prevvalue= trim(Request.Form(prev))
code=trim(Request.Form(codeva))
if prevvalue= "" then
prevvalue = 0
end if
if curvalue= "" then
curvalue = 0
end if
checksql2="select * from LeaveOpeningBalance where EMPNO = '"& getEmpNum &"' and " & _
"leave_code='"& code &"' and record_active='Y'"
Response.Write "<BR>" & checksql2
set rs7 = conn1.execute(checksql2)
Response.Write("<Br> " & rs7.eof)
if rs7.eof = true then

Response.Write ("<br /> INSERTING DATA --" & sqlUP1)


else

'*************UPDATING DATA**********************
Response.Write ("<br /> UPDATING DATA --" & sqlUP1)

end if


next

can any one help me in this

simi
Apr 23 '07 #1
5 1738
devsusen
136 100+
hi simi,

Expand|Select|Wrap|Line Numbers
  1. for x = 1 to 4
  2. prev = "prev" & x
  3. codeva = "code" & x
  4. curva= "cur" & x
  5. curvalue=trim(Request.Form(curva))
  6. prevvalue= trim(Request.Form(prev))
  7. code=trim(Request.Form(codeva))
  8. if prevvalue= "" then
  9. prevvalue = 0
  10. end if
  11. if curvalue= "" then
  12. curvalue = 0
  13. end if
  14. checksql2="select * from LeaveOpeningBalance where EMPNO = '"& getEmpNum &"' and " & _
  15. "leave_code='"& code &"' and record_active='Y'"
  16. Response.Write "<BR>" & checksql2
  17. set rs7 = conn1.execute(checksql2)
  18. Response.Write("<Br> " & rs7.eof)
  19. if rs7.eof = true then
  20.  
  21. Response.Write ("<br /> INSERTING DATA --" & sqlUP1)
  22.  
  23.  
  24. else
  25.  
  26. '*************UPDATING DATA**********************
  27. Response.Write ("<br /> UPDATING DATA --" & sqlUP1)
  28.  
  29. end if
  30.  
  31.  
  32. next
the code is not the complete code. atleast u should provide some sort of psudo code so that we can understand where the actual problem lies.

susen
Apr 23 '07 #2
i have 2 files ...one for entering values in input box (eg: value.asp) and another for saving the values (eg: save.asp)
after submitting value.asp ,i pass values to save.asp....

what i want is while i execute the for loop if there is NO records it should insert the new value 'and if there is record it should run update query...

but now what is happening is, if there is no record it will insert all the new record ,after that, it goes to else condition loop and run update query also.
ie.instead of executing only the insert query, it will execute update query also..




<body>

<form name="confstaffleaveSave" method="post">
<input type="text" name ="emp_num" value ="<%=login%>">
<%

if true then
session.Contents("secondtime")=login

dim TodaysDate
sql3="select to_char(sysdate,'DD-Mon-YY') from dual"
set rs1=conn2.execute(sql3)
TodaysDate=rs1(0).value
' Response.Write ("<br>" + TodaysDate)
dim getReason,getLeaveCode,getHolidayCode,getEmpNum,ge tLeaveName,getApplDate,getJoinDate1,leavearray,get holiday
dim getLeaveYear_From,getLeaveYear_To,getLeave_Code,ge tHOLI_CODE,getValid_to_date,getOpening_Balance,get Current_Credit,getNotHoli,Not_holi_count
dim getLeave_Taken,getAdded_to_AL,getActive,getappcode ,prev,codeva,curva,curvalue,prevvalue,checksql2,ge tLastHoli,last_holi_count
session.Contents("loginempno")=loginuser

'*************************PREVIOUS YEAR LEAVE DETAILS ***********************
getEmpnum=trim(Request.Form("emplist"))
getappcode=Request.Form("appcode")
getLeaveYear_From ="01-Jul-06"
getLeaveYear_To ="30-Jun-07"
'**********************ENTERING LEAVE DETAILS*******************

for i = 1 to 4
prev = "prev" & i ' input box name
codeva = "code" & i ' input box name
curva= "cur" & i ' input box name curvalue=trim(Request.Form(curva))'*****CURRENT leave year credit
prevvalue= trim(Request.Form(prev))'**** leave taken last year
code=trim(Request.Form(codeva)) '*****leave code

if prevvalue= "" then
prevvalue = 0
end if
if curvalue= "" then
curvalue = 0
end if
Response.Write ("<br />Loop---"& i &"<br />")

'CHECKING IF ANY OF THE LEAVE SELECTED EXISTS FOR THAT PARTICULAR EMPLOYEE

checksql2="select * from LeaveOpeningBalance where EMPNO = '"& getEmpNum &"' and " & _
"leave_code='"& code &"' and record_active='Y'"

set rs7=conn1.execute(checksql2)

if rs7.eof=true then '***********NO RECORDS

'*************INSERTING NEW LEAVE DATA **********************

sql_insert1 = "insert into LeaveOpeningBalance(empno,LeaveYear_From,LeaveYear _To," & _
" Leave_Code,Valid_to_date,Opening_Balance,Current_C redit,Leave_Taken,Added_to_AL, " & _
" Active,CREATED_ON,CREATED_BY,LAST_MODIFYON,LAST_MO DIFYBY,RECORD_ACTIVE ) values " & _
" ('"& getEmpNum &"',to_date('"& getLeaveYear_From &"','DD-Mon-YY'), " & _
" to_date('"& getLeaveYear_To &"','DD-Mon-YY'),'"& code &"'," & _
" '','"& prevvalue &"','"& curvalue &"', " & _
" '0','N','Y','"& TodaysDate &"','"& loginuser &"','"& TodaysDate &"','"& loginuser &"','Y')"
Response.Write ("<br /> INSERTING DATA --" & sql_insert1)
conn1.execute(sql_insert1)


else ' RECORD EXISTS


'*************UPDATING DATA**********************


sqlUP1 = "update LeaveOpeningBalance set OPENING_BALANCE='"& prevvalue &"', " & _
" LAST_MODIFYON='"& TodaysDate &"',LAST_MODIFYBY='"& loginuser &"' " & _
" where EMPNO = '"& getEmpNum &"' and Leave_CODE='"& code &"' " & _
" and LeaveYear_From =to_date('"& getLeaveYear_From &"','DD-Mon-YY') " & _
" and LeaveYear_To=to_date('"& getLeaveYear_To &"','DD-Mon-YY') and record_Active='Y' "
conn1.execute(sqlUP1)
Response.Write ("<br /> UPDATING DATA --" & sqlUP1)


end if

next

conn1.close
conn2.close
%>

<table width="75%" border="0" align="center" cellspacing="0" bordercolor="#336699">
<tr>
<td><div align="center">Thank You for submitting your leave details of previous year and current year..</div></td>
</tr>
<Tr><td align="center"><br><input type="button" value="Back" id=button1 name=ContBtn onClick="javascript:gotoMain();"></td></tr>
</table>
</form>
</body>
Apr 24 '07 #3
devsusen
136 100+
Hi simi,
I have checked ur code. It looks like proper. but one thing I am not getting clearly. why r u running the check, insert or update within a loop for 4 times?

It seems to me that due to this loop if the record for a particular empno does not exists then it inserts the record and then for next 3 times its updating the same record. getEmpnum=trim(Request.Form("emplist")) as this variable is having same value so within the loop its inserting the record for first time and then updating the record for next 3 times.

susen
Apr 24 '07 #4
i have 4 input boxes with the name s
prev1,prev2,prev3,prev4 ..these are values for 4 different type of leaves.

actually i am passing 4 values(ie. 4 different leave types) for same employee.

for each type of leave he entered,there should be one record.
so, before executing the query
first il take leavetype prev1 value and check if there is any record for the employee for that particular leave type.....
if there is no record i will insert new one
if there is record i will just update it...

next i go for the next leavetype prev2 value ...and so on...


prev = "prev" & i '********input box name for leave type
prevvalue= trim(Request.Form(prev)) '****previous year balance

simi

Hi simi,
I have checked ur code. It looks like proper. but one thing I am not getting clearly. why r u running the check, insert or update within a loop for 4 times?

It seems to me that due to this loop if the record for a particular empno does not exists then it inserts the record and then for next 3 times its updating the same record. getEmpnum=trim(Request.Form("emplist")) as this variable is having same value so within the loop its inserting the record for first time and then updating the record for next 3 times.

susen
Apr 25 '07 #5
devsusen
136 100+
ok, I got ur point. Now try to print the checksql2 query if its generated correctly or not.

Expand|Select|Wrap|Line Numbers
  1. checksql2="select * from LeaveOpeningBalance where EMPNO = '"& getEmpNum &"' and leave_code='"& code &"' and record_active='Y'"
in the query u r checking for prev1, prev2,.... but for code.

susen
Apr 27 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Charles Alexander | last post by:
Hello I am new to php & MySQL - I am trying to retrieve some records from a MySQL table and redisplay them. The data in list form looks like this: Sample_ID Marker_ID Variation ...
5
by: build | last post by:
G'day All, I have a problem with this loop. There are a number of .txt files in 'myPath'. tmpFile = Dir(myPath & "\*.txt") 'PROCESS FOLDER Do Until tmpFile = "" <lottsa code> <too much to...
11
by: ritterhaus | last post by:
Just a simple bit of code to toggle between two state at intervals... import time for i in range(4): print 'On' time.sleep(1) print 'Off' time.sleep(1) .... SHOULD toggle On and Off four...
12
by: reynoldscraigr | last post by:
Hi All, hope someone can see what wrong here I have the following function function RemoveMenuFromHoldArray(menuName) { var i = 0; for (i=0;i<=MenusToHoldOpen.length-1;i++) { if...
43
by: Gremlin | last post by:
If you are not familiar with the halting problem, I will not go into it in detail but it states that it is impossible to write a program that can tell if a loop is infinite or not. This is a...
63
by: Aaron Ackerman | last post by:
What is the sytax for exiting a for loop in C#?
15
by: Mike Lansdaal | last post by:
I came across a reference on a web site (http://www.personalmicrocosms.com/html/dotnettips.html#richtextbox_lines ) that said to speed up access to a rich text box's lines that you needed to use a...
2
by: d3vkit | last post by:
Okay so I can NOT get my while loop to work. It's the most confusing thing I've ever come across. It was working fine and then suddenly, nothing. No error. The page just dies. I am using PHP5 with...
5
by: sgurukrupagmailcom | last post by:
Hi, I haven't come accross an elegant solution to a design problem that I show below. Have a look at the piece of code here: class Exc { Exc () { System.out.println ("Haribol"); }
1
by: JavaJon | last post by:
Hello, I'm Jon. I've recently picked up Java after using a "gimmick" programming language called GML ( Game Maker Language ). I've read a lot of tutorials and even a Java for Dummies *.pdf book....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.