(Resolved) CSV > Datagridview > SQL Server 2008
I have a form that needs to pull a CSV file into a datagridview, which I have working OK with the code below.
VB Code:
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.
VB Code:
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?
Re: CSV > Datagridview > SQL Server 2008
You've bound a DataTable to your grid. The WriteToServer method takes a DataTable as an argument.
Re: CSV > Datagridview > SQL Server 2008
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)
VB Code:
'
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?
Re: (Resolved) CSV > Datagridview > SQL Server 2008
Quote:
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:You don't need the DataSet or the DefaultView at all. Just do this:
vb.net Code:
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:to this:
vb.net Code:
Private dt As New DataTable