#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    19
    Rep Power
    0

    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


    %>
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    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 Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    19
    Rep Power
    0
    Thank you

Similar Threads

  1. Create new data, find old data, update old data, save both
    By hawzmolly in forum Microsoft Access Help
    Replies: 1
    Last Post: October 11th, 2008, 01:32 PM
  2. Check box unavailable if...
    By esskaykay in forum Microsoft Access Help
    Replies: 2
    Last Post: March 13th, 2006, 04:01 PM
  3. Replies: 1
    Last Post: October 17th, 2005, 04:37 AM
  4. Server in unavailable
    By burhankhan in forum Microsoft IIS
    Replies: 2
    Last Post: January 6th, 2005, 08:28 PM
  5. Array value unavailable ?
    By The Stoat in forum ASP Development
    Replies: 2
    Last Post: October 18th, 2004, 11:58 AM

IMN logo majestic logo threadwatch logo seochat tools logo