Click here to Skip to main content
15,567,415 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
C#
After many time found a code for inserting datatable to dbf but yet this code some error type An unhandled exception of type 'System.StackOverflowException' occurred in System.Data.dll. Then How to Solve this error.

Dim DtGrid As DataTable
    DtGrid = CType(dbfdatagrid.DataSource, DataTable).Copy()
    Dim ConnectionString1 As String
    ConnectionString1 = "Provider=vfpoledb.1;Data Source=C:\dbf_folder1;Collating Sequence=machine"
    Dim insertstatement As String = "Select * from area"
    dBaseConnection1 = New System.Data.OleDb.OleDbConnection(ConnectionString1)
    Dim instertcommand As OleDbCommand = New OleDbCommand("Insert INTO area (AREAID, SLNO, NEWSLNO, HOUSENO, NAME, SURNAME, RTYPE, RNAME, RSURNAME, AGE, SEX, ACLISTNO, IDCARDNO, STATUSTYPE, ACPARTNO) VALUES (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m,@n,@o);", dBaseConnection1)
    If (dBaseConnection1.State) = 0 Then dBaseConnection1.Open()
    Dim i As Integer = 0
    For Each datarow In DtGrid.Rows
        instertcommand.Parameters.AddWithValue("@a", dbfdatagrid.Rows(i).Cells(0).Value.ToString)
        instertcommand.Parameters.AddWithValue("@b", dbfdatagrid.Rows(i).Cells(1).Value.ToString)
        instertcommand.Parameters.AddWithValue("@c", dbfdatagrid.Rows(i).Cells(2).Value.ToString)
        instertcommand.Parameters.AddWithValue("@d", dbfdatagrid.Rows(i).Cells(3).Value.ToString)
        instertcommand.Parameters.AddWithValue("@e", dbfdatagrid.Rows(i).Cells(4).Value.ToString)
        instertcommand.Parameters.AddWithValue("@f", dbfdatagrid.Rows(i).Cells(5).Value.ToString)
        instertcommand.Parameters.AddWithValue("@g", dbfdatagrid.Rows(i).Cells(6).Value.ToString)
        instertcommand.Parameters.AddWithValue("@h", dbfdatagrid.Rows(i).Cells(7).Value.ToString)
        instertcommand.Parameters.AddWithValue("@i", dbfdatagrid.Rows(i).Cells(8).Value.ToString)
        instertcommand.Parameters.AddWithValue("@j", dbfdatagrid.Rows(i).Cells(9).Value.ToString)
        instertcommand.Parameters.AddWithValue("@k", dbfdatagrid.Rows(i).Cells(10).Value.ToString)
        instertcommand.Parameters.AddWithValue("@l", dbfdatagrid.Rows(i).Cells(11).Value.ToString)
        instertcommand.Parameters.AddWithValue("@m", dbfdatagrid.Rows(i).Cells(12).Value.ToString)
        instertcommand.Parameters.AddWithValue("@n", dbfdatagrid.Rows(i).Cells(13).Value.ToString)
        instertcommand.Parameters.AddWithValue("@o", dbfdatagrid.Rows(i).Cells(14).Value.ToString)
        'instertcommand.Parameters.AddWithValue("@p", dbfdatagrid.Rows(i).Cells(15).Value.ToString)
        i = i + 1
    Next
    instertcommand.Connection.Open()
    instertcommand.ExecuteNonQuery()
    instertcommand.Connection.Close()


What I have tried:

i am tried at many time but couldn't solved it. please help me for this issue of problem.

Thank You
Posted
Updated 23-Oct-16 4:33am

The easiest way is to use a DataAdapter and an SQLCommandBuilder.
Prep the table:
VB
Dim dt As New DataTable()
	dt.Columns.Add("EmpId", GetType(Integer))
	dt.Columns.Add("EmpName", GetType(String))
	dt.Columns.Add("StartDate", GetType(DateTime))
	dt.Columns.Add("DeptNo", GetType(Integer))
	dt.Rows.Add(103, "Joe Smith", DateTime.Now, 129)
	dt.Rows.Add(104, "Mike Jones", DateTime.Now, 130)
And insert:
VB
Using con As New SqlConnection(strConnect)
    Using da As New SqlDataAdapter("SELECT EmpId, EmpName, StartDate, DeptNo FROM MyTable", con)
        Dim cmdb As New SqlCommandBuilder(da)
        da.InsertCommand = cmdb.GetInsertCommand()
        da.Update(dt)
    End Using
End Using
 
Share this answer
 
v2
Every time your code goes through that loop, you're adding a bunch of Parameter objects to the command. On the first pass, your command object has 15 parameters. On the second pass, 30. On the third pass, 45 parameters.

You do NOT add the parameter objects in a loop. You add them ONCE outside of the loop and then reuse them, setting new values in the parameters, inside the loop.

But, there's an easier way to do this without all the code, as explained by OriginalGriff.
 
Share this answer
 
Sir, Your Sugeestion is right, But i was require update from datagrid to dbf database. where your code is sqlclient connection is not connect to dbf database provider and there i was change my code sqlclient to oledbclient but there have a error like - "Update requires a valid UpdateCommand when passed DataRow collection with modified rows.", please sir anybody solve this problem, so reply me. My Code is after update before code.
C#
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=vfpoledb.1;Data Source=C:\dbf_folder;Collating Sequence=machine;"
        con.Open()
        ds.Tables.Add(dt)
        da = New OleDbDataAdapter("Select * from area.dbf", con)
        Dim cb = New OleDbCommandBuilder(da)
        cb.QuotePrefix = "["
        cb.QuoteSuffix = "]"
        da.Fill(dt)
        dt.Merge(dt1)
        dbfdatagrid.DataSource = dt.DefaultView
        con.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        con1.Close()
        con1.ConnectionString = "Provider=vfpoledb.1;Data Source=C:\dbf_folder1;Collating Sequence=machine;"
        con1.Open()
        da1 = New OleDbDataAdapter("Select * from area.dbf", con1)
        Dim columns(5) As DataColumn
        columns(4) = dt.Columns("NAME")
        dt.PrimaryKey = columns
        da1.Fill(dt1)
        da1.Update(dt)
    End Sub
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900