I am developing an ASP.NET 2.0 Web page using Visual Web Developer.
My page has three gridviews which have its own SqlDataSource's. I want my intranet users (who can access this page only after correct Windows Server Domain logon) export those gridviews data to MS Excel by 'right-clicking' on the gridview. It is a great built-in feature of gridview which does not require any coding. My problem is only second gridview successfully exports the table.
When I right-click and chose 'Export to Microsoft Excel' menu on the first and third gridview, Excel opens up and showed an error message :
"This Web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar."
I searched MS site and got an article about this message (http://support.microsoft.com/default.aspx?SCID=kb;en-us;277596) and it says table of gridview maybe password protected, which I can't understand because I am working on the same developing machine which runs IIS and VWD together. SQL server is remote, though. My user ID is the database owner of the remote SQL server and had no problem accessing the databse. That's why I don't think it is really a securury issue. Besides, why the second gridview CAN export succesfully then?
The cause may not be in the difference of gridview designs but in how the gridviews access data source. But to my understanding, web query from Excel does not invoke data connection to the database but extracts information from HTML already displayed in the browser.
Here I attach a part of my source file about the three gridviews. Any comment will be welcome.
===============================================================================
...
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
CellPadding="0" DataSourceID="SqlDataSource_Shiplist" ForeColor="Black"
GridLines="Vertical" style="z-index: 100; left: 320px; position: absolute; top: 92px"
DataKeyNames="Sort_Number,Kit_Number"
DataMember="DefaultView" ToolTip="Right-click and choose 'Export to Excel' for output and printing."
Caption="Contents of shiplist file" CaptionAlign="Left" Font-Names="Verdana"
Font-Size="Small" EnableTheming="True">
<FooterStyle BackColor="#CCCCCC" />
<Columns>
...
</Columns>
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#CCCCCC" />
</asp:GridView>
---------------------------------------------------------------------------------
<asp:GridView ID="GridView2" runat="server" AllowSorting="True" AutoGenerateColumns="False"
BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
CellPadding="0" DataSourceID="SqlDataSource_Parts" ForeColor="Black"
GridLines="Vertical" style="z-index: 108; left: 320px; position: absolute; top: 92px" DataKeyNames="Kit_Number"
ToolTip="Right-click and choose 'Export to Excel' for output and printing." Caption="List of Shiplists"
CaptionAlign="Left" Font-Bold="False" Font-Names="Verdana" Font-Size="Small" TabIndex="2">
<FooterStyle BackColor="#CCCCCC" />
<Columns>
...
</Columns>
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#CCCCCC" />
</asp:GridView>
----------------------------------------------------------------------------------
<asp:GridView ID="GridView3" runat="server" AllowSorting="True"
AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="Solid"
BorderWidth="1px" Caption="Merged Shiplist" CaptionAlign="Left" CellPadding="3"
DataKeyNames="Part_Number" DataSourceID="SqlDataSource_MergedShiplist" Font-Names="Verdana"
Font-Size="Small" ForeColor="Black" GridLines="Vertical" Style="z-index: 110;
left: 320px; position: absolute; top: 88px" Width="392px" TabIndex="3">
<Columns>
<asp:BoundField DataField="Part_Number" HeaderText="Part_Number" SortExpression="Part_Number" />
<asp:BoundField DataField="QtyReq" HeaderText="QtyReqTotal" SortExpression="QtyReq" />
<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
</Columns>
<RowStyle Font-Names="Verdana" Font-Size="X-Small" />
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="Black" Font-Bold="True" Font-Names="Verdana" Font-Size="X-Small"
ForeColor="White" />
<AlternatingRowStyle BackColor="#CCCCCC" />
</asp:GridView>
===============================================================================