Error converting data type varchar to numeric using POST on a .asp form

Question

I have the following piece of code handling a form using POST on a classic .asp webpage:

<%
update_LotNo = Replace(Request.Form, "&lotno=", ",")
update_LotNo = Replace(strIDs, "lotno=", "")

update_Qty = Replace(Request.Form, "&qty=", ",")
update_Qty = Replace(strIDs, "qty=", "")

update_Building = Replace(Request.Form, "&bldng=", ",")
update_Building = Replace(strIDs, "bldng=", "")

set DoModify = Server.CreateObject("ADODB.Command")
DoModify.ActiveConnection = MM_StockAccDB_STRING
DoModify.CommandText = "UPDATE tbl_stock_at_locations SET lotno_sal = '" & update_LotNo & "%', building_sal = '" & update_Building & "%', qty_sal = '" & update_Qty & "%' WHERE Lotno_SAL LIKE '" & update_LotNo & "%'"
'DoDelete.CommandType = 1
'DoDelete.CommandTimeout = 0
'DoDelete.Prepared = true
DoModify.Execute()
Response.Redirect("coe_in2.asp?8812901")
%>

This is the error message I get:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.

Obviously I'd like as the end result to update the record with the form strings.

I'm new to SQL, so I apologise if this is a very simple question...

Can you provide the DDL for your tbl_stock_at_locations table? I suspect that one of the fields you're touching is a numeric type and you're either trying to set it as or compare it to a varchar value. — 3N1GM4
Also, add response.write DoModify.CommandText before DoModify.Execute(), to see what is the generated query. You have non numeric values for sure in a numeric field. — DanB

John John · Accepted Answer · 2018-10-27T14:14:03

Looking at the first part of your update query

"UPDATE tbl_stock_at_locations SET lotno_sal = '" & update_LotNo & "%',

I'm pretty sure that % symbol shouldn't be there, The % is a wildcard which you use to look up rows, you don't want to actually write it to the field. I'd lose the single quotes too, and VBScript has a function which converts strings to numbers, cint().

Putting all this together, and assuming that qty_sal is also a numeric field, change your query to

"UPDATE tbl_stock_at_locations SET lotno_sal = " & cint(update_LotNo) & ", building_sal = '" & update_Building & "', qty_sal = " & cint(update_Qty) & " WHERE Lotno_SAL LIKE '" & update_LotNo & "%'"

I also recommend you take a look some tutorials involving databases and classic asp rather than using Dreamweaver to generate your code, DW code is awful. If this is customer facing then you should research parameterised queries, otherwise you could be vulnerable to a SQL injection attack

Error converting data type varchar to numeric using POST on a .asp form

1 Answers