- Total Members: 220,321
- Threads: 525,398
- Posts: 977,016
Great community. Great ideas.
Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.
-
November 4th, 2012, 10:22 PM
#1
Unavailable update data
Hi, im unavailable update data and it show display message "Error-Conversion failed when converting date and/or time from character string".
As below is my complete ASP code which is file name plan_adjust.asp and e_plan_adjust.asp, hope can advise,thank you.
Plan_adjust.asp
<%
DIM EmpID,EmpName,ref,SInvID,SInvCategory,SInvCal,ref2 ,ref3,QInvCal,QInvDate,QInvID,QInvCategory,QShortD esc,SQInvID,SQInvCategory,SQQty,QInvPlanQty,RowCou nt
if Request.QueryString("empid") = "" then
EmpID = Request.Form("HEmpID")
EmpName = Request.Form("HEmpName")
EmpLevel = Request.Form("HEmpLevel")
else
EmpName = Request.QueryString("Name")
EmpID = Request.QueryString("empid")
EmpLevel = Request.Form("EmpLevel")
end if
SInvID = Request.Form("SInvID")
SInvCategory = Request.Form("SInvCategory")
SInvCal = Request.Form("SInvCal")
QInvPlanQty = Request.Form("QInvPlanQty")
ref = Request.QueryString("ref")
ref2 = Request.QueryString("ref2")
ref3 = Request.QueryString("ref3")
DIM mySQL,mySQL2,mySQL3,mySQL4,mySQL5,mySQL6
DIM objRS,objRS2,objRS3,objRS4,objRS5,objRS6
'******* Inventory ID SQL Start ****************
mySQL = "SELECT INV_ID FROM OTH_INV_DETAILS ORDER BY INV_ID"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, oConnection
'******* Inventory ID SQL End ****************
'******* Inventory Type SQL Start ****************
mySQL2 = "SELECT INV_TYPE FROM OTH_INV_CATEGORY ORDER BY INV_TYPE"
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open mySQL2, oConnection
'******* Inventory ID SQL End ****************
'******* Inventory Location SQL Start ****************
mySQL3 = "SELECT INV_LOCATION FROM OTH_INV_QTY_LOC GROUP BY INV_LOCATION ORDER BY INV_LOCATION"
Set objRS3 = Server.CreateObject("ADODB.Recordset")
objRS3.Open mySQL3, oConnection
'******* Inventory Location SQL End ****************
'******* Reason ID SQL Start ****************
mySQL5 = "SELECT INV_REASON_ID FROM OTH_INV_REASON WHERE INV_REASON_TYPE = 'PADJ' ORDER BY INV_REASON_ID"
Set objRS5 = Server.CreateObject("ADODB.Recordset")
objRS5.Open mySQL5, oConnection
'******* Reason ID SQL End ****************
mySQL6 = "SELECT INV_VENDOR1,INV_VENDOR2,INV_VENDOR3 FROM OTH_INV_DETAILS GROUP BY INV_VENDOR1,INV_VENDOR2,INV_VENDOR3 ORDER BY INV_VENDOR1,INV_VENDOR2,INV_VENDOR3"
'"SELECT INV_VENDOR1,INV_VENDOR2,INV_VENDOR3,INV_VENDOR4,IN V_VENDOR5 FROM OTH_INV_DETAILS GROUP BY INV_VENDOR1,INV_VENDOR2,INV_VENDOR3,INV_VENDOR4,IN V_VENDOR5 ORDER BY INV_VENDOR1,INV_VENDOR2,INV_VENDOR3 "
Set objRS6 = Server.CreateObject("ADODB.Recordset")
objRS6.Open mySQL6, oConnection
'******* Inventory Query SQL Start ****************
if SInvID <> "" Or SInvCategory <> "" Or SInvCal <> "" Then
mySQL4 = "SELECT ROW_NUMBER() OVER (Order by C.INV_DATE, C.INV_ID, A.INV_TYPE,A.INV_SHORTDESC,C.INV_PLANQTY) AS RowNumber,c.INV_DATE, C.INV_ID, A.INV_TYPE, A.INV_SHORTDESC,C.INV_PLANQTY FROM OTH_INV_DETAILS A JOIN OTH_INV_PLAN c ON c.INV_ID=A.INV_ID WHERE 1=1 "&QInvDate&" "&QInvID&" "&QInvCategory&" ORDER BY C.INV_DATE,C.INV_ID, A.INV_TYPE"
Set objRS4 = Server.CreateObject("ADODB.Recordset")
objRS4.Open mySQL4, oConnection
end if
'******* Inventory Query SQL End ****************
%>
<html>
<body>
<SCRIPT Language = "vbscript">
Function VarCheck(object,row)
Dim OldValue,NewValue
NewValue = Int(object.value)
OldValue = Int(PlanAdjustASP.TPInvQty.value)
if NewValue <> 0 then
if NewValue > OldValue then
object.style.backgroundcolor="#00FF00"
object.style.color="#000000"
else
object.style.backgroundcolor="#FF0000"
object.style.color="#FFFFFF"
end if
else
object.style.backgroundcolor="#FFFFFF"
object.style.color="#000000"
end if
End Function
</SCRIPT>
<p align="center">
<font face="Gill Sans MT" size="6" color ="#C12283">
<b>Plan Adjustment ...</b>
</font>
</p>
<!-- ****** User Menu Start ******************* -->
<table style="font-family: Gill Sans MT" width="200" border="1" cellpadding="1" align="center">
<tr>
<th bgcolor="black" colspan=4><font color="white">User Menu</font></th>
</tr>
<tr bgcolor="black">
<form name="INDEXASP" method="post" action="index.asp">
<input type="hidden" name="HEmpID" value="<% Response.Write EmpID %>" />
<input type="hidden" name="HEmpName" value="<% Response.Write EmpName %>" />
<input type="hidden" name="HEmpLevel" value="<% Response.Write EmpLevel%>" />
<th bgcolor="black" ><input type="submit" style="height: 30px; width: 150px;font-family: Gill Sans MT" name="Submit" value="Index" /></th>
</form>
<form name="LOGOUTASP" method="post" action="login.asp">
<td bgcolor=black >
<input type="submit" style="height: 30px; width: 150px;font-family: Gill Sans MT" name="Submit" value="LogOut" />
</td>
</form>
</tr>
<tr bgcolor=black>
</tr>
</table>
<br>
<!-- ****** User Menu End ******************* -->
<br>
<!-- ****** Inventory Query Start ******************* -->
<table width="200" border="1" cellpadding="1" align="center" style="font-family: Gill Sans MT" >
<form name="PlanAdjustASP" method="post" action="plan_adjust.asp">
<tr>
<th colspan=6>
<input type="hidden" name="HEmpID" value="<% Response.Write EmpID %>" />
<input type="hidden" name="HEmpName" value="<% Response.Write EmpName %>" /><input type="hidden" name="HEmpLevel" value="<% Response.Write EmpLevel%>" />
</th>
</tr>
<tr>
<th bgcolor="#8585A6" colspan=6>
<font face="Gill Sans MT" color ="white">
Plan Adjustment
</font>
</th>
</tr>
<tr>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Inventory ID
</font>
</th>
<th bgcolor="#ECE5B6">
<select name="SInvID">
<option selected></option>
<% DO WHILE NOT objRS.EOF %>
<option><% Response.Write objRS("INV_ID") %></option>
<%
objRS.MoveNext
Loop
%>
</select>
</th>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Inventory Category
</font>
</th>
<th bgcolor="#ECE5B6">
<select name="SInvCategory">
<option selected></option>
<% DO WHILE NOT objRS2.EOF %>
<option><% Response.Write objRS2("INV_TYPE") %></option>
<%
objRS2.MoveNext
Loop
%>
</select>
</th>
</tr>
<tr>
<th align="center" colspan=6>
<input type="submit" style="height: 30px; width: 150px;font-family: Gill Sans MT" name="Submit" value="Query"/>
</th>
</tr>
</form>
<tr>
<th colspan= 6 align="center">
<b><font face="Gill Sans MT" color ="red"><% Response.Write ref %></font>
<font face="Gill Sans MT" color ="blue"><% Response.Write ref2 %></font>
<font face="Gill Sans MT" color ="green"><% Response.Write ref3 %></font>
</b>
</th>
</tr>
</table>
<!-- ****** Inventory Query Start ******************* -->
<br>
<!-- ****** Inventory Table Start ******************* -->
<table width="400" border="1" cellpadding="1" align="center" style="font-family: Gill Sans MT" >
<form name="PlanAdjustASP" method="post" action="e_plan_adjust.asp">
<tr>
<th colspan=7>
<input type="hidden" name="HEmpID" value="<% Response.Write EmpID %>" />
<input type="hidden" name="HEmpName" value="<% Response.Write EmpName %>" /><input type="hidden" name="HEmpLevel" value="<% Response.Write EmpLevel%>" />
</th>
</tr>
<!--<tr>
<th bgcolor="#8585A6" ><font color="white">Date</font></th>
<th colspan = 3 bgcolor="#ECE5B6" align=left><input type ="text" name="TInvDate" value="<% Response.Write QInvDate %>" style="text-align: left" size = 23 maxlength="30"/><font color='Red'>** </font></th>
</tr>-->
<th bgcolor="#8585A6" colspan=1 align="Left">
<font face="Gill Sans MT" color ="White">
Reason Code
</font>
</th>
<th bgcolor="#ECE5B6" colspan=2 align="Left">
<select name="SInvReason">
<option selected></option>
<% DO WHILE NOT objRS5.EOF %>
<option><% Response.Write objRS5("INV_REASON_ID") %></option>
<%
objRS5.MoveNext
Loop
%>
</select>
<font color="Red">** </font>
</th>
</tr>
</th>
</tr>
<th bgcolor ="#8585A6" ><font color="white">Ref_No.</font></th>
<th align="left" bgcolor="#ECE5B6"><input type ="text" name="TREFNO" style="text-align: center" size = 40 /></th>
</th>
</tr>
<tr>
<th bgcolor="#8585A6" colspan=3 align="Left">
<font face="Gill Sans MT" color ="White">
Remark
</font>
</th>
<th bgcolor="#ECE5B6" colspan=4 align="Left">
<textarea rows="2" cols="40" name="TREASONREMARK"></textarea>
</th>
</tr>
<tr>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Select Delete Item
</font>
</th>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Row No
</font>
</th>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Date
</font>
</th>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Inventory ID
</font>
</th>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Inventory Category
</font>
</th>
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Short Desc
</font>
</th>
<!--<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Location
</font>
</th>-->
<!--<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
On Hand Balance
</font>
</th>-->
<th bgcolor="#8585A6">
<font face="Gill Sans MT" color ="white">
Plan Adjust Qty
</font>
</th>
</tr>
<%
if SInvID <> "" Or SInvCategory <> "" Then
If objRS4.EOF AND objRS4.BOF Then
DIM Msg2
Msg2 = "** No Record Found !!!"
Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref=" & Msg2
Else
DO WHILE NOT objRS4.EOF
SQInvDate= objRS4("INV_Date")
SQInvID = objRS4("INV_ID")
SQInvCategory = objRS4("INV_TYPE")
QShortDesc =objRS4("INV_SHORTDESC")
QInvPlanQty= objRS4("INV_PLANQTY")
'SQQty = objRS4("INV_QTY")
Response.Write ("<tr><th>")
Response.Write ("<input type =checkbox name='TDelete'value=")
Response.Write ("TDelete")
Response.Write objRS4("rownumber")
Response.Write ("' value='")
Response.Write objRS4("rownumber")
Response.Write ("' style='text-align: center;color: #0000FF' modifty=modifty />")
Response.Write ("</th><th>")
Response.Write ("<input type =text name='")
Response.Write ("TRowID")
Response.Write objRS4("RowNumber")
Response.Write ("' value='")
Response.Write objRS4("RowNumber")
Response.Write ("' style='text-align: center;color: #0000FF' readonly=readonly />")
Response.Write ("</th><th>")
Response.Write("<input type =text name='SQInvDate'")
Response.Write ("TInvDate")
Response.Write objRS4("RowNumber")
Response.Write ("' value='")
Response.Write objRS4("INV_DATE")
Response.Write ("' style='text-align: center;color: #0000FF' modifty=modifty />")
Response.Write ("</th><th>")
Response.Write ("<input type =text name='")
Response.Write ("TInvID")
Response.Write objRS4("RowNumber")
Response.Write ("' value='")
Response.Write objRS4("INV_ID")
Response.Write ("' style='text-align: left;color: #0000FF' modifty=modifty />")
Response.Write ("</th><th>")
Response.Write ("<input type =text name='")
Response.Write ("TInvCategory")
Response.Write objRS4("RowNumber")
Response.Write ("' value='")
Response.Write objRS4("INV_TYPE")
Response.Write ("' style='text-align: center;color: #0000FF' readonly=readonly />")
Response.Write ("</th><th>")
Response.Write ("<input type =text name='")
Response.Write ("TInvShortDesc")
Response.Write objRS4("RowNumber")
Response.Write ("' value='")
Response.Write objRS4("INV_SHORTDESC")
Response.Write ("' style='text-align: center;color: #0000FF' readonly=readonly />")
'Response.Write ("</th><th>")
'Response.Write ("<input type =text name='")
'Response.Write ("TInvLocation")
'Response.Write objRS4("RowNumber")
'Response.Write ("' value='")
'Response.Write objRS4("INV_LOCATION")
'Response.Write ("' style='text-align: center;color: #0000FF' readonly=readonly />")
'Response.Write ("</th><th>")
'Response.Write ("<input type =text name='")
'Response.Write ("TInvQQty")
'Response.Write objRS4("RowNumber")
'Response.Write ("' value='")
'Response.Write objRS4("INV_QTY")
'Response.Write ("' style='text-align: center;color: #0000FF' readonly=readonly />")
Response.Write ("</th><th>")
Response.Write ("<input type =text name='QInvPlanQty'")
Response.Write ("TInvPlanQty")
Response.Write objRS4("RowNumber")
Response.Write ("' value='")
Response.Write objRS4("INV_PLANQTY")
Response.Write ("' style='text-align: center;color: #0000FF' modifty=modifty />")
RowCount = objRS4("RowNumber")
objRS4.MoveNext
Loop
end if
end if
%>
<tr>
<th colspan=7>
<input type="hidden" name="TVarCheck" value = "<% Response.Write RowCount %>" />
<input type="submit" style="height: 30px; width: 150px;font-family: Gill Sans MT" name="Submit" value="Update"/>
</th></tr>
</form>
</table>
<!-- ****** Inventory Table End ******************* -->
</body>
</html>
<%
oConnection.Close
Set oConnection = Nothing
%>
e_plan_adjust.asp
<%
Dim SystemTime,objRSTime
SystemTime = "SELECT CURRENT_TIMESTAMP AS TIME"
Set objRSTime = Server.CreateObject("ADODB.Recordset")
objRSTime.Open SystemTime, oConnection
DIM EmpID,EmpName,TInvDate,TInvID,TInvCategory,TInvPla nQty,TVarCheck,Counter,SInvReason,SQInvDate,SInvID ,SInvCategory,QInvPlanQty,TREASONREMARK,QTRANSID,E mpLevel
EmpID = Request.Form("HEmpID")
EmpName = Request.Form("HEmpName")
EmpLevel = Request.Form("HEmpLevel")
TVarCheck = CInt(Request.Form("TVarCheck"))
Counter = 1
SQInvDate= Request.Form("SQInvDate")
SInvID = Request.Form("SInvID")
SInvCategory = Request.Form("SInvCategory")
QInvPlanQty= Request.Form("QInvPlanQty")
SInvReason = Request.Form("SInvReason")
TREASONREMARK = Request.Form("TREASONREMARK")
if TRIM(SInvReason) = "" then
DIM Msg2
Msg2 = "** Mandatory Field not filled, please check !!!"
Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref=" & Msg2
else
if SInvReason = "Modifty" then
dim objRSUP,mySQLUP
'TPInvQty = objRS3("INV_PLANQTY")
'TPInvQty = TPInvQty
mySQLUP = "UPDATE OTH_INV_PLAN set INV_DATE = convert(date,Convert(Date,'"&SQInvDate&"'),112),IN V_PLANQTY = convert(float,CONVERT(float,'"&QInvPlanQty&"')) Where INV_ID = '"&SInvID&"'"
Set objRSUP = Server.CreateObject("ADODB.Recordset")
objRSUP.Open mySQLUP, oConnection
dim msg
Msg = "Plan Adjustment Update Completed !"
Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref3=" & Msg
else
if SInvReason = "Delete" then
mySQLD = "Delete FROM OTH_INV_PLAN Where INV_DATE = '"&SQInvDate&"' and INV_PLANQTY = '"&QInvPlanQty&"'"
Set objRSD = Server.CreateObject("ADODB.Recordset")
objRSD.Open mySQLD, oConnection
msg = "Plan Adjustment Deleted !"
Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel & "&ref3=" & Msg
end if
end if
end if
%>
-
November 5th, 2012, 11:36 AM
#2
it seems like you get error which explain a problem... you sending date to query not in correct format... or it is missing value, because default for request is "" and it can not be converted to datetime
GK
__________________________________________________ _____
if you found this post is useful click scale
(right side on this reply ) and agree
-
November 6th, 2012, 03:36 AM
#3
Thank you 
Similar Threads
-
By hawzmolly in forum Microsoft Access Help
Replies: 1
Last Post: October 11th, 2008, 12:32 PM
-
By esskaykay in forum Microsoft Access Help
Replies: 2
Last Post: March 13th, 2006, 03:01 PM
-
By awiezzz in forum ASP Development
Replies: 1
Last Post: October 17th, 2005, 03:37 AM
-
By burhankhan in forum Microsoft IIS
Replies: 2
Last Post: January 6th, 2005, 07:28 PM
-
By The Stoat in forum ASP Development
Replies: 2
Last Post: October 18th, 2004, 10:58 AM
