Start Free Trial

asked on

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

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 & "')"

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?

ASKER

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

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

or else use Parameterized Queries :

Refer :
http://aspnet101.com/aspnet101/tutorials.aspx?id=1

ASKER

Thats was the initial code I had. Sorry for the way I presented it.

The problem is still there.

Any more ideas..

Thanks

Please post current code here :

ASKER

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

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

ASKER

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

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 & ")"

ASKER

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

Just debeug and check what you are getting in query after this step
copy that here

ASKER

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

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

ASKER

I get the following error

Incorrect Syntax near ','.

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

ASKER

Tried it and the following error was prompted

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

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

ASKER

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

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

ASKER

Tried it separetely it works

Tried the new coding, and received the previous error

Incorrect Syntax near ','.

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) & ")"

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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).

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

ASKER

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 ','.

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

ASKER

Tried it before never worked.. The error is the same

Incorrect Syntax near ','.

Hi!

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

Check your field in DB -> ToPay What filed type is this in database table ?

ASKER

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

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 ?

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 ?

ASKER

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.

ASKER

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

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.

ASKER

I am working with SQL server

Changed numeric to decimal(18,2) too no success
any ideas!!

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 & ")"

ASKER

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

Hi!

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