Please help me to solve this Exception error

Question

0.00/5 (No votes)

See more:

The bellow code work all right but after inserting the data it keeps showing this error: Error converting data type varchar to numeric.

Please help.

VB

Imports System.Data.SqlClient

Public Class Form1

    Dim connection As SqlConnection
    Dim ds As DataSet
    Dim dt As DataTable
    Dim da As SqlDataAdapter
    Dim sql As String
    Dim cmb As SqlCommand
    Dim sBuilder As SqlCommandBuilder
    
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    
    
        DataGridView1.Columns(5).DefaultCellStyle.Format = ("n2")
        DataGridView1.Columns(6).DefaultCellStyle.Format = ("n2")
        DataGridView1.Columns(0).DefaultCellStyle.Format = Date.Now
    
    End Sub
    
    
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
        Try
            Dim connetionString As String
            Dim connection As SqlConnection
            Dim adapter As New SqlDataAdapter
            Dim sql As String
            
            
            Dim Ref_No, Account_Code, Account_Name, Details, Debit, Credit As String
            Dim value As String = "2000-02-02"
            Dim Date_ As DateTime = Date.Parse(value)
            
            For i As Integer = 0 To Me.DataGridView1.Rows.Count
            
                Date_ = Me.DataGridView1.Rows(i).Cells(0).Value
                Ref_No = Me.DataGridView1.Rows(i).Cells(1).Value
                Account_Code = Me.DataGridView1.Rows(i).Cells(2).Value
                Account_Name = Me.DataGridView1.Rows(i).Cells(3).Value
                Details = Me.DataGridView1.Rows(i).Cells(4).Value
                Debit = Me.DataGridView1.Rows(i).Cells(5).Value
                Credit = Me.DataGridView1.Rows(i).Cells(6).Value
                
                connetionString = "Data Source=.\sqlexpress;Initial Catalog=financialaccounting;Integrated Security=True"
                connection = New SqlConnection(connetionString)
                sql = "insert into General_Ledger (Date_,Ref_No,Account_Code,Account_Name,Details,Debit,Credit) values('" & Date_ & "','" & Ref_No & "','" & Account_Code & "','" & Account_Name & "','" & Details & "','" & Debit & "','" & Credit & "')"
                
                connection.Open()
                
                
                
                adapter.InsertCommand = New SqlCommand(sql, connection)
                adapter.InsertCommand.ExecuteNonQuery()
            
            
            Next
            
            MsgBox("Row inserted !! ")
        
        Catch oex As Exception
            MsgBox(oex.Message)
        End Try
    
    End Sub
    
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    
        Dim connectionString As String = "Data Source=.\sqlexpress;Initial Catalog=financialaccounting;Integrated Security=True"
        Dim sql As String = "SELECT Date_,Ref_No,Account_Code,Account_Name,Details,Debit,Credit from General_Ledger"
        Dim connection As New SqlConnection(connectionString)
        connection.Open()
        cmb = New SqlCommand(sql, connection)
        da = New SqlDataAdapter(cmb)
        sBuilder = New SqlCommandBuilder(da)
        ds = New DataSet()
        da.Fill(ds, "General_Ledger")
        dt = ds.Tables("General_Ledger")
        connection.Close()
        DataGridView1.DataSource = ds.Tables("General_Ledger")
        DataGridView1.Columns(5).DefaultCellStyle.Format = ("n2")
        DataGridView1.Columns(6).DefaultCellStyle.Format = ("n2")
        DataGridView1.Columns(0).DefaultCellStyle.Format = Date.Now

    End Sub

Posted 26-Aug-13 0:59am

Mathiudi

Updated 26-Aug-13 1:13am

Maarten Kools

v2

Add a Solution

Comments

abbaspirmoradi 26-Aug-13 7:04am

it means you try put wrong type into field with varchar type in your database.check your values that you want insert into database

abbaspirmoradi 26-Aug-13 7:08am

your Date_ value should insert into field with datetime type in your database.check it!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hasan Habib Surzo · Accepted Answer · 2013-08-26T01:13:00

hope your debit and credit both are number. So you can write it like this.

VB

' Debit part
Debit = Me.DataGridView1.Rows(i).Cells(5).Value
' here is the modify version
Debit = ctype(Me.DataGridView1.Rows(i).Cells(5).Value,double)

VB

'Credit part
Credit = Me.DataGridView1.Rows(i).Cells(6).Value
' Here is the modify credit version
Credit = ctype(Me.DataGridView1.Rows(i).Cells(6).Value,double)

I assume debit and credit as double type data but if you use long just use long instead of doulbe in the code.

_Amy · Accepted Answer · 2013-08-26T01:20:00

Since, I don't know the column data types, which you are passing to Sql Server. Here in this case, you obviously have something in varchar column that can't be converted to numeric type. This error message explains it clearly.

The solution is not so tough. You need to check for the following:

Solution:
1. Look at the data.
2. Find the non-numeric values in the varchar column.
3. Change/Remove those values.

You could have searched your error message in Google and fixed it.

--Amit

Please help me to solve this Exception error

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0