SmartMarker: Not adding 1st row?

Aspose.Cells Product Family
1

This may or may not be an Aspose problem - perhaps someone has run into it previously?

This is the code I am running:

Dim db As New Hot_DB
Dim dr As SqlDataReader = db.HOT_Email(Int32.Parse(Me.txtnewitem.Text))
dr.Read()
If dr.HasRows Then
Dim dt As New DataTable("gm")
dt.Load(dr)
Dim designer As WorkbookDesigner = New WorkbookDesigner()
designer.Open("C:\Inetpub\wwwroot\mnintranet\documents\hotemail.xls")
designer.SetDataSource(dt)
designer.Process()
designer.Save("C:\Inetpub\wwwroot\mnintranet\documents\hotemail2.xls")

It works great... except the first column of the DR never appears. If I change the order clause in the stored proc called by db.HOT_Email, I can confirm that it is ALWAYS the first record that does not appear.

When I step through the code in the debugger after the Load line, dt.Rows.count = '&H2' ... does that mean it has 3 rows or two? When I call the stored proc in Query Analyzer using the same Int value there are always 3 records.

I don't understand where I am losing the first record. Is it in the FILL command? is it my sheet - it is a basic fill and go spreadsheet - nothing fancy. All Fields are formatted correctly with valid field names, such as &=gm.so1

Any uggestions would be greatly appreciated! Thanks in advance,

Lou Syracuse
Sr. Programmer/Analyst
Merle Norman Cosmetics
Los Angeles, CA

2

Hi Lou,

You can print dt.Rows.Count to see if it's same as rows of data in your file. And I think you can try to change your code to:

Dim db As New Hot_DB
Dim dr As SqlDataReader = db.HOT_Email(Int32.Parse(Me.txtnewitem.Text))
Dim dt As New DataTable("gm")
dt.Load(dr)

If dt.Rows.Count > 0 Then

Dim designer As WorkbookDesigner = New WorkbookDesigner()
designer.Open("C:\Inetpub\wwwroot\mnintranet\documents\hotemail.xls")
designer.SetDataSource(dt)
designer.Process()
designer.Save("C:\Inetpub\wwwroot\mnintranet\documents\hotemail2.xls")

3

It is definetly not an Aspose.Cells issue.

I did a read loop through the SqlDataReader and it clearly has 3 records. But the DataTable only gets populated with 2 of them - always failing to add the 1st record. I tried removing the inital read command on the datareader, no change.

So I wrote code (kinda long and boring, I'll skip posting it here) to do manually create the DataTable and loop through the dataReader and populate the datatable by hand. And guess what - it comes out with 3 records.

Still have no idea why the FILL command isn't working, but at least I have what I need and we know that the problem is on Micro$oft, not Aspose. :)

Thanks for the assist!

Lou Syracuse
Senior Programmer Analyst
Merle Norman Cosmetics,
Los Angeles, CA