dcsimg
Results 1 to 16 of 16

Thread: Conversion varchar to numeric Error

  1. #1

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Conversion varchar to numeric Error

    Please help with this code. Im trying to do a fixed Asset program.
    Code:
    Private Sub BtnInsertar_Click(sender As Object, e As EventArgs) Handles btnInsertar.Click
            If (Me.txtCodigoInterno.Text = "") Then
                MsgBox("El campo idententificacion no puede estar vacio", MsgBoxStyle.Critical, "Atencion")
                Me.txtCodigoInterno.Select()
            Else
                Dim CodigoInterno As Integer
                Dim NumerodeSerie As Integer
                Dim NumerodeFactura As Integer
                Dim FechadeCompra As Date
                Dim Precio As Decimal
                Dim Acargode As String = ""
                Dim Estado As String = ""
                Dim Depreciacion As Decimal
                Dim Caracteristicas As String = ""
    
    
                CodigoInterno = Me.txtCodigoInterno.Text
                NumerodeSerie = Me.txtNumerodeSerie.Text
                NumerodeFactura = Me.txtNumerodeFactura.Text
                FechadeCompra = Me.DateTimePicker1.MinDate
                Precio = CDec(txtPrecio.Text)
                Acargode = Me.txtACargode.Text
                Estado = Me.txtEstado.Text
                Depreciacion = CDec(txtDepreciacion.Text)
                Caracteristicas = Me.txtCaracteristicas.Text
    
                cmd.CommandType = CommandType.Text
                cmd.Connection = conn
    
                sql = "INSERT INTO [Equipos de Computacion] (CodigoInterno, NumerodeSerie, NumerodeFactura, FechadeCompra, Precio, Acargode, Estado, Caracteristicas, Depreciacion) "
                sql += "Values('" & CodigoInterno & "','" & NumerodeSerie & "','" & NumerodeFactura & "','" & FechadeCompra & "','" & CDec(Precio) & "','" & Acargode & "','" & Estado & "','" & Caracteristicas & "','" & CDec(Depreciacion) & ")"
                MsgBox(sql)
                cmd.CommandText = sql
                Try
                    cmd.ExecuteNonQuery()
                    MsgBox("Registro insertado correctamente")
    
                Catch ex As Exception
    
                    If ex.ToString.Contains("duplicate") Then
                        MsgBox("El registro ya existe en la base de datos")
                    Else
                        MsgBox(ex.ToString)
                    End If
                End Try
            End If
        End Sub
    Last edited by Shaggy Hiker; Aug 22nd, 2019 at 09:05 AM. Reason: Added CODE tags.

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,322

    Re: Conversion varchar to numeric Error

    Look at your table definitions and the data you are trying to insert. The message is absolutely clear; the program is trying to insert character data into a numeric field. Make sure the data types being passed to SQL match the table definitions.

    https://www.mssqltips.com/sqlservert...in-sql-server/
    Please remember next time...elections matter!

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,090

    Re: Conversion varchar to numeric Error

    Welcome to the forums. I edited your post to wrap the code in [CODE][/CODE] tags. You can do this by pressing the # button and pasting the code between the resulting tags, or paste in the code first, then highlight it and press the # button.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Re: Conversion varchar to numeric Error

    Thank u.

  5. #5

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Re: Conversion varchar to numeric Error

    Quote Originally Posted by TysonLPrice View Post
    Look at your table definitions and the data you are trying to insert. The message is absolutely clear; the program is trying to insert character data into a numeric field. Make sure the data types being passed to SQL match the table definitions.

    https://www.mssqltips.com/sqlservert...in-sql-server/
    Name:  2019-08-22 (1).jpg
