CSV--> Datagrid--> SqlTable

[kwood13]

I am trying to import a csv file into a datagrid, once data is in datagrid, I would like to be able to edit data and then write all the data into a sql table.

So far I can populate the csv data into a dataset and fill the datagrid to display the dataset. I am stuck on writing that date to a sql table.

 

[jmcilhinney]

Are you using ADO.NET to get the data from the CSV? If not then you should. Set the AcceptChnagesDuringFill property of your OleDbDataAdapter to False so every row appears to be new. Now you simply create an SqlDataAdapter with the appropriate InsertCommand to save the data to SQL Server, exactly as you would if the data had come from SQL Server in the first place.

 

[cjard]

See the Data Walkthroughs 2.0 link in my signature for a tutorial on how to send data to a database

 

[kwood13]

Thanks jmcilhinney & cjard,

I am rather new and I am still having some difficulties. Would my Insert command be querying the data from the dataset or the excel spreadsheet? How would I program the dataadapter know to write the datagrid dataset to sqldatabase? That is what's confusing me the most.




OptionStrictOn

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO

PublicClass frmImport
Inherits System.Windows.Forms.Form


PrivateSub frmImport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
'Me.LoadOrders()
EndSub

PrivateSub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
Me.LoadOrders()
EndSub

PrivateSub LoadOrders()

Dim sConnectionString AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\GiftCardImport;Extended Properties=Text;"
Dim conImport AsNew OleDbConnection(sConnectionString)

conImport.Open()

Dim importCmdSelect AsNew OleDbCommand("Select * from PendingTransactionList.csv", conImport)
Dim dbImport AsNew OleDbDataAdapter()
dbImport.SelectCommand = importCmdSelect

Dim dsImport AsNew DataSet()
dbImport.Fill(dsImport, "PendingTransactionList")
grdImport.DataSource = dsImport.Tables(0).DefaultView
dbImport.AcceptChangesDuringFill = False

EndSub


EndClass

 

[cjard]

Youre on the right track but i wouldnt recommend reading the file in and displaying what was read in for the user to edit. Go the other way round - show the user whats going into the database:

Use the DW2 link in my sig to establish a client side datatable representation of the database. Make a form with a grid that shows this table instead. When you read the CSV, do it with ExecuteReader() and write a loop to read the values yourself. Fill them into the typed datatable connected to the grid.