query in storing the data into SQL

[jaya]

i created one table in SQL with numeric fields.
now i kept some textboxes in my field and when i am trying to store the values from vb.net to sql server.
An error is thrown showing

"Unable to convert varchar to numeric"
now what do i do?
any suggestions
thks

 

[jmcilhinney]

It sounds like the issue is probably in your SqlCommand object. I would guess that either you have used the wrong type in a parameter or, if you are putting the values in the SQL statement explicitly, you have put single quotes around a numeric value, e.g.

UPDATE Table1 SET stringField = 'Hello World', numberField = 0

Note that the string value is quoted but the number is not.

 

[Claude]

It sounds like the issue is probably in your SqlCommand object. I would guess that either you have used the wrong type in a parameter or, if you are putting the values in the SQL statement explicitly, you have put single quotes around a numeric value, e.g.

UPDATE Table1 SET stringField = 'Hello World', numberField = 0

Note that the string value is quoted but the number is not.

I got same problem of above poster.
Mine is even worse cause i'm trying to pass some decimal values in an Update string:
This is how my code looks

Dim MyConn AsNew SqlConnection("Data Source=MyServer;Initial Catalog=MyCatalog;Integrated Security=True")
MyConn.Open()
Dim dPrezzo AsDecimal = CDec(txtPrezzo.Text)
Dim dQ AsDecimal = CDec(txtQ.Text)
If txtScon.Text <> ""Then
Dim dSconto AsDecimal = CDec(txtScon.Text)
EndIf
If txtSel.Text = "0"Then
Dim MyComm AsNew SqlCommand("UPDATE RIGORTMP SET RIGORTMP.R$QTOR = Cast('" & GViewListino.SelectedDataKey.Values(3) & "' as Numeric) ," & _
"RIGORTMP.R$COAR = '" & GViewListino.SelectedDataKey.Values(0) & "' , " & _
"RIGORTMP.R$PREZ = " & dPrezzo & " ," & _
"RIGORTMP.R$DEAR = '" & GViewListino.SelectedDataKey.Values(1) & "' " & _
"WHERE RIGORTMP.R$RIFL = '" & GViewRighe.SelectedValue & "'", MyConn)
End If

Let's say value of dPrezzo (or txtPrezzo.Text) is 3.486 if i try to:
A) Pass Textbox Value (aka a string) i get the infamous unable to convert varchar to numeric
B) Pass dPrezzo Value (aka a Decimal) without '' then i get an error cause the sql statement gets erratic with the "." (3.486)
C) Try to Cast the string value to Decimal (see above)

How am i meant to pass a decimal value in an update string?
Or is it impossible?

Thx in advance for any light at the end of the tunnel xD

 

[Claude]

I got same problem of above poster.
Mine is even worse cause i'm trying to pass some decimal values in an Update string:
This is how my code looks

Dim MyConn AsNew SqlConnection("Data Source=MyServer;Initial Catalog=MyCatalog;Integrated Security=True")
MyConn.Open()
Dim dPrezzo AsDecimal = CDec(txtPrezzo.Text)
Dim dQ AsDecimal = CDec(txtQ.Text)
If txtScon.Text <> ""Then
Dim dSconto AsDecimal = CDec(txtScon.Text)
EndIf
If txtSel.Text = "0"Then
Dim MyComm AsNew SqlCommand("UPDATE RIGORTMP SET RIGORTMP.R$QTOR = Cast('" & GViewListino.SelectedDataKey.Values(3) & "' as Numeric) ," & _
"RIGORTMP.R$COAR = '" & GViewListino.SelectedDataKey.Values(0) & "' , " & _
"RIGORTMP.R$PREZ = " & dPrezzo & " ," & _
"RIGORTMP.R$DEAR = '" & GViewListino.SelectedDataKey.Values(1) & "' " & _
"WHERE RIGORTMP.R$RIFL = '" & GViewRighe.SelectedValue & "'", MyConn)
End If

Let's say value of dPrezzo (or txtPrezzo.Text) is 3.486 if i try to:
A) Pass Textbox Value (aka a string) i get the infamous unable to convert varchar to numeric
B) Pass dPrezzo Value (aka a Decimal) without '' then i get an error cause the sql statement gets erratic with the "." (3.486)
C) Try to Cast the string value to Decimal (see above)

How am i meant to pass a decimal value in an update string?
Or is it impossible?

Thx in advance for any light at the end of the tunnel xD

Nm solved it casting the textbox value to varchar and casting the varchar to decimal.