Views: 43
Size:  26.7 KB

  6. #6

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Re: Conversion varchar to numeric Error

    Quote Originally Posted by TysonLPrice View Post
    Look at your table definitions and the data you are trying to insert. The message is absolutely clear; the program is trying to insert character data into a numeric field. Make sure the data types being passed to SQL match the table definitions.

    https://www.mssqltips.com/sqlservert...in-sql-server/
    Code:
    [Bmports System.Data.SqlClient
    Public Class Equipos_de_Computacion
        Dim sql As String = ""
    
    
        Private Sub BtnInsertar_Click(sender As Object, e As EventArgs) Handles btnInsertar.Click
            If (Me.txtCodigoInterno.Text = "") Then
                MsgBox("El campo idententificacion no puede estar vacio", MsgBoxStyle.Critical, "Atencion")
                Me.txtCodigoInterno.Select()
            Else
                Dim CodigoInterno As Integer
                Dim NumerodeSerie As Integer
                Dim NumerodeFactura As Integer
                Dim FechadeCompra As Date
                Dim Precio As Decimal
                Dim Acargode As String = ""
                Dim Estado As String = ""
                Dim Depreciacion As Decimal
                Dim Caracteristicas As String = ""
    
    
                CodigoInterno = Me.txtCodigoInterno.Text
                NumerodeSerie = Me.txtNumerodeSerie.Text
                NumerodeFactura = Me.txtNumerodeFactura.Text
                FechadeCompra = Me.DateTimePicker1.MinDate
                Precio = CDec(txtPrecio.Text)
                Acargode = Me.txtACargode.Text
                Estado = Me.txtEstado.Text
                Depreciacion = CDec(txtDepreciacion.Text)
                Caracteristicas = Me.txtCaracteristicas.Text
    
                cmd.CommandType = CommandType.Text
                cmd.Connection = conn
    
                sql = "INSERT INTO [Equipos de Computacion] (CodigoInterno, NumerodeSerie, NumerodeFactura, FechadeCompra, Precio, Acargode, Estado, Caracteristicas, Depreciacion) "
                sql += "Values('" & CodigoInterno & "','" & NumerodeSerie & "','" & NumerodeFactura & "','" & FechadeCompra & "','" & CDec(Precio) & "','" & Acargode & "','" & Estado & "','" & Caracteristicas & "','" & CDec(Depreciacion) & ")"
                sql += "SELECT CAST(LEFT(Precio, CHARINDEX(',', Precio) - 1) + ',' + SUBSTRING(Depreciacion,(CHARINDEX(',',Depreciacion)+1),3) AS DECIMAL(22,8)) CastedNumeric
    	          FROM [Equipos de Computacion]"
                MsgBox(sql)
                cmd.CommandText = sql
                Try
                    cmd.ExecuteNonQuery()
                    MsgBox("Registro insertado correctamente")
    
                Catch ex As Exception
    
                    If ex.ToString.Contains("duplicate") Then
                        MsgBox("El registro ya existe en la base de datos")
                    Else
                        MsgBox(ex.ToString)
                    End If
                End Try
            End If
        End Sub
    Attached Images Attached Images  
    Last edited by Augusto100; Aug 22nd, 2019 at 12:29 PM.

  7. #7

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Re: Conversion varchar to numeric Error

    Quote Originally Posted by TysonLPrice View Post
    Look at your table definitions and the data you are trying to insert. The message is absolutely clear; the program is trying to insert character data into a numeric field. Make sure the data types being passed to SQL match the table definitions.

    https://www.mssqltips.com/sqlservert...in-sql-server/
    Name:  2019-08-22 (1).jpg
Views: 81
Size:  26.7 KB
    Attached Images Attached Images  

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,322

    Re: Conversion varchar to numeric Error

    Maybe someone else can see that but I can't. Here is my suggestion...

    Go to the immediate window, key in ?sql, and press enter. Take the SQL that was in that variable and go to enterprise manager and try and run it (you should get the same error). That should take the mystery out of it.
    Please remember next time...elections matter!

  9. #9

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Re: Conversion varchar to numeric Error

    Please someone help me.

  10. #10
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    789

    Re: Conversion varchar to numeric Error

    No one can read the screenshots you posted to see what the data types are in your database. That being said, no one should have to for you.

    You have the data types in front of you. You have the variable declarations in front of you. Make sure that a string variable in your VB code is being put into a string/char field in your database, and that a numerical variable in your VB code is being put into a numeric field in your database.

  11. #11

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Re: Conversion varchar to numeric Error

    Quote Originally Posted by OptionBase1 View Post
    No one can read the screenshots you posted to see what the data types are in your database. That being said, no one should have to for you.

    You have the data types in front of you. You have the variable declarations in front of you. Make sure that a string variable in your VB code is being put into a string/char field in your database, and that a numerical variable in your VB code is being put into a numeric field in your database.
    My datatable its ok, the problem is that I cant insert decimal values in my Datatable, I got no problem when I use Cint the registration runs good when I do it.

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,090

    Re: Conversion varchar to numeric Error

    Quote Originally Posted by Augusto100 View Post
    My datatable its ok, the problem is that I cant insert decimal values in my Datatable, I got no problem when I use Cint the registration runs good when I do it.
    That suggests that the field is type int, and there do appear to be a few of those in the image, but I can't read it, either. Putting a decimal into an int field won't work. Another possible issue might be dependent on the decimal separator. If you use the comma as a decimal separator, that could be an issue, but it should be an issue when you call CDec on the textbox, not on the insert.

    Using parameters might fix the issue. It's a good idea anyways.
    My usual boring signature: Nothing

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,192

    Re: Conversion varchar to numeric Error

    Multiple people have told you they can't read the table definition from the images you posted and this information is necessary for us to help you. If you can't provide a better image then simply type the table definition by hand.

  14. #14

    Thread Starter
    Junior Member Augusto100's Avatar
    Join Date
    Aug 2019
    Location
    Bolivia
    Posts
    16

    Re: Conversion varchar to numeric Error

    codigointerno int, numerodeSerie int, numerodefactura int, FechadeCompra date, Precio money, acargode nvarchar(50), Estado nvarchar(50), Depreciacion decimal(18,0), caracteristicas nvarchar(50)

  15. #15
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    789

    Re: Conversion varchar to numeric Error

    In your code you have a msgbox to display the SQL command right after your SQL statement is built. Did you notice that ALL values in your Insert statement are surrounded by single quotes? Well, except for the last value, which you appear to add an opening single quote but then neglect to add a closing single quote.

    But quoting around values, you do that for data going in to String/Char fields, but you don't do that for data going into numeric fields.

  16. #16
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,090

    Re: Conversion varchar to numeric Error

    Switching to parameters would solve that issue, also, as you wouldn't need to worry about whether it has single quotes or not.

    Having looked at the query and the code a bit more closely, you have a lot of weird/bad stuff going on with data types. You really need to turn Option Strict ON and fix all the errors it reports, as there will be several of them. In some cases, you were using CDec to convert what looked like a textbox into a decimal. CDec will throw an exception if the string cannot be FULLY converted, which may be the case with user input. However, you are converting that one. In some other lines, you are taking the text from a textbox and putting it into an integer variable. That has the same problem that CDec has, but is also slower, since it's an implicit conversion. The best solution for those is to use a NumericUpDown control rather than a textbox, as a textbox ONLY outputs strings, whereas the .Value property of a NUD is a decimal already, so no conversion to decimal is needed, and CInt will safely convert the .Value to an integer.

    Second, you are doing some odd work with the decimals in the query, where you are treating them as if they were strings. If that works, it works because they are being cast as strings, but they are decimals. You would probably get better performance doing that work before the SQL. They aren't strings anyways.

    Third, you are directly concatenating what looks like a user supplied string into the SQL string. This leaves you open to SQL injection attacks. Parameters would prevent that, and would take care of the fact that you are wrapping decimals in single quotes. Using parameters, the variables will be passed as they should be, wrapped or not as they need.

    Fourth, and least significant is that you are calling CDec() on a Decimal variable. This does no harm, nor does it do any good, but it does suggest that you aren't keeping track of what data types you are working with. Option Strict ON would help with that, as it would prevent the implicit conversions (slow and unsafe) that you are using, and force you to do explicit conversions (faster, and safer).
    My usual boring signature: Nothing

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width