How can I edit datagridview by importing csv file into it

rajulama

Hi, I m trying to develop a small application where I have to read the data from csv file, display it in DGV and update the database so that I can execute a query related to the database. My code is as follows:

Public Class Form1
Dim myDA As OleDbDataAdapter
Dim myDataSet As DataSet

Private Sub Form1\_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\\db1.mdb")
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT \* FROM Sheet1", con)
    con.Open()
    myDA = New OleDbDataAdapter(cmd)
    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
    myDataSet = New DataSet()
    myDA.Fill(myDataSet, "MyTable")
    DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
    con.Close()
    con = Nothing
End Sub
Private Sub Button1\_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Me.Validate()
    Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
    Me.myDataSet.AcceptChanges()
End Sub

Private Sub DataGridView1\_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

End Sub

Private Sub Button2\_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\\db1.mdb")
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT \* FROM Query3", con)
    con.Open()
    myDA = New OleDbDataAdapter(cmd)
    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
    myDataSet = New DataSet()
    myDA.Fill(myDataSet, "MyTable")
    DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
    con.Close()
    con = Nothing
End Sub

Private Sub Button3\_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    If OpenFileDialog1.ShowDialog(Me) = DialogResult.OK Then
        Dim fi As New FileInfo(OpenFileDialog1.FileName)
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName
        Dim Con As New OleDbConnection(sConnectionString)
        Dim myDataSet As D

Mycroft Holmes

Other than to make you feel better, why are you using the DGV. It's nice to see the data but I would not include it in the process. I would do the following. Load the data from the CSV into a datatable Use Bulkcopy to push it into the database. This article may help[^] I would never use the jet oledb connection to read the data (just prejudiced I guess). Ah my mistake, you are using Access X| so the jet DB is already required.

Never underestimate the power of human stupidity RAH

rajulama

can i use sqlCommand for access database also? I tried but it didnt support.

Dave Kreskowiak

No, you must use the OleDb classes, or for more generic support, the Odbc class (I don't recommend them) to get to Access database. The Sql classes only work with SQL Server.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008

rajulama

so how can I use bulk copy command for access database

Dave Kreskowiak

You can't. Access doesn't have such a facility.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008