SQL/DataGrid connection problems

Feb 7, 2008 at 8:29 PM
Hi All,

I'm trying to follow the tutorial in the book "real world sharepoint 2007", and the use of smartpart to create an usercontrol that accesses the AdventureWorks database on SQL2005. I've been able to install Return of the SmartPartv1.2.0.0 on my SharePoint box, and have been able to add the demo usercontrolsimplecalculatorascx to a smartpart webpart.

However, I have been trying to get the usercontrolOrderGridascx running in the smartpart, but I am encountering different errors. My connection string is below:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="DataSource=webparts.mvp.local;Initial Catalog=AdventureWorks;User ID=sa" SelectCommand="SELECT PurchaseOrderID, EmployeeID, OrderDate, SubTotal FROM Purchasing.PurchaseOrderHeader WHERE (EmployeeID = @empid)">

When I add the usercontrolOrderGridascx to the webpart with the data string above, I get the following error exception back from SharePoint:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Keyword not supported: 'datasource'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Keyword not supported: 'datasource'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
ArgumentException: Keyword not supported: 'datasource'.
System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +1409890
System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +102
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52
...

- - - - - - - - - - - - - - -

I have looked through some posts for this type of error so I separted datasource with a space in the connection string so that it looks like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=webparts.mvp.local;Initial Catalog=AdventureWorks;User ID=sa" SelectCommand="SELECT PurchaseOrderID, EmployeeID, OrderDate, SubTotal FROM Purchasing.PurchaseOrderHeader WHERE (EmployeeID = @empid)">

When I rebuild and add the control, I am returned with the error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123

...
-------------------

I have checked all rights/permissions on the AdventureWorks DB and have allowed both named pipes and tcp/ip to access DB's remotely on my SQLServer which is running locally on the same server as MOSS2007.

Do you think that the issue is the connection string?

Also, here is the full code in the OrderGrid.ascx file:
-----------------------------------------------
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="OrderGrid.ascx.cs" Inherits="OrderGrid" %>
<asp:GridView ID="GridView1" runat="server" AllowPaging="true" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="PurchaseOrderID"
DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Width="100%">
<Columns>
<asp:BoundField DataField="PurchaseOrderID" HeaderText="PurchaseOrderID" InsertVisible="False"
ReadOnly="True" SortExpression="PurchaseOrderID" />
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
<asp:BoundField DataField="OrderDate" HeaderText="OrderDate" SortExpression="OrderDate" />
<asp:BoundField DataField="SubTotal" HeaderText="SubTotal" SortExpression="SubTotal" />
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="true" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=webparts.mvp.local;Initial Catalog=AdventureWorks;User ID=sa" SelectCommand="SELECT PurchaseOrderID, EmployeeID, OrderDate, SubTotal FROM Purchasing.PurchaseOrderHeader WHERE (EmployeeID = @empid)">
<SelectParameters>
<asp:Parameter DefaultValue="-1" Name="empid" />
</SelectParameters>
</asp:SqlDataSource>
------------------------------------------
and here is the full code in the OrderGrid.ascx.cs file:
-----------------------------------------
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class OrderGrid : System.Web.UI.UserControl, SmartPart.IConnectionConsumerControl
{
int empID;

protected void Page_Load(object sender, EventArgs e)
{

}

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceCommandEventArgs e)
{
e.Command.Parameters"@empid".Value = empID;
}

public string ConsumerMenuLabel
{
get { return "Receives an employee from"; }
}

public void SetConsumerData(object data)
{
if (data != null)
{
empID = (int)data;
GridView1.DataBind();
}
}
}

------------------------------------------

Thanks for any and all help!
Jason

Feb 7, 2008 at 8:57 PM
Additionally, here is the Event in the NT Applications Event Log on the server:

-----------------
Event Type: Warning
Event Source: ASP.NET 2.0.50727.0
Event Category: Web Event
Event ID: 1309
Date: 2/7/2008
Time: 12:47:09 PM
User: N/A
Computer: SHAREPOINT2
Description:
Event code: 3005 Event message: An unhandled exception has occurred. Event time: 2/7/2008 12:47:09 PM Event time (UTC): 2/7/2008 8:47:09 PM Event ID: bd6252a076ab4b2eb9acf0c224a2ea47 Event sequence: 54 Event occurrence: 2 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1663674670/Root-2-128468904160676286 Trust level: WSSMedium Application Virtual Path: / Application Path: C:\Inetpub\wwwroot\wss\VirtualDirectories\80\ Machine name: SHAREPOINT2 Process information: Process ID: 5412 Process name: w3wp.exe Account name: AGIAMAIL\jcoltrin Exception information: Exception type: SqlException Exception message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Request information: Request URL: http://sharepoint2/sites/it/dev/webparts/simpletestingwebparts.aspx?PageView=Shared Request path: /sites/it/dev/webparts/simpletestingwebparts.aspx User host address: 172.25.1.70 User: AGIAMAIL\jcoltrin Is authenticated: True Authentication Type: NTLM Thread account name: AGIAMAIL\jcoltrin Thread information: Thread ID: 1 Thread account name: AGIA_MAIL\jcoltrin Is impersonating: False Stack trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)
at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
at System.Web.UI.WebControls.GridView.DataBind()
at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
at System.Web.UI.Control.EnsureChildControls()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.WebControls.WebParts.WebPart.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Custom event details:

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


---------------------------------------


BTW, the firewall on the server is currently not active. When I try to open the firewall control panel on the server, I get the following error:

Windows Firewall cannot run because the Windows Firewall/Internet Connection Sharing (ICS) Service is not running. Do you want to start the firewall/ICS service? Yes/No
I choose No. Also the firewall on the client machine (Vista) is disabled.

Thanks for your help!
Jason