September 13, 2009 at 2:30 am
when trying to insert a record i get this error but i don't know why =(
Microsoft SQL Server Native Client 10.0 error '80040e07'
Error converting data type nvarchar to float.
/InsertRecordEmpty.asp, line 114
Note: line 114 is MM_editCmd.Execute
here is my code:
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
' IIf implementation
Function MM_IIf(condition, ifTrue, ifFalse)
If condition = "" Then
MM_IIf = ifFalse
Else
MM_IIf = ifTrue
End If
End Function
%>
<%
If (CStr(Request("MM_insert")) = "form1") Then
If (Not MM_abortEdit) Then
'obtenemos el numero de orden mayor antes de insertar
Dim MAXNOORDEN
Dim MAXNOORDEN_cmd
Dim MAXNOORDEN_numRows
Set MAXNOORDEN_cmd = Server.CreateObject ("ADODB.Command")
MAXNOORDEN_cmd.ActiveConnection = MM_GrupoDSN_STRING
MAXNOORDEN_cmd.CommandText = "SELECT MAX(NOORDEN) AS biggerId FROM ORDENES"
MAXNOORDEN_cmd.Prepared = true
Set MAXNOORDEN = MAXNOORDEN_cmd.Execute
MAXNOORDEN_numRows = 0
' execute the insert
Dim MM_editCmd
Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_GrupoDSN_STRING
MM_editCmd.CommandText = "INSERT INTO ORDENES (PROCESO, ORDENDECOMPRA, RELEASE, FACTURADO, FECHAFACTURACION, NOFACTURA, EMBARQUE, FECHAENVIO, LINEADEORDEN, NOORDEN, ATENCION, ORDENANTERIOR, ORDENADOPOR, FECHAANTERIOR, PLANTA, FECHANUEVA, NOMBRECLIENTE, TRABAJO, CLAVEFORMATO, CANTIDAD, ESTATUS, TAMANO, LOTE, MATERIAL, NOTAS, COLOR, TINTAS, PERFORACION, TROQUEL, DOBLES, FOLIO, PEGADO, OTROSACABADOS, CANTMATORDENADO, PRECIO1, TAMANOMATERIAL, PORPZA, PORCIENTO, FORMATOSENTAMANO, PORLOTE, PORMILLAR, PESO, LENGUAJE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, "&(MAXNOORDEN.Fields.Item("biggerId").value+1)&", ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 40, Request.Form("PROCESO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 40, Request.Form("ORDENDECOMPRA")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 255, Request.Form("RELEASE")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 5, 1, -1, MM_IIF(Request.Form("FACTURADO"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 255, Request.Form("FECHAFACTURACION")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 5, 1, -1, MM_IIF(Request.Form("NOFACTURA"), Request.Form("NOFACTURA"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 5, 1, -1, MM_IIF(Request.Form("EMBARQUE"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 202, 1, 255, Request.Form("FECHAENVIO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param9", 202, 1, 40, Request.Form("LINEADEORDEN")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param11", 202, 1, 40, Request.Form("ATENCION")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param12", 5, 1, -1, MM_IIF(Request.Form("ORDENANTERIOR"), Request.Form("ORDENANTERIOR"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param13", 202, 1, 40, Request.Form("ORDENADOPOR")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param14", 135, 1, -1, MM_IIF(Request.Form("FECHAANTERIOR"), Request.Form("FECHAANTERIOR"), null)) ' adDBTimeStamp
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param15", 202, 1, 40, Request.Form("PLANTA")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param16", 135, 1, -1, MM_IIF(Request.Form("FECHANUEVA"), Request.Form("FECHANUEVA"), null)) ' adDBTimeStamp
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param17", 202, 1, 255, Request.Form("NOMBRECLIENTE")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param18", 202, 1, 100, Request.Form("TRABAJO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param19", 202, 1, 40, Request.Form("CLAVEFORMATO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param20", 5, 1, -1, MM_IIF(Request.Form("CANTIDAD"), Request.Form("CANTIDAD"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param21", 202, 1, 10, Request.Form("ESTATUS")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param22", 202, 1, 40, Request.Form("TAMANO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param23", 202, 1, 6, Request.Form("LOTE")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param24", 202, 1, 40, Request.Form("MATERIAL")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param25", 202, 1, 255, Request.Form("NOTAS")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param26", 202, 1, 40, Request.Form("COLOR")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param27", 202, 1, 40, Request.Form("TINTAS")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param28", 5, 1, -1, MM_IIF(Request.Form("PERFORACION"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param29", 5, 1, -1, MM_IIF(Request.Form("TROQUEL"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param30", 5, 1, -1, MM_IIF(Request.Form("DOBLES"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param31", 202, 1, 40, Request.Form("FOLIO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param32", 202, 1, 40, Request.Form("PEGADO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param33", 202, 1, 40, Request.Form("OTROSACABADOS")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param34", 202, 1, 255, Request.Form("CANTMATORDENADO")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param35", 5, 1, -1, MM_IIF(Request.Form("PRECIO1"), Request.Form("PRECIO1"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param36", 202, 1, 40, Request.Form("TAMANOMATERIAL")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param37", 5, 1, -1, MM_IIF(Request.Form("PORPZA"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param38", 5, 1, -1, MM_IIF(Request.Form("PORCIENTO"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param39", 5, 1, -1, MM_IIF(Request.Form("FORMATOSENTAMANO"), Request.Form("FORMATOSENTAMANO"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param40", 5, 1, -1, MM_IIF(Request.Form("PORLOTE"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param41", 5, 1, -1, MM_IIF(Request.Form("PORMILLAR"), 1, 0)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param42", 5, 1, -1, MM_IIF(Request.Form("PESO"), Request.Form("PESO"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param43", 202, 1, 255, Request.Form("LENGUAJE")) ' adVarWChar
response.Write(MM_editCmd.CommandText)
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
[/code]
September 13, 2009 at 6:40 am
Hi phanatico,
posting way over 400 lines of VB code in an SQL forum probably won't find many volunteers to look at it...
You should try to narrow the issue down to the part of the source code that's causing a problem. Based on that, generate a sample environment the explains what you're struggling with. Once you're done with that it should become more visible whether it's an VB or T-SQL issue. Then you can select an appropriate forum and I'll almost guarantee you'll get an answer within a very short period of time.
For details on how to post questions here on SSC please follow the link in my signature.
September 13, 2009 at 7:39 am
It seems you need to find where you are trying to INSERT nvarchar into a float column in the database.
After you find out where that is, you will have to CONVERT or change the datatype so that the nvarchar value will go in.
The Wall
September 13, 2009 at 12:32 pm
well i put less code, i hope this help, and sorry about that, its my first post.
September 13, 2009 at 12:38 pm
Note: the only two columns that are float type in my database are PESO and PRECIO1 but when creating the parameter i put it as adDouble, do i need to convert it anyway?
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param35", 5, 1, -1, MM_IIF(Request.Form("PRECIO1"), Request.Form("PRECIO1"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param42", 5, 1, -1, MM_IIF(Request.Form("PESO"), Request.Form("PESO"), null)) ' adDouble
September 13, 2009 at 1:05 pm
If the database side is recognizing it as nvarchar, then you will have to convert it to float, so it will go in.
The SQL field
CAST (PRECIO1 AS Float)
CONVERT( float, PRECIO1)
The variable in a SQL sproc
CAST (@PRECIO1 AS Float)
CONVERT( float, @PRECIO1)
**When you request.form("PRECIO1"), it is probably a character not a double
The variable on a page -- THIS IS PROBABLY WHAT YOU NEED for in-line SQL
CTYPE (@PRECIO1, Double)
Try it!
The Wall
September 13, 2009 at 1:41 pm
y change to this:
MM_editCmd.CommandText = "INSERT INTO ORDENES (PROCESO, ORDENDECOMPRA, RELEASE, FACTURADO, FECHAFACTURACION, NOFACTURA, EMBARQUE, FECHAENVIO, LINEADEORDEN, NOORDEN, ATENCION, ORDENANTERIOR, ORDENADOPOR, FECHAANTERIOR, PLANTA, FECHANUEVA, NOMBRECLIENTE, TRABAJO, CLAVEFORMATO, CANTIDAD, ESTATUS, TAMANO, LOTE, MATERIAL, NOTAS, COLOR, TINTAS, PERFORACION, TROQUEL, DOBLES, FOLIO, PEGADO, OTROSACABADOS, CANTMATORDENADO, PRECIO1, TAMANOMATERIAL, PORPZA, PORCIENTO, FORMATOSENTAMANO, PORLOTE, PORMILLAR, PESO, LENGUAJE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, "&(MAXNOORDEN.Fields.Item("biggerId").value+1)&", ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CONVERT(float, ?), ?, ?, ?, ?, ?, CONVERT(float, ?), ?)"
i have two date colums i hope thats the problem, could be?
September 13, 2009 at 3:54 pm
An extra field in the INSERT is always a problem!
It shifts the whole statement.
The Wall
September 13, 2009 at 9:52 pm
Hey you were correct, i look the code and i was missing one column in the insert, that resolves the error, but after i get some new errors, then i changed the value of al nvarchar columns to nvarchar(255) and now is working 😀 thanks for your help, I will be looking for continued support because im migrating a system that was using MS Access database to SQL Server 2008, I hope not to be annoying.
you were very helpful 🙂
September 13, 2009 at 10:24 pm
you were correct, i look at the code and i was missing a column in the insert, that corrects the error, after that i get some new errors but I solved it changing all nvarchar columns to nvarchar(255) i think the size was the problem :-D, I'll be here looking for support because i'm migrating an asp system that was working with MS Access database to MS SQL Server 2008, I hope not to be annoying.
thanks
you were very helpful 😀
Alejandro Sánchez Betancourt
Chihuahua Chihuahua México
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply