How to updating to dbf table using in VB.NET

Question

0.00/5 (No votes)

See more:

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 22-Oct-16 22:04pm

Member 10469183

Updated 23-Oct-16 3:33am

Add a Solution

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

OriginalGriff · Answer 1 · 2016-10-22T22:48:00

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

Dave Kreskowiak · Answer 2 · 2016-10-23T03:33:00

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.

Member 10469183 · Answer 3 · 2016-10-26T17:59:00

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

How to updating to dbf table using in VB.NET

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0