excel grab range

[Anti-Rich]

does anyone know how to open an excel file, grab a range, convert it to a datatable and store it in a dataset?
i have imported the com component, microsoft excel 11
ive tried using code like this:

dim xlapp as excel.application
dim xlwrkbk as excel.workbook
xlApp = New Excel.Application
xlApp.Visible = False


xlWrkBk = xlApp.Workbooks.Open(path)

dim dt as datatable
dt = ctype(xlapp.workbooks.worksheets(0).range("A1:B7"), datatable)

can anyone help me? this is for an important app at work and needs to be done asap

i get a com exception, but i dont know how to use the com error codes, or throw a specific com exception (i tried looking for one but couldnt find it)

please help me

regards
adam

 

[jmcilhinney]

If you want your data in a DataTable then don't use Excel automation at all. Use ADO.NET all the way. It's almost exactly the same as using Access. Check out www.connectionstrings.com for the details.

 

[Anti-Rich]

hi,

yeah i actually tried it that way and it works and it fills the dataset just fine (i am using a dataset rather than datatable).

now, i was using an oledbcommand to grab the data from the excel file to put it into the ds, now i want to use that ds and update it to my sql server database.
this is my code so far

oDa.Fill(ds)
Dim sCmd As New SqlCommand("SELECT * FROM tblNSN", cnn)
Dim uCmd As New SqlCommand("UPDATE tblNSN SET NSN = @NSN, Description = @Description")
Dim iCmd As New SqlCommand("INSERT INTO tblNSN VALUES (@NSN, @Description)")

Dim sDa As New SqlDataAdapter(sCmd)
sDa.UpdateCommand = uCmd
sDa.InsertCommand = iCmd
Try
sDa.Update(ds)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

this is put intoa button_click event so it grabs the information adn chucks it straight into the table. now the problem im having, is im clicking the button and nothing happens! i have always been really terrible using datasets and dataadapters, and i try to avoid them, but i have no choice here i think. if you could explain what im doing wrong, and how im screwing up i would be eternally grateful!

regards
adam

 

[jmcilhinney]

i am using a dataset rather than datatable

No you're not. That's like saying that you are using a database with no tables in it. DataSets don't contain any data themselves. A DataSet contains DataTables in its Tables collection and DataRelations in its Relations collection. You may not be referring to a DataTable directly but when you retrieve the data it is populating a DataTable within your DataSet. As such your DataSet is completely useless. It's like creating an array to store a single Integer, except that a DataSet uses more memory space than an array. Don't use a DataSet unless there is a specific reason to do so.

Now to your issue. When you call Fill on a DataAdapter the default behaviour is to implicitly call AcceptChanges, thus all the DataRows have a RowState of Unchanged. When you call Update there are no new or edited rows in the table so nothing happens. You need to set the AcceptChangesDuringFill property of your original DataAdapter to False so that all DataRows keep their RowState of Added. Now when you call Update the new rows will be detected and inserted into the database.

Note also that your UPDATE command is faulty. It has no WHERE clause so EVERY time it is executed it will update EVERY row. That means that after you update a row EVERY row in the table will have the values you just set for that one row. You MUST have a WHERE clause to identify the row you want to update by, normally, its primary key. Having said that, you have no edited rows anyway so the UPDATE command is not required.

 

[Anti-Rich]

yeah cjard (in another thread) explained the datatable to me.. whoops!

ah, so THATS what was missing. im gonna try it now

cheers!

regards
adam

EDIT: oh and the update thing... that was pretty stupid of me, usually i spot things like that *sheepish grin* thanks for pointing it out

 

[Anti-Rich]

thanks mate it worked perfectly!!!!! now i know the little pitfalls and things to watch out for for datasets.

much appreciated!

Regards
adam