update problem help...

[arravalli12]

I have 4 fields : Code, amt, st_date,expr_dte. All are primary keys.
In my program I am dispalying the page with all records. I have an update link to update every record. To update I am passing all fields as querystring and displaying that record in different page.
Now I have the option of updating amt, st_dte and expr_dte in text box.
If I put correct dates and amount,it updates the page.

If I am putting wrong date - It displays the message saying wrong date format.
Now again when I put correct dates it blows the page beocs hidden fields I am passing for amt, st_dte and expr_dte are either taken from request.form which are wrong dates.
Any help will be appreciated.
Thanks in advance.

My code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Server.ScriptTimeout = 1000000 %>
<!--#include virtual="/Connections/Marketing.asp" -->
<!--#include virtual="/Connections/Datacom.asp" -->

<%
If Request.form("Submit") <> "" Then
Dim amt,st_dte,expr_dte,amt1,st_dte1,expr_dte1,cd_no, iSlashPos, eSlashPos
dim emsg(50)
amt = Request.form("amt")

st_dte = trim(Request.Form("st_dte"))

expr_dte = trim(Request.form("expr_dte"))

amt1 = Request.form("amt1")

st_dte1 = trim(Request.Form("st_dte1"))

expr_dte1 = trim(Request.form("expr_dte1"))


cd_no = Request.form("cd_no")
iSlashPos = Instr(st_dte, "/")
eSlashPos = Instr(expr_dte, "/")
If (iSlashPos < 2) Or (iSlashPos > 3) Then
MM_abortEdit = true
emsg(e) = "Start date should be in format mm/dd/yyyy"
e = e + 1
Else
sFirstNum = Left(st_dte, iSlashPos - 1)

If Not IsNumeric(sFirstNum) then
MM_abortEdit = true
emsg(e) = "Start date should be in format mm/dd/yyyy"
e = e + 1
Else
If (cInt(sFirstNum) > 12) Or (cInt(sFirstNum) < 1) Then
MM_abortEdit = true
emsg(e) = "Start date should be in format mm/dd/yyyy"
e = e + 1
End If
End If
End If
'response.write MM_abortEdit

if len(request.form("st_dte")) <> 0 and MM_abortEdit = false then
if not isdate (request.form("st_dte")) then
MM_abortEdit = true
emsg(e) = "Start date should be in format mm/dd/yyyy"
e = e + 1
end if
end if

If (eSlashPos < 2) Or (eSlashPos > 3) Then
MM_abortEdit = true
emsg(e) = "Expiration date should be in format mm/dd/yyyy"
e = e + 1
Else
sFirstNum = Left(expr_dte, eSlashPos - 1)

If Not IsNumeric(sFirstNum) then
MM_abortEdit = true
emsg(e) = "Expiration date should be in format mm/dd/yyyy"
e = e + 1
Else
If (cInt(sFirstNum) > 12) Or (cInt(sFirstNum) < 1) Then
MM_abortEdit = true
emsg(e) = "Expiration date should be in format mm/dd/yyyy"
e = e + 1
End If
End If
End If

if len(request.form("expr_dte")) <> 0 and MM_abortEdit = false then
if not isdate (request.form("expr_dte")) then
MM_abortEdit = true
emsg(e) = "Expiration date should be in format mm/dd/yyyy"
e = e + 1
end if
end if

if MM_abortEdit <> true then
set Doupdate = Server.CreateObject("ADODB.Command")
Doupdate.ActiveConnection = MM_Marketing_STRING
Doupdate.CommandText = "Update dbo.instant_redeemable_coupons Set amt ='" & amt& " ', st_dte ='" & st_dte& " ', expr_dte ='" &expr_dte& "' WHERE cd_no= '"&cd_no&"' and st_dte = '"&st_dte1&"' and amt = '"&amt1&"' and expr_dte = '" &expr_dte1& " '"
response.write Doupdate.CommandText
response.end

Doupdate.CommandType = 1
Doupdate.CommandTimeout = 0
Doupdate.Prepared = true
Doupdate.Execute()
response.write " Record updated"
Response.Redirect("ec_couponRebateExpiration.asp")
end if
end if%>
<%

