Tranfering data from excel to a database table?

[rookie]

I am trying to transfer data from an excel file to a database table. I am able to collect the data into a dataset table. However, I need to store the dataset table into an sql server database table.

Any assistance will be appreicated

Below is the code that does this. 
[SIZE=2][COLOR=#008000][COLOR=#000000]===============================================[/COLOR][/COLOR][/SIZE][COLOR=#008000]
[/COLOR][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myFile [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = Server.MapPath("testxls.xls")[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection( _[/SIZE]
[SIZE=2]"provider=Microsoft.Jet.OLEDB.4.0;data source=" & myFile & _[/SIZE]
[SIZE=2]";Extended Properties=Excel 8.0;")[/SIZE]
[SIZE=2]MyConnection.Open()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyCmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand[/SIZE]
[SIZE=2]MyCmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand("select * from [Sheet1$]", MyConnection)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter[/SIZE]
 
[SIZE=2]MyAdapter.SelectCommand = MyCmd[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myDS [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
 
[SIZE=2]MyAdapter.Fill([COLOR=red]myDS, "myTable")[/COLOR][/SIZE]
=================================================

Thanks.

 

[jmcilhinney]

At the end of that code you have a DataTable containing your data. Where that data came from is of no consequence whatsoever. How would you normally insert data from a DataTable into a table in SQL Server? You create an SqlDataAdapter with the appropriate InsertCommand and call its Update method, passing the DataTable as a parameter.

One point to note is that for this to work all the rows in your DataTable must have a RowState of Added. You can set this manually for each DataRow but the best way is to not have them set to Unchanged in the first place. To do that, set the AcceptChangesDuringFill property of your OleDbDataAdapter to False before calling Fill.

If you don't know how to insert data to begin with then I suggest that you do a member search for TechGnome and check out his signature. He has links to an ADO.NET tutorial he wrote that will teach you the basics of retrieving, deleting, inserting and updating records.

 

[rookie]

Thank for your response Jmcilhinney.

I apologize for not mentioning that I tried, but got some errors when I attempted to insert the table from my dataset into another database table.

* Please note, my excel file does not reside on my database server. so i need some sort of new/different connection to my DB server

I attempted creating a new OleDB connection i got an error stating: 'Multiple OLE DB generated errors'


When i tried to reassign a connectionstring, I got an error stating 'Not allowed to change 'connectionstring' property while the connection (state=open)' . I attempted to fix this error by closing and reopening the connection, then I got the former: 'Multiple OLE DB ...'


* Also I didnt find any material on TechGnome's previous blob that addressed this particular scenario. Please, refer me to any helpful material if you find one

Thanks

 

[jmcilhinney]

If you're connection to two different data sources then you should be using two connections. You could use the OLEDB provider for SQL Server if you wanted to. That would allow you to use a single OleDbDataAdapter. The SelectCommand would use one OleDbConnection to the Excel file and the InsertCommand would use another OleDbConnection to the SQL Server. Alternatively you could use OleDb for Excel and SqlClient for SQL Server. That would require an OleDbDataAdapter and OleDbConnection for Excel and an SqlDataAdapter and an SqlConnection for SQL Server.

Also, TechGnome's tute absolutely DOES address everything you need. You're making the mistake of thinking of this as one problem that you need a specific solution to. It's not. It's two separate problems for which solutions already exist. Getting data from the Excel file into a DataTable is one individual problem. Once the data is in the DataTable you can forget about where it came from. A DataTable is a DataTable. The Excel side of the problem doesn't care where the data goes after it is put into the DataTable and the SQL Server side of the problem doesn't care how the data got into the DataTable in the first place. They are two independent problems. If I need to get from A to C and there are maps to get from A to B and from B to C, do I need a map that goes from A to C? No I don't. Once I get to B the fact that I came from A is immaterial. I just use the map from B to C even though it doesn't know anything about the location of A.

 

[rookie]

Every attempt I made to create a new connection gave an error message 'Multiple OLE DB ..', even when I tried to close the connection and reopen it. I am certain their is something i'm missing partaining to resetting/changing connections. I tried to reset connection by:

MyAdapter.SelectCommand.Connection = Nothing
and also
MyCmd.Connection.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=SERVER1;" & _
"initial catalog=DB1; UID=us ;PASSWORD=ps; TrustedConnection=false"


I looked again in GNome tutorial, yes, i saw inserting, updating into database, but I could nt find any example where he reconnected to a database. I'm not saying that this is a special case, I just want to understand what am doing wrong. Please can you point me to any relevant tutorial for this.

Thanks again

 

[jmcilhinney]

I don't see what reconnecting has to do with this. You connect to one data source, get your data, connect to another and save your data. If he has shown you how to connect to a database then he's shown you all you need to know. Obviously there's an issue with your code so you probably need to post all of the code you're using for this operation. Otherwise we're just guessing. Also, although I said that you can use OleDb to connect to SQL Server I'd recommend always using SqlClient. It's no big deal to create a second data adapter.

 

[cjard]

Every attempt I made to create a new connection gave an error message

MyCmd.Connection.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=SERVER1;" & _
"initial catalog=DB1; UID=us ;PASSWORD=ps; TrustedConnection=false"

With this connection string, you are using the OLE DB adapter, and specifically the Microsoft Jet data provider. Jet 4.0 is a driver that allows you to connect to Microsoft Access databases. It cannot be use dto connect to SQL Server.

Use the SqlConnection as JMcIlhinney suggests. For more information about any class, such as SqlConnection you type it into google
Within the top 10 results is ususally the MSDN page for that object - Microsoft wrote it and they write lots about their components too.
We read the SqlConnection page, and at the bottom in the OTHER RESOURCES section we see a link to a page titled "Using the .NET Framework Data Provider for SQL Server"
On this page, which is a contents page for many related topics, we can see "Modifying Data In ADO.NET" and from there we can access "Updataing Data Sources With Data Adapters"

That's how to find some example code for connecting to a database. All that is missing is the conenctions string. There are several options, including www.connectionstrings.com and googling for "how to make a sql server connection string"
Perhaps the simplest is to make a new file called A.UDL, double click it, set the relevant options on all tabs, test the connection, say OK then view the ucontent of the UDL file with notepad. It will be a connection string suitable for what you enteredi into the "UDL wizard".

 

[rookie]

Thanks a lot jmcilhinney, cjard. The issue i had was mainly trying access SQL server with OLE DB (like you both had observed). I used SqlClient for the Sql server connection and OLE DB for Excel data (since SqlClient does nt support Excel), and it works fine now.

Thanks guys. You really helped a lot.

 

[jmcilhinney]

There is no issue accessing SQL Server with OleDb as long as you use the SQL Server provider for OLEDB. As I missed and cjard pointed out, you were using the Microsoft Jet provider, which is primaraily used for Access but also connects to text files, Excel files and many other data sources besides. www.connectionstrings.com, which cjard also suggested, lists the format for many connection strings, including SQL Server by OLEDB. SqlClient is more efficient than OleDb so should genereally be your first choice, though in this case the fact that using OleDb allows you to use a single DataAdapter may be a plus. I've done the same thing myself transferring from Access to SQL Server.

 

[rookie]

Again, thanks for the correction and re-highlighting the fact that "Microsoft Jet data provider. Jet 4.0 isn't the right driver for sqlServer - (OLEDB wasnt the issue but the driver - got that)
- I have one more issue with the code:
Like you already know, I am trying to copy/read data from another dataset then save the new dataset into my database. Below is my code. The grid displays the copied data just fine. However, after calling the * DBAdapter.Update(0), the copied data is not commited into my database. I'm suspecting one of my preblem is that i'm not using the DBAdapter.Update correctly.
Can you tell what i'm doing wrong, and an appropriate way(if any) to get data from dataset instead of copying/ or looping through a dataset table to insert rows?

Thanks

Private Sub UpdateDataBase(ByVal ds As DataSet)
 
Dim dbConnString As String
 
Dim dbConnection As SqlConnection = New SqlConnection
dbConnection.ConnectionString = "Data Source=ServerName; Initial Catalog=DB;" & _
"UID=uid;PASSWORD=psw; Trusted_Connection=false"
dbConnection.Open()
 
Dim DBCmd As SqlCommand
DBCmd = New SqlCommand("Select * from [myTable]", dbConnection)
 
Dim DBAdapter As New SqlDataAdapter
DBAdapter.SelectCommand = DBCmd
 
Dim DBds As New DataSet
DBAdapter.Fill(DBds, "myTable")
 
DBds = ds.Copy()
DBds.AcceptChanges()
DBAdapter.Update(DBds.Tables(0))
[COLOR=#990000][B]DataGrid1.DataSource = DBds.Tables(0).DefaultView[/B][/COLOR]
DataGrid1.DataBind()
 
DBds = Nothing
DBAdapter = Nothing
DBCmd = Nothing
ds = Nothing
 
End Sub

 

[jmcilhinney]

You're making two mistakes.

Firstly, in order to insert DataRows into a database they must have a RowState of Added. When you call Fill, the default behaviour of the DataAdapter is to implicitly call AcceptChanges, which sets the RowState of all rows to Unchanged. As far as ADO.NET is concerned those are all rows that exist in the database so it will not try to insert them when you call Update. As I stated in the very first reply, you need to set the AcceptChangesDuringFill property of the DataAdapter to False before calling Fill. That way the DataAdapter will not call AcceptChanges, the RowState of each row will remain as Added and they will all be insreted when you call Update.

Secondly, you're explicitly calling AcceptChanges. I've lost count of the number of people who call AcceptChanges before calling Update and then wonder why nothing happens. The one and only reason to call AcceptChanges is to overwrite the original data in a DataRow or DataRows with the current data and set the RowState to Unchanged. When you do that the DataRow, DataTable or DataSet you called it on appears to have not changed, thus calling Update will have no effect. Update only acts on changed rows, i.e. those with a RowState of Deleted, Added, or Modified. If there are no rows with those RowStates then Update will do nothing.

 

[rookie]

I made the changes (in code below) as you adviced, but my database table isnt still populated with the new values.

PrivateSub UpdateDataBase(ByVal ds As DataSet)
Dim dbConnString AsString
Dim dbConnection As SqlConnection = New SqlConnection
dbConnection.ConnectionString = "Data Source=ServerName; Initial Catalog=DB;" & _
"UID=uid;PASSWORD=psw; Trusted_Connection=false"
dbConnection.Open()
Dim dbAdapter AsNew SqlDataAdapter
dbAdapter.AcceptChangesDuringFill = False
'I want replace existing tbl in SqlServer with ds.table(0)
dbAdapter.Update(ds, "ExistingTblinDB") ' still confused
DataGrid1.DataSource = ds.Tables("ExistingTblinDB")).DefaultView
DataGrid1.DataBind() ' this prints exaclty i want to see
dbAdapter = Nothing
ds = Nothing
EndSub

I am a little more confused. if can you please rewrite the code so that I can see how you will do it, I will understand if you won't. i will go and do some reading on data access from the scratch.

Thanks offering your assistance

 

[jmcilhinney]

I've said twice now that you need to set the AcceptChangesDuringFill property to False before calling Fill. It's got nothing whatsoever to do with updating. As the name of that property suggests, it determines whether AcceptChanges is called when you call Fill. If you're not calling Fill then what use is it. I've explained in detail why this is important. Create a DataAdapter, set its AcceptChangesDuringFill property, call its Fill method to get the data, create another DataAdapter, call its Update method to save the data. Very simple.

 

[rookie]

* My dataset 'ds' has data in Tables(0) already. I now want to save to db - i dont get why i need to call dataadapter fill or set any of its properties. i attempted to do what u said and still no success
pls see the text highlighted in red: i'm trying to specify the where to the data should be selected from - really not sure how to do that

Please help

PrivateSub UpdateDataBase(ByVal ds As DataSet)
Dim dbConnString AsString
Dim dbConnection As SqlConnection = New SqlConnection
dbConnection.ConnectionString = "Data Source=Server1; Initial Catalog=DB;" & _
"UID=ui;PASSWORD=psw; Trusted_Connection=false"
dbConnection.Open()
Dim mycmd As SqlCommand
mycmd = New SqlCommand("select * from [DBTable]", dbConnection)
Dim dbAdapter AsNew SqlDataAdapter
dbAdapter.AcceptChangesDuringFill = False
dbAdapter.SelectCommand = mycmd
dbAdapter.Fill(ds, "myTable")
mycmd = New SqlCommand("insert into EEOTest select * from ds.Table(0)", dbConnection)
dbAdapter.InsertCommand = mycmd
dbAdapter.Update(ds.Tables(0))
DataGrid1.DataSource = ds.Tables(0).DefaultView
DataGrid1.DataBind()
dbAdapter = Nothing
ds = Nothing
EndSub

 

[jmcilhinney]

This is all wrong. You should not be executing any SELECT statements with your SqlDataAdapter. That second DataAdapter is purely to INSERT data. Also, SQL doesn't know anything about DataSets or DataTables. Your SQL code should not have any references whatsoever to your VB objects. I've given you a very simple explanation of the entire process.

To get the data from Excel:
Create a DataAdapter, set its AcceptChangesDuringFill property, call its Fill method to get the data.

To add the data to SQL Server:
Create another DataAdapter, call its Update method to save the data.

The first part is the bit where you have to set the AcceptChangesDuringFill property to False, otherwsie the data you get from Excel cannot be added to SQL Server. That's what I've been saying all along.

Once you've got the data correctly from Excel into a DataTable then it's just a case of creating an appropriate SqlDataAdapter and calling its Update method. "Appropriate" means the correct SQL code and the correct parameters. As an example, assuming that the DataTable containing the data you retrieved from Excel contains DataColumns named "ID" and "Name" and the SQL Server table you're trying to add the data to has columns with the same names, your code should look like this:

Dim command As New SqlCommand("INSERT INTO MyTable (ID, Name) VALUES (@ID, @Name)", connection)

command.Parameters.Add("@ID", SqlDbType.Integer, 4, "ID")
command.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name")

Dim adapter As New SqlDataAdapter

adapter.InsertCommand = command
adapter.Update(ds, "myTable")

That code creates a data adapter with an appropriate command to insert data. The SQL code inserts values into the ID and Name columns from the @ID and @Name parameters. The value for the @ID parameter is of type Integer with precision 4 and is taken from the ID column of the source. The value for the @Name parameter is of type VarChar with maximum length 50 and is taken from the Name column of the source. The aforementioned source is whatever you pass to the adapter's Update method. Now you will need to adjust the SQL code and the parameters to the particulars of the table and columns you're using, but that's the idea.