[RESOLVED] Error converting data type varchar to numeric

hi,please help,when update the sql database have this error message appear.
Error converting data type varchar to numeric.

this is the current code:

Code:

---

sSQL = "Update PartList set PartNo = '" & combopartno.Text & "',Dimension ='" & txtdimension.Text & "',Nominal = '" & txtnominal.Text & "', Unit = '" & Txtunit.Text & "', USL ='" & Txtusl.Text & "' ,LSL = '" & Txtlsl.Text & "',UCL ='" & txtucl.Text & "', LCL = '" & txtlcl.Text & "', UpperRL = '" & txturl.Text & "', LowerRL = '" & txtlrl.Text & "' , SampleSize = '" & Txtsamplesize.Text & "', Decimal = '" & txtdecimal.Text & "', Equip ='" & txtequipment.Text & "', Decimalp = '" & txtdecimalp.Text & "', Range = '" & txtrange.Text & "'where ID = '" & txtid.Text & "'"

---

thanks for help.

It looks like you passing all your values as .text, which will be interpreted as strings. (which is fine if all your fields are varchar, or string type fields)

Before you update the database, output what is being passed to your sSQL parameter and post it in here.

any fields which are numeric will have to be converted as you build your string.

e.g As a quick example if Txtunit.Text is getting passed to a numeric field in your database then you could change it to.

Code:

---

Unit = " & CDec(Txtunit.Text) & "

---

As well as converting Txtunit.Text to a numeric you will also need to remove the single quotes from around CDec(Txtunit.Text) otherwise it will put the number in quotes and still pass it as a string.
(based on .Net code)

Check the data type in the DB.

For some fields the data type is numeric (INT, SMALLINT...etc) and you are inserting a STRING value. That's wht this error.

hi,thanks the previous problem has been solved.but then i having the same error when i try to insert new data to the sql server table.
The UCL and LCL fields is decimal datatype at sql table. so i use CDEC(txtucl.text) and CDEC(txtlcl.text) but the error message problem still appear.

this is the current code:

Code:

---

sSQL = "INSERT INTO PartList " & _
        "(PartNo,Dimension,Nominal,Unit,USL,LSL,UCL,LCL,Range,UpperRL,LowerRL,SampleSize,Decimal,Equip,BinLower,BinNormal,BinUpper,Decimalp)" & _
        "VALUES (" & _
        "'" & Combopartno.Text & "', " & _
        "'" & txtdimension.Text & "', " & _
        "'" & txtnominal.Text & "', " & _
        "'" & txtunit.Text & "', " & _
        "'" & txtusl.Text & "', " & _
        "'" & txtlsl.Text & "', " & _
        "" & CDec(txtucl.Text) & ", " & _
        "" & CDec(txtlcl.Text) & ", " & _
        "'" & txtrange.Text & "', " & _
        "'" & txturl.Text & "', " & _
        "'" & txtlrl.Text & "', " & _
        "'" & txtsamplesize.Text & "', " & _
        "'" & txtdecimal.Text & "', " & _
        "'" & txtequipment.Text & "', " & _
        "'" & txtbinlower.Text & "', " & _
        "'" & txtbinnormal.Text & "', " & _
        "'" & txtbinupper.Text & "', " & _
        "'" & txtdecimalp.Text & "'" & _
        ")"

---

thanks for help.

Can you step through your code and look at the value that sSql ends up with? You should be able to get a SQL statement out of it, have a look at it for errors or paste it here so that we can find mistakes.

Also paste the structure of your PartList table in here also.

hi,when i try to add the new data to sql database table error message "Error converting data type varchar to numeric".

this is the current code:

Code:

---

Private Sub CmdAdd_Click()
 Dim rs As ADODB.Recordset
 Dim rs1 As ADODB.Recordset
Dim conDataConnection As Connection
Dim strsql As String
Dim abc As Integer
Dim list_item As ListItems
Dim sSQL As String
Dim ucl As String
Dim lcl As String

  Dim conn As New ADODB.Connection
  Set conn = New ADODB.Connection
      Set conDataConnection = New Connection
      conn.ConnectionString = "driver={SQL Server};Server=GRACEKUO;Uid=sa;pwd=sa;database=SPC"
 

sSQL = "INSERT INTO PartList " & _        "(PartNo,Dimension,Nominal,Unit,USL,LSL,UCL,LCL,Range,UpperRL,LowerRL,SampleSize,Decimal,Equip,BinLower,BinNormal,BinUpper,Decimalp)" & _
        "VALUES (" & _
        "'" & combopartno.Text & "', " & _
        "'" & txtdimension.Text & "', " & _
        "'" & txtnominal.Text & "', " & _
        "'" & Txtunit.Text & "', " & _
        "'" & Txtusl.Text & "', " & _
        "'" & Txtlsl.Text & "', " & _
        "" & CDec(txtucl.Text) & ", " & _
        "" & CDec(txtlcl.Text) & ", " & _
        "'" & txtrange.Text & "', " & _
        "'" & txturl.Text & "', " & _
        "'" & txtlrl.Text & "', " & _
        "'" & Txtsamplesize.Text & "', " & _
        "'" & txtdecimal.Text & "', " & _
        "'" & txtequipment.Text & "', " & _
        "'" & txtbinlower.Text & "', " & _
        "'" & txtbinnormal.Text & "', " & _
        "'" & txtbinupper.Text & "', " & _
        "'" & txtdecimalp.Text & "'" & _
        ")"
       
conn.Execute sSQL
Debug.Print sSQL
End Sub

---

thanks for help.

The UCL and LCL fields are not the only Decimal fields.. you need to do the same for the others too (such as USL).

thanks the problem have been solved.:thumb: