Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    6

    Unanswered: Error converting data type char to numeric.

    i am attempting to retrieve data from a db and display it on a page. I was having trouble in the beginning with a numeric datatype so i did a cast to char. now i get this error >> Microsoft OLE DB Provider for SQL Server error '80040e07'

    Error converting data type char to numeric.

    BELOW IS THE CODE AND THE STORED PROCEDURE
    ***************************
    CREATE PROCEDURE _SP_ProductSearch_qoh @QTYONHND numeric
    AS
    select cast(@QTYONHND as char(25)) from [TABLE NAME]
    GO
    ***************************
    <%@ Language=VBScript %>
    <% Option Explicit %>
    <%
    Const adVarChar = 200
    'Const adDate = 7
    Const adInteger = 3
    Const adCurrency = 6
    Const adNumeric = 131
    Const adDouble = 5
    Const adChar = 129
    Const adSmallInt = 2
    Const adParamInput = &H0001

    Dim objConn, objConn2
    Dim objCmd, objCmd2
    Dim objRS, objUpd
    Dim iProgId, iPname
    iProgId = Request("progid")
    iPname = Request("progName")

    'Set objConn = CreateObject("ADODB.Connection")
    'objConn.Open Application("Conn_ConnectionString")

    'If Request.Form("Update") = "1" Then

    'Set objCmd=Server.CreateObject("ADODB.Command")
    'objCmd.ActiveConnection=objConn

    'objCmd.CommandText="_SP_UpdateInsertItem"
    'objCmd.CommandType=4

    'objCmd.Parameters.Append objCmd.CreateParameter ("@itemid",adInteger,adParamInput,,Cint(Request.Fo rm("itemid")))
    'objCmd.Parameters.Append objCmd.CreateParameter ("@progid",adInteger,adParamInput,,Cint(Request.Fo rm("progid")))
    'objCmd.Parameters.Append objCmd.CreateParameter ("@margin",adInteger,adParamInput,,Cint(Request.Fo rm("margin")))
    'objCmd.Parameters.Append objCmd.CreateParameter ("@price",adCurrency,adParamInput,,Ccur(Request.Fo rm("price")))
    'objCmd.Parameters.Append objCmd.CreateParameter ("@tax",adDouble,adParamInput,,Cdbl(Request.Form(" tax")))
    'objCmd.Parameters.Append objCmd.CreateParameter ("@points",adInteger,adParamInput,,Cint(Request.Fo rm("points")))
    'objCmd.Parameters.Append objCmd.CreateParameter ("@levels",adVarChar,adParamInput,20,Cstr(Request. Form("levels")))
    'objCmd.Parameters.Append objCmd.CreateParameter ("@sub",adVarChar,adParamInput,20,Cstr(Request.For m("sub")))
    'set objUpd = objCmd.Execute
    'End If

    Set objConn2 = CreateObject("ADODB.Connection")
    objConn2.Open Application("Conn_ConnectionString3")

    Set objCmd2=Server.CreateObject("ADODB.Command")
    objCmd2.ActiveConnection=objConn2

    If iProgId > "0" Then
    '******* STORED PROCEDURE CALL ********
    objCmd2.CommandText="_SP_GetItem"
    objCmd2.CommandType=4
    objCmd2.Parameters.Append objCmd2.CreateParameter ("@progid",adVarChar,adParamInput,4,Cstr(iProgI d))

    set objRS = objCmd2.Execute

    End If
    %>
    <%
    DIM QTYONHND
    If len(trim(Request("QTYONHND"))) = 0 then
    QTYONHND = "%"
    Else
    QTYONHND = "%" & Request("QTYONHND") & "%"
    End if
    Dim objCmd3
    Dim objRS3
    Dim objConn3
    Set objConn3 = CreateObject("ADODB.Connection")
    objConn3.Open Application("Conn_ConnectionString2")
    Set objCmd3=Server.CreateObject("ADODB.Command")
    objCmd3.ActiveConnection=objConn3
    objCmd3.CommandText="_SP_ProductSearch_qoh"
    objCmd3.CommandType=4
    objCmd3.Parameters.Append objCmd3.CreateParameter ("@QTYONHND",adChar,adParamInput,25,QTYONHND)

    set objRS3 = objCmd3.Execute
    %>
    <html>
    <head>
    <title>Programs Master Database</title>
    <LINK REL="stylesheet" type="text/css" HREF="master.css">
    </head>
    <body bgcolor="#9D112F">
    <font size="3" color="white"><b>Program Items for <%=iPname%></b></font>
    <form method="post" action="program_item_insert2.asp" name="myform">
    <table border="1" bordercolor="#9D112F" width="100%" >
    <tr>
    <th align="center" width="2%"><font color="white">Active</font></th>
    <th align="center" width="8%"><font color="white">Item #</font></th>
    <th align="center" width="29%"><font color="white">Description</font></th>
    <th align="center" width="8%"><font color="white">Great Plains Model #</font></th>
    <th align="center" width="7%"><font color="white">Shipping Type</font></th>
    <th align="center" width="3%"><font color="white">Qty on Hand</font></th>
    <th align="center" width="3%"><font color="white">Qty Allctd</font></th>
    <th align="center" width="5%"><font color="white">MSRP</font></th>
    <th align="center" width="5%"><font color="white">Cost</font></th>
    <th align="center" width="5%"><font color="white">Shipping</font></th>
    <th align="center" width="3%"><font color="white">Margin</font></th>
    <th align="center" width="3%"><font color="white">Price</font></th>
    <th align="center" width="3%"><font color="white">Tax</font></th>
    <th align="center" width="3%"><font color="white">Points</font></th>
    <th align="center" width="5%"><font color="white">Levels</font></th>
    <th align="center" width="4%"><font color="white">Substitute Item #</font></th>
    <th align="center" width="4%"><font color="white">Update values</font></th>
    </tr>

    <% Dim counter
    counter = 1


    Do until objRS.EOF

    if counter mod 2 = 0 then
    response.write("<tr bgcolor='white'>")
    else
    response.write("<tr bgcolor='white'>")
    end if %>


    <td align="center"><%If len(objRs.fields("itemactive")) > 0 then %>
    <input name="active" value="1" type="checkbox" checked onclick="window.navigate('program_item_insert.asp? insert=0&itemid=<%=objRs.fields("itemid")%>&progid =<%=iProgId%>&progName=<%=iPname%>&margin=<%=objRS .fields("margin")%>&price=<%=objRS.fields("price") %>&tax=<%=objRS.fields("tax")%>&points=<%=objRS.fi elds("points")%>&levels=<%=objRS.fields("levels")% >&sub=<%=objRS.fields("sub_item")%>');">
    <% else %>
    <!--<input name="active" value="0" type="checkbox" onclick="document.myform.submit();">-->
    <input name="active" value="0" type="checkbox" onclick="window.navigate('program_item_insert.asp? insert=1&itemid=<%=objRs.fields("itemid")%>&progid =<%=iProgId%>&progName=<%=iPname%>&margin=<%=objRS .fields("margin")%>&price=<%=objRS.fields("price") %>&tax=<%=objRS.fields("tax")%>&points=<%=objRS.fi elds("points")%>&levels=<%=objRS.fields("levels")% >&sub=<%=objRS.fields("sub_item")%>');">
    <%end if %>
    <input type="hidden" name="itemid" value="<%=objRs.fields("itemid")%>">
    <input type="hidden" name="progid" value="<%=iProgId%>">
    <input type="hidden" name="progName" value="<%=iPname%>">
    <input type="hidden" name="Update" value=1>
    </td>
    <td align="center"><%=objRS.fields("dummy_model_num")% ></td>
    <td align="center"><%=objRS.fields("description")%></td>
    <td align="center"><%=objRS.fields("gp_model_num")%></td>
    <td align="center"><%=objRS.fields("shipping_type")%></td>
    <td align="center"><%=objRS3.fields("QTYONHND")%></td>
    <td align="center"><%=objRS.fields("q_all")%></td>
    <td align="center"><%=objRS.fields("msrp")%></td>
    <td align="center"><%=objRS.fields("cost")%></td>
    <td align="center"><%=objRS.fields("shipping")%></td>
    <td align="center" bgcolor="FFFF99"><%=objRS.fields("margin")%></td>
    <td align="center" bgcolor="FFFF99"><%=objRS.fields("price")%></td>
    <td align="center" bgcolor="FFFF99"><%=objRS.fields("tax")%></td>
    <td align="center" bgcolor="FFFF99"><%=objRS.fields("points")%></td>
    <td align="center" bgcolor="FFFF99"><%=objRS.fields("levels")%></td>
    <td align="center" bgcolor="FFFF99"><%=objRS.fields("sub_item")%></td>
    <td align="center">
    <%If len(objRs.fields("itemactive")) > 0 then %>
    <img name="UpdateValues" src="/ProgramManagement/images/update_button1.jpg" onclick="popUp('program_item_value_upd.asp?insert= 1&itemid=<%=objRs.fields("itemid")%>&progid=<%=iPr ogId%>&progName=<%=iPname%>&itemNumber=<%=objRS.fi elds("dummy_model_num")%>',400,300,200,200)">
    <%else %>
    <img name="UpdateValues" src="/ProgramManagement/images/update_button1.jpg" onclick="activeAlert()">
    <%end if %>
    </td>
    <%counter = counter +1
    objRS.MoveNext
    Loop %>
    </tr>
    <tr>
    <td colspan=17 align=center height=15></td>
    </tr>
    <tr>
    <td colspan=17 align=center><input type="button" name="Close" value=" Close " onclick="Javascript:self.close();"></td>
    </tr>
    </table>
    <%'Set objRS = nothing
    'objConn.Close
    'Set objConn = nothing %>
    </form>
    <script language="JavaScript">

    function popUp(url,width,height,top,left) {
    var newWin;
    winset="toolbar=0,location=0,directories=0,status= 0,menubar=1,scrollbars=1,resizable=1,width="+ width +",height="+ height +",top="+ top +",left="+ left;
    newWin=window.open(url,"win2",winset);
    newWin.focus();
    }

    function activeAlert() {
    alert("Please activate this item in the program prior to making changes.");
    }


    function updateValue(itemid,progid,progName) {
    //alert("hello, have a nice day!");

    // var itemid;
    // var progid;
    // var progName;
    var margin;
    var price;
    var tax;
    var points;
    var levels;
    var sub;
    var newWin2;
    var url2;
    var winset2;

    // itemid = document.myform.myform.itemid.value;
    // progid = myform.progid.value;
    // progName = myform.progName.value;
    margin = document.myform.margin.value;
    price = document.myform.price.value;
    tax = document.myform.tax.value;
    points = document.myform.points.value;
    levels = document.myform.levels.value;
    sub = document.myform.sub.value;

    url2="program_item_insert.asp?insert=1&itemid="+ itemid +"&progid="+ progid +"&progName="+ progName +"&margin="+ margin +"&price="+ price +"&tax="+ tax +"&points="+ points +"&levels="+ levels +"&sub="+ sub;
    winset2="toolbar=0,location=0,directories=0,status =0,menubar=1,scrollbars=1,resizable=1,width="+ width +",height="+ height +",top="+ top +",left="+ left;
    newWin2=window.open(url2,"win2",winset2);
    // newWin2=window.navigate("program_item_insert.asp?i nsert=1&itemid="+ itemid +"&progid="+ progid +"&progName="+ progName +"&margin="+ margin +"&price="+ price +"&tax="+ tax +"&points="+ points +"&levels="+ levels +"&sub="+ sub);
    newWin2.focus();
    }
    </script>
    </body>
    </html>
    ANY HELP OR INPUT WOULD BE GREATLY APPRECIATED THANK YOU
    Last edited by jmaresca2004; 10-15-04 at 17:18.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    your whole problem revolves around this....
    Code:
    If len(trim(Request("QTYONHND"))) = 0 then
      QTYONHND = "%" 
    Else
      QTYONHND = "%" & Request("QTYONHND") & "%"
    End if
    
    Dim objCmd3
    Dim objRS3
    Dim objConn3
    Set objConn3 = CreateObject("ADODB.Connection")
    objConn3.Open Application("Conn_ConnectionString2")
    Set objCmd3=Server.CreateObject("ADODB.Command")
    objCmd3.ActiveConnection=objConn3
    objCmd3.CommandText="_SP_ProductSearch_qoh"
    objCmd3.CommandType=4
    objCmd3.Parameters.Append objCmd3.CreateParameter ("@QTYONHND",adChar,adParamInput,25,QTYONHND)
    the % symbols can't be converted into a numeric value so when you call the stored procedure that requires a numeric parameter you get your error.

    what you probably want to do is change your stored proc parameter to a varchar value. I'm not sure what your stored proc is meant to do but I would have thought something like this is what your are after...
    Code:
    CREATE PROCEDURE _SP_ProductSearch_qoh @QTYONHND varchar(25)
    AS
    select * from [TABLE NAME] where cast(field as char(25)) like @QTYONHND 
    GO

  3. #3
    Join Date
    Oct 2004
    Posts
    6
    i am uable to change the datatype of numeric for QTYONHND. QTYONHND is used by other applications with a numeric datatype. I need to convert the datatype through a cast. i would have just changed the datatype in the db to a varchar and wouldnt even have to be worrying about this but i have to do a workaround to get the desired results.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Like I said eariler I think you may have missed something at a slightly lower level. What is it you are actually trying to do??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •