如何在VB.NET中使用dbf表更新 [英] How to updating to dbf table using in VB.NET

问题描述

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

什么我试过了：



i很多次尝试但是无法解决它。请帮我解决这个问题。



谢谢

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

推荐答案

最简单的方法是使用一个DataAdapter和一个SQLCommandBuilder。

准备表格：

The easiest way is to use a DataAdapter and an SQLCommandBuilder.
Prep the table:

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:

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

每次代码经过该循环时，都会添加一堆Parameter对象命令。在第一次传递时，您的命令对象有15个参数。在第二遍，30。在第三遍，45个参数。



你不要在循环中添加参数对象。你将它们添加到循环外部，然后重复使用它们，在循环内部的参数中设置新值。



但是，有一个更容易的方法来做到这一点所有的代码，正如OriginalGriff所解释的那样。

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.

先生，你的Sugeestion是对的，但是我需要从datagrid更新到dbf数据库。您的代码是sqlclient连接的地方没有连接到dbf数据库提供程序，并且我将我的代码sqlclient更改为oledbclient但是有一个错误 - 更新需要有效的UpdateCommand，当传递带有修改行的DataRow集合 请先生解决这个问题，所以回复我。我的代码是在代码更新之后。

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.

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

这篇关于如何在VB.NET中使用dbf表更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！