(Resolved) CSV > Datagridview > SQL Server 2008

[Elfish]

I have a form that needs to pull a CSV file into a datagridview, which I have working OK with the code below.

Private Sub FillDataGridviewWithCSV()
        Dim strDirectory = Path.GetDirectoryName(ofdCSV.FileName)
        Dim conString As String = String.Format(ProviderString, strDirectory)
        ' I extracted the provider string to a separate 
        ' sub to make this post a little easier to read
        ' on the forum. The string looks like this:
        ' "Provider=Microsoft.Jet.OLEDB.4.0; _
        ' Data Source={0};Extended Properties=Text;""
 
        Using con As New OleDbConnection(conString)
            con.Open()
            Dim cmd As New OleDbCommand(SQLString(ofdCSV.FileName), con)
            Using da As New OleDbDataAdapter() With {.SelectCommand = cmd}
                Using ds As New DataSet()
                    da.Fill(ds, "Text")
                    DataGridView1.DataSource = ds.Tables(0).DefaultView
                End Using
            End Using
            con.Close()
        End Using
    End Sub

Now that I have it in a datagridview I need allow the user to press a button and push it into SQL server. I know it can be done with this but I am not sure how to take what I have in the DataGridview, the DataAdapter or the DataSet into a DataReader.

Private Sub SQLBulkInsert(ByVal dataReader As SqlDataReader)
        Dim sqlCon As String = My.Settings.MyCon
        Using bc As New SqlBulkCopy(sqlCon) _
        With {.DestinationTableName = "VendorInvoices"}
            bc.WriteToServer(dataReader)
        End Using
    End Sub

I have tried many different variations on converting what is in the DataGrid (or what is feeding it) into somthing I can push into the BulkCopy but have not got it to work right.

Can somebody point me in the right direction?

[jmcilhinney]

You've bound a DataTable to your grid. The WriteToServer method takes a DataTable as an argument.

[Elfish]

Got it. For those of you following along I added this Private dt As DataTable to the top of the form and then modified the Using ds block like this dt = ds.Tables(0)

'
        Using ds As New DataSet()
            da.Fill(ds, "Text")
            dt = ds.Tables(0)
            dgvShowGLA.DataSource = ds.Tables(0).DefaultView
        End Using

Then all I had to do was call it with my Export button with this SQLBulkInsert(dt)

jmcilhinney you said that I had already bound a DataTable to the grid, which I don't see on the surface in my code. Is the DataAdapter creating one in the background?

[jmcilhinney]

jmcilhinney you said that I had already bound a DataTable to the grid, which I don't see on the surface in my code. Is the DataAdapter creating one in the background?

You said that you declared 'dt' as type DataTable, so any value you assign to it must be a DataTable. You're assigning a value to it here:

dt = ds.Tables(0)

You don't need the DataSet or the DefaultView at all. Just do this:

da.Fill(dt)
dgvShowGLA.DataSource = dt

A DataSet is just a container for DataTables and the DataRelations between them. If there's only one DataTable and no DataRelations, what's the container for? You don't need to bind the DefaultView because, when you bind a DataTable, the data comes from the DefaultView anyway.

Just note that you now need to create the DataTable yourself, so you must change this:

Private dt As DataTable

to this:

Private dt As New DataTable