cd_no = request.querystring("cd_no")
amt1 = request.querystring("amt")
st_dte1 = request.querystring("st_dte")
expDate1 = request.querystring("expr_dte")
select case len(cd_no)
case "4" cd_no = "0" & cd_no
case "3" cd_no = "00" & cd_no
case "2" cd_no = "000" & cd_no
case "1" cd_no = "0000" & cd_no
end select
CodeNumber_str = CStr(cd_no)

%>

<%
Dim Recordset2
Dim Recordset2_numRows
Dim MM_abortEdit
'MM_abortEdit = false

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_Marketing_STRING
Recordset2.Source = "select cd_no,amt,st_dte,expr_dte from dbo.instant_redeemable_coupons where cd_no = '"&CodeNumber_str&"' and st_dte = '"&st_dte1&"' and amt = '"&amt1&"' and expr_dte ='" &expDate1&"'"
response.write recordset2.source
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

%>
<%
if MM_abortEdit= true then
cd_no = Request.form ("cd_no")
amt = Request.Form("amt")
st_dte = Request.Form("st_dte")
expr_dte = Request.Form("expr_dte")
'response.end

MM_abortEdit = false
else
cd_no = (Recordset2.Fields.Item("cd_no").Value)
amt = trim((Recordset2.Fields.Item("amt").Value))
st_dte = trim((Recordset2.Fields.Item("st_dte").Value))
expr_dte = trim((Recordset2.Fields.Item("expr_dte").Value))
end if%>


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Coupon Expiration Update</title>
<body>


<!--#include virtual="/COMMON/DSFRemoteUI/DSF_RemoteUI_inc.asp" -->
<%=top&nonavtop%>
<table width="98%" border="1" align="center">
<tr>
<td><form name="form1" method="post" action="coupon_expire_update.asp">
<p align="center"><strong>Coupon Expiration Update</strong></p>
<font color="#FF0000">
<%
if e > 0 then
for i = 0 to e - 1
response.write "<br>Error - "&emsg(i)
next
end if %>
</font>
<table width="96%" border="1" align="center">
<tr>
<td width="162" bgcolor="#CCCC99"><div align="left">Code</div></td>
<td width="728" ><div align="left"><%=cd_no%></div><input name="cd_no" type="hidden" value="<%=cd_no%>" ></td>
</tr>
<tr>
<td bgcolor="#CCCC99"><div align="left">Amount</div></td>
<td><input name="amt" type="text" value="<%=amt%>" size="20" maxlength="18"></td>
</tr>
<tr>
<td bgcolor="#CCCC99"><div align="left">Start Date (&quot;mm/dd/yyyy&quot </div></td>
<td><input name="st_dte" type="text" value="<%=st_dte%>" size="10" maxlength="10"></td>
</tr>
<tr>
<td bgcolor="#CCCC99"><div align="left">Expiration Date (&quot;mm/dd/yyyy&quot</div></td>
<td><input name="expr_dte" type="text" value="<%=expr_dte%>" size="10" maxlength="10"></td>
</tr>

</table>
<p align="center"><a href="ec_couponRebateExpiration.asp">Coupon Management Page </a></p>
<p align="center">

<input name="amt1" type="hidden" value="<%=amt%>">
<input name="st_dte1" type="hidden" value="<%=st_dte%>">
<input name="expr_dte1" type="hidden" value="<%=expr_dte%>">
<input name = "submit" type="submit" value="Update" >
</p>
</form>
<br></td>
</tr>
<tr> </tr>
</table>
</html>


I do not know how to solve this.

 

[Sheco]

Before even looking at the code I must say that if you are updating your primary keys then perhaps it would be better to choose a different key field.... maybe a simple identity counter?

 

[arravalli12]

I appreciate your answer. I told my DBA to have an autogenerated number but he is reluctant to do that. I can update all fields except code. This code can have more than one record so it cannot be made as primary key. Ideally promotion # or counter # is perfect but he does not want.
Any other way?
Thanks