• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Error converting varchar to numeric

Hallo,
I am working with SQL and Vb.net. When I run the following function (shown below) an error is highlighted "Error converting varchar to numeric".  Whats wrong with the coding that triggers the error..

Thanks


Dim query As String
        Dim cmd As New SqlCommand
        Dim irow As Integer
 
        For irow = 0 To DgvToPay.Rows.Count - 1
            If DgvToPay.Rows(irow).Cells(3).Value = True Then
 
                Try
                    query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, 
                                   AmounttobePaid) VALUES ('" & _
                            DgvToPay.Rows(irow).Cells(0).Value & "', '" & _
                            DgvToPay.Rows(irow).Cells(1).Value & "', '" & _
                            DgvToPay.Rows(irow).Cells(2).Value & "', '" & _
                            "Yes" & "', '" & _
                            DgvToPay.Rows(irow).Cells(4).Value & "')"
                    cmd = New SqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Information, "Payment System")
                End Try
            End If
        Next irow
    End Sub

Open in new window

0
nick2175
Asked:
nick2175
  • 18
  • 9
  • 7
  • +2
1 Solution
 
Pratima PharandeCommented:
are all the fields in query are varchar ?? prac_no, prac_eid, num_pats, ToPay,
                                   AmounttobePaid ?

  query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay,
                                   AmounttobePaid) VALUES ('" & _
                            DgvToPay.Rows(irow).Cells(0).Value & "', '" & _
                            DgvToPay.Rows(irow).Cells(1).Value & "', '" & _
                            DgvToPay.Rows(irow).Cells(2).Value & "', '" & _
                            "Yes" & "', '" & _
                            DgvToPay.Rows(irow).Cells(4).Value & "')"

If not then remove the '' around them

For ex I have done for prac_eid

  query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay,
                                   AmounttobePaid) VALUES ('" & _
                            DgvToPay.Rows(irow).Cells(0).Value & "', "& _
                            DgvToPay.Rows(irow).Cells(1).Value & ", '" & _
                            DgvToPay.Rows(irow).Cells(2).Value & "', '" & _
                            "Yes" & "', '" & _
                            DgvToPay.Rows(irow).Cells(4).Value & "')"
0
 
thiyagukCommented:
In the Debug Mode , Step into

DgvToPay.Rows(irow).Cells(0).Value
DgvToPay.Rows(irow).Cells(1).Value
DgvToPay.Rows(irow).Cells(2).Value
DgvToPay.Rows(irow).Cells(4).Value

these values, and find any of these values contain nonnumeric values or character?

0
 
nick2175Author Commented:

All the fields are numeric except ToPay is varcahr(50). Also note when I did the change

 query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid)
                                        VALUES ('" & _
                            DgvToPay.Rows(irow).Cells(0).Value & "', " & _
                            DgvToPay.Rows(irow).Cells(1).Value & "', " & _
                            DgvToPay.Rows(irow).Cells(2).Value & "', " & _
                            "Yes" & "', " & _
                            DgvToPay.Rows(irow).Cells(4).Value & "')"

An error is prompted as

Incorrect syntax near ', 9356'.

9356 - is the number populated from the database, when you ok the next number is entered '2000' and so on.

Any ideas..
Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
thiyagukCommented:
Rewrite this as
 query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid)
                                        VALUES ('" & _
                            DgvToPay.Rows(irow).Cells(0).Value & "', '" & _
                            DgvToPay.Rows(irow).Cells(1).Value & "', '" & _
                            DgvToPay.Rows(irow).Cells(2).Value & "', '" & _
                            "Yes" & "', '" & _
                            DgvToPay.Rows(irow).Cells(4).Value & "')"

Open in new window

0
 
thiyagukCommented:
or else use Parameterized Queries :

Refer :
                  http://aspnet101.com/aspnet101/tutorials.aspx?id=1
0
 
nick2175Author Commented:
Thats was the initial code I had. Sorry for the way I presented it.

The problem is still there.

Any more ideas..

Thanks
0
 
thiyagukCommented:
Please post current code here :
0
 
nick2175Author Commented:

query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, 
         ToPay, AmounttobePaid) VALUES ('" & _
                 DgvToPay.Rows(irow).Cells(0).Value & "', '" & _
                 DgvToPay.Rows(irow).Cells(1).Value & "', '" & _
                 DgvToPay.Rows(irow).Cells(2).Value & "', '" & _
                 "Yes" & "', '" & _
                 DgvToPay.Rows(irow).Cells(4).Value & "')"
 

Open in new window

0
 
Pratima PharandeCommented:

query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, 
         ToPay, AmounttobePaid) VALUES (" & _
                 DgvToPay.Rows(irow).Cells(0).Value & ", " & _
                 DgvToPay.Rows(irow).Cells(1).Value & "', " & _
                 DgvToPay.Rows(irow).Cells(2).Value & "', '" & _
                 "Yes" & "', " & _
                 DgvToPay.Rows(irow).Cells(4).Value & "')"

Open in new window

0
 
nick2175Author Commented:
I have made the changes as highlighted by Pratima but the error now is

Incorrect Syntax near ', 9356'.
Unclosed quotation mark after the character string ')'.


9356 - is the number populated from the database, when you ok the next number is entered '2000' and so on. down the column.

Thanks
0
 
EDDYKTCommented:
if all numeric then take out all '

ie

query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay,
                                   AmounttobePaid) VALUES (" & _
                            DgvToPay.Rows(irow).Cells(0).Value & ", " & _
                            DgvToPay.Rows(irow).Cells(1).Value & ", " & _
                            DgvToPay.Rows(irow).Cells(2).Value & ", "' & _
                            "Yes" & "', " & _
                            DgvToPay.Rows(irow).Cells(4).Value & ")"
0
 
nick2175Author Commented:
Almost there with the following error highlighted, any ideas

Incorrect Syntax near ','.

Also note the 3 Column "Yes" - is a varchar all other fields are numeric

Thanks
 query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, 
          ToPay, AmounttobePaid) VALUES (" & _
            DgvToPay.Rows(irow).Cells(0).Value & ", " & _
            DgvToPay.Rows(irow).Cells(1).Value & ", " & _
            DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
            "Yes" & "', " & _
            DgvToPay.Rows(irow).Cells(4).Value & ")"

Open in new window

0
 
Pratima PharandeCommented:
Just debeug and check what you are getting in query after this step
copy that here
0
 
nick2175Author Commented:
The error prompted is

Incorrect Syntax near ','.

Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
 
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
      DgvToPay.Rows(irow).Cells(0).Value & ", " & _
      DgvToPay.Rows(irow).Cells(1).Value & ", " & _
      DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
      "Yes" & "', " & _
       DgvToPay.Rows(irow).Cells(4).Value & ")"
 
            cmd = New SqlCommand(query, conn)
             cmd.ExecuteNonQuery()
 
                Catch ex As Exception
 
          MsgBox(ex.Message,MsgBoxStyle.Information, "ABC System")
 
                End Try
 
            End If
 
        Next irow
 
    End Sub

Open in new window

0
 
Pratima PharandeCommented:
tell me waht you  are getting in

MsgBox(query , "ABC System")
Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
 
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
      DgvToPay.Rows(irow).Cells(0).Value & ", " & _
      DgvToPay.Rows(irow).Cells(1).Value & ", " & _
      DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
      "Yes" & "', " & _
       DgvToPay.Rows(irow).Cells(4).Value & ")"
 MsgBox(query , "ABC System")
 
            cmd = New SqlCommand(query, conn)
             cmd.ExecuteNonQuery()
 
                Catch ex As Exception
 
          MsgBox(ex.Message,MsgBoxStyle.Information, "ABC System")
 
                End Try
 
            End If
 
        Next irow

Open in new window

0
 
nick2175Author Commented:
I get the following error

Incorrect Syntax near ','.


0
 
Pratima PharandeCommented:
try this
Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
 
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
      DgvToPay.Rows(irow).Cells(0).Value & ", " & _
      DgvToPay.Rows(irow).Cells(1).Value & ", " & _
      DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
      "Yes" & "', " & _
       DgvToPay.Rows(irow).Cells(4).Value & ")"
 MsgBox(query , "ABC System")
 
        
                Catch ex As Exception
 
          MsgBox(ex.Message,MsgBoxStyle.Information, "ABC System")
 
                End Try
 
            End If
 
        Next irow

Open in new window

0
 
nick2175Author Commented:
Tried it and the following error was prompted

Conversion from string "ABC System" to type "Integer" is not valid

0
 
Pratima PharandeCommented:

Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
 
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
      DgvToPay.Rows(irow).Cells(0).Value & ", " & _
      DgvToPay.Rows(irow).Cells(1).Value & ", " & _
      DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
      "Yes" & "', " & _
       DgvToPay.Rows(irow).Cells(4).Value & ")"
 MsgBox(query )
 
        
                Catch ex As Exception
 
          MsgBox(ex.Message,MsgBoxStyle.Information, "ABC System")
 
                End Try
 
            End If
 
        Next irow

Open in new window

0
 
nick2175Author Commented:
The following error was prompted -

INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (21, 6437, 44, 'Yes', 22.46)

When you ok it, it brings up the next values and so on.

Any ideas

Thanks

0
 
Pratima PharandeCommented:
Run this
INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (21, 6437, 44, 'Yes', 22.46)

Direclty on database
is it working ?

if yes then this code needs to work
Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
 
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
      DgvToPay.Rows(irow).Cells(0).Value & ", " & _
      DgvToPay.Rows(irow).Cells(1).Value & ", " & _
      DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
      "Yes" & "', " & _
       DgvToPay.Rows(irow).Cells(4).Value & ")"
cmd = New SqlCommand(query, conn)
             cmd.ExecuteNonQuery()
 
 
        
                Catch ex As Exception
 
          MsgBox(ex.Message)
 
                End Try
 
            End If
 
        Next irow

Open in new window

0
 
nick2175Author Commented:
Tried it separetely it works

Tried the new coding, and received the previous error

Incorrect Syntax near ','.


0
 
EDDYKTCommented:
may be


query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay,
                                   AmounttobePaid) VALUES (" & _
                            val(DgvToPay.Rows(irow).Cells(0).Value) & ", " & _
                            val(DgvToPay.Rows(irow).Cells(1).Value) & ", " & _
                            val(DgvToPay.Rows(irow).Cells(2).Value) & ", "' & _
                            "Yes" & "', " & _
                            val(DgvToPay.Rows(irow).Cells(4).Value) & ")"
0
 
Pratima PharandeCommented:
try this
Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
 
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (21, 6437, 44, 'Yes', 22.46)"
 
cmd = New SqlCommand(query, conn)
             cmd.ExecuteNonQuery()
 
 
        
                Catch ex As Exception
 
          MsgBox(ex.Message)
 
                End Try
 
            End If
 
        Next irow

Open in new window

0
 
nick2175Author Commented:

No error is prompted and data is inserted to the database. However, note that it adds 400 records with the same VALUES i.e., ( 21,6437, 44, 'Yes', 22.46).

0
 
Pratima PharandeCommented:
Now only one condition is remains
any one of the value comes null in query

try beloq and observe on which query your are getting the error

Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
 
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
      DgvToPay.Rows(irow).Cells(0).Value & ", " & _
      DgvToPay.Rows(irow).Cells(1).Value & ", " & _
      DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
      "Yes" & "', " & _
       DgvToPay.Rows(irow).Cells(4).Value & ")"
 MsgBox(query )
cmd = New SqlCommand(query, conn)
             cmd.ExecuteNonQuery()
 
 
        
                Catch ex As Exception
 
          MsgBox(ex.Message)
 
                End Try
 
            End If
 
        Next irow

Open in new window

0
 
nick2175Author Commented:
The following error was prompted -

INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (21, 6437, 44, 'Yes', 22.46)

When you ok it, it brings up the next values and so on.


When I remove the 11 line:   MsgBox(query )

The error prompted was

Incorrect Syntax near ','.



0
 
team2005Commented:
Hi!

Try the attached code...
query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
      DgvToPay.Rows(irow).Cells(0).Value & "," & _
      DgvToPay.Rows(irow).Cells(1).Value & "," & _
      DgvToPay.Rows(irow).Cells(2).Value & ",'" & _
      "Yes" & "'," & _
       DgvToPay.Rows(irow).Cells(4).Value & ")"

Open in new window

0
 
nick2175Author Commented:
Tried it before never worked..  The error is the same

Incorrect Syntax near ','.

0
 
team2005Commented:
Hi!

Can you print out the query -> query
MsgBox(query )

Check your field in DB -> ToPay  What filed type is this in database table ?
0
 
nick2175Author Commented:
ToPay field  = Varchar, All other fields are numeric

The query is;

query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
                               DgvToPay.Rows(irow).Cells(0).Value & ", " & _
                               DgvToPay.Rows(irow).Cells(1).Value & ", " & _
                               DgvToPay.Rows(irow).Cells(2).Value & ", '" & _
                               "Yes" & "', " & _
                               DgvToPay.Rows(irow).Cells(4).Value & ")"
                    cmd = New SqlCommand(query, conn)
                    cmd.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Information, "ABC System")
                End Try

