Cant update my database

[twocool]

i response.write my sql.. the values are passed to nxt page... but it dun want to update my database.... i need yr advice.. pls help me::confused:

this is my codes.. pls help...

approve_request.asp

<script language="javascript">
<!--
function submit_form(ReqNo,ReqStatus,RejRemarks,action)
{
document.form2.ReqNo.value = ReqNo;
document.form2.ReqStatus.value = ReqStatus;
document.form2.RejRemarks.value = RejRemarks;
document.form2.submit();
}
//-->
</script>

<% sql= "SELECT * from Requests WHERE ReqtrEmail = '"&Request.ServerVariables("LOGON_USER")&"@singtel.com' AND ReqStatus = 'New'"
set rs = MSCS.Execute(sql)
%>

<form name="form2" method="post" action="approve_action.asp">
<table width="100%" border="0" cellspacing=0 align="center">
<%Do While Not rs.EOF%>
<tr style="background-color:#EEEEEE;" class="header3">
<td width="15%" height="22">Request No</td>
<td width="25%" height="22">Requestor Name</td>
<td width="15%" height="22">Application System</td>
<td width="20%" height="22">Request Title</td>
<td width="10%" height="22">Request Date</td>
<td width="10%" height="22">Desired Date</td>
<td width="15%" height="22">Action</td>
</tr>

<tr style="background-color:#FFFFCC;" onmouseover="this.style.backgroundColor='#ffcc99';" onmouseout="this.style.backgroundColor='#FFFFCC';" class="text">

<td width="15%"><a href="approve_display.asp?ReqNo=<%=rs("ReqNo")%>" target="_blank", width=500,height=400><%=rs("ReqNo")%></a></td>
<input type="hidden" name="ReqNo" value="<%=rs("ReqNo")%>">
<td width="25%"><span class="txtblack12"><%=rs("Reqtr")%></span></td>

<td width="15%"><span class="txtblack12"><%=rs("AppnId")%></span></td>

<td width="20%"><span class="txtblack12"><%=rs("ReqTitle")%></span></td>

<td width="10%"><span class="txtblack12"><%=Day(rs("ReqDt") )%> /<%=Month(rs("ReqDt") )%> /<%=Year(rs("ReqDt") )%> <%'=rs("ReqDt")%></span></td>

<td width="10%"><span class="txtblack12"><%=Day(rs("DesiredDt") )%> /<%=Month(rs("DesiredDt") )%> /<%=Year(rs("DesiredDt") )%> <%'=rs("DesiredDt")%></span></td>


<td width="15%">
<select name="ReqStatus">
<% sql="Select ReqStatus from ReqStatusRef where SeqNo='2' or SeqNo='98'"
set rs1 = MSCS.Execute(sql)


if not rs1.eof then
do
%>
<option value="<%=rs1("ReqStatus")%>"><%=rs1("ReqStatus")%></option>
<%
rs1.movenext
loop until rs1.eof

end if

%>
</select>
</td>
</tr>

<tr>
<td width="15%">Reasons For Rejection:</td>
<td width="25%">
<textarea rows="3" cols="45" name="RejRemarks" onKeyUp="max_count();" class="txtblack12"></textarea>
</td>
<td width="13%">
<input type="submit" onclick="javascript:submit_form('<%=ReqNo%>','<%=ReqStatus%>','<%=RejRemarks%>','update')" name=Submit value="Submit">
<!--input type="submit" name="Submit" value="Submit"-->
</td>
</tr>
<%rs.MoveNext%>
<%loop%>

</table></form>
<%
rs.close
set rs=nothing
%>


approve.action.asp

<%

'vAppnId = Request("AppnId")

%>

<%
vReqStatus = Request("ReqStatus")
vRejRemarks = Request("RejRemarks")
vReqNo = Request("ReqNo")
if vReqNo <>"" then vReqNo = replace(vReqNo,",","','")
if vReqStatus <>"" then vReqStatus = replace(vReqStatus,",","','")
if vRejRemarks <>"" then vRejRemarks = replace(vRejRemarks,",","','")

sql="EXEC UpdateApprove '" & vReqNo & "','" & vReqStatus & "','" & vRejRemarks & "'"

'Response.Write sql
'Response.End

redirecturl = "approve_request.asp"
Response.redirect redirecturl
%>


i really need yr helps... thanks..

[Roy Sinclair]

You don't have any code in there to actually pass the SQL statement anywhere, you build it all up but then it goes nowhere.

[twocool]

the code is stored in the stored prodecure which is UppdateApprove

this is wat is coded in there....

CREATE PROCEDURE UpdateApprove
@ReqNo char,
@vReqStatus char,
@vRejRemarks char

AS
BEGIN
update Requests set
ReqStatus=@vReqStatus,
RejRemarks=@vRejRemarks
where ReqNo=@ReqNo
END
GO







Approve_action.asp
<% sql= "SELECT * from Requests WHERE ReqtrEmail = '"&Request.ServerVariables("LOGON_USER")&"@singtel.com' AND ReqStatus = 'New'"
set rs = MSCS.Execute(sql)

if not rs.eof then

vReqStatus = Request("ReqStatus")
vRejRemarks = Request("RejRemarks")
vReqNo = Request("ReqNo")
if vReqNo <>"" then vReqNo = replace(vReqNo,",","','")
if vReqStatus <>"" then vReqStatus = replace(vReqStatus,",","','")
if vRejRemarks <>"" then vRejRemarks = replace(vRejRemarks,",","','")

else

sql1="EXEC UpdateApprove '" & vReqNo & "','" & vReqStatus & "','" & vRejRemarks & "'"
MSCS.execute(sql1)

end if

rs.close
set rs=nothing

'Response.Write sql
'Response.End


redirecturl = "approve_request.asp"
Response.redirect redirecturl

PLs HELP ME SOLVE THS PROBLEM

[Roy Sinclair]

Are you getting an error or is it acting like everything worked but the database isn't updated?

[twocool]

the values can pass through the nxt pages.. when i response.write.... but never updates my database...

[Roy Sinclair]

Do you have "on error resume next" in your code do you? If so, are you checking for errors?

[twocool]

ithere arent any errors........ its jst that it cant update database

[Roy Sinclair]

You should either be getting an error or the database should be getting updated unless you've got the code set up so the database update is getting bypassed. Try adding a few response.write tags at critical points to ensure the code is following the path you expect it to.

[twocool]

i printscreen my website.... u see then u can get a clear idea

there is a submit button there
when i response.write in the action page..

its EXEC UpdateApprove 'RQ2003050007',' RQ2003050009','',''

but actual thing

it should show EXEC UpdateApprove 'RQ2003050007' , 'REJECTED','test'


after clickin the first submit button......

pls help me

[twocool]

Roy Sinclair did u get the picture of my problems.. its very urgent... pls do help me solve this problem... i send u my codes and printscreen shots already... pls do help.. thanks..