0
 
team2005Commented:
Hi!

I want to see what the value of the query is.
Print out the Query you see on screen > MsgBox(query )

Is the field ToPay 3 char long ?

0
 
team2005Commented:
Hi!

INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (21, 6437, 44, 'Yes', 22.46)

Is the field in db table AmounttobePaid a double or real type ?
0
 
nick2175Author Commented:

Quar_id = automated increment set also as a Primary key
ToPay = Varchar
prac_no = numeric
prac_eid = numeric
num_pats = numeric
AmounttobePaid = numeric

Note: ToPay is a checkbox and is always checked onload.

The query dumps all the fields loaded to the DB with  the ToPay checkbox value set to  "Yes".

such as

Quar_id        prac_no        prac_eid        num_pats     ToPay           AmopunttobePaid
30823      1      755258      9367      Yes      233.93
30824      2      667453      7060      Yes      176.50

Please note; The data is inserted to the DB regardless the error.



0
 
nick2175Author Commented:
The error lays in the last code;

cmd = New SqlCommand(query, conn)
                    cmd.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Information, "ABC System")
                End Try


This is where an error exists, but I dont know where

0
 
team2005Commented:
Hi!

AmounttobePaid  cannot be numeric.
Must be double or real.

If i try to put 22.34 into a numeric field in my mysql database
It vil not store that value..

Try to change your field AmounttobePaid  to double.

0
 
nick2175Author Commented:
I am working with SQL server

Changed numeric to decimal(18,2) too no success
any ideas!!
0
 
team2005Commented:
Hi!

Quar_id        prac_no        prac_eid        num_pats     ToPay           AmopunttobePaid
30823      1      755258      9367      Yes      233.93
30824      2      667453      7060      Yes      176.50

Maby the number  prac_eid have to big value ?

Try this:

query = "INSERT INTO xyz.TblQuarPay (prac_no, prac_eid, num_pats, ToPay, AmounttobePaid) VALUES (" & _
                               DgvToPay.Rows(irow).Cells(0).Value & ", " & _
                               DgvToPay.Rows(irow).Cells(1).Value & ", " & _
                               DgvToPay.Rows(irow).Cells(0).Value & ", '" & _
                               "Yes" & "', " & _
                               DgvToPay.Rows(irow).Cells(4).Value & ")"
0
 
nick2175Author Commented:

 I have sorted it there was a problem with

MsgBox(ex.Message, MsgBoxStyle.Information, "ABC System")

greyed it out and it worked well.

Thank you all
0
 
team2005Commented:
Hi!

If you have greyed out the Msgbox.
Then it must be errors with your Query.
Else, the Msgbox wil not be executed.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 18
  • 9
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now