asked on 9/13/2010

Getting Sharepoint Designer to get data from SQL using SQLDataSource

Hi,

I am trying to create a simple timesheet in Sharepoint using SQL to store and retreive the data for reportint purposes.

I am using Sharepoint Designer to create the webapges for our Sharepoint 2.0 site which resides on a SBS 2003 server. I have created a subsite where I would like our employees to go to and enter their timesheets and for the managers to get reports from.

I have run into a problem when trying to use a dropdownlist and SQLDataSource for which I have attached the code for. The error i am getting when trying to access the page is -

Could not instantiate control asp:SqlDataSource

Web Parts Maintenance Page: If you have permission, you can use this page to temporarily disable Web Parts or remove personal settings. For more information, contact your site administrator.

Troubleshoot issues with Windows SharePoint Services.

Any help with this problem would be great.

<asp:DropDownList runat="server" id="DropDownList1" DataValueField="EmployeeID" DataTextField="Fullname" DataSourceID="VaterDB">
	</asp:DropDownList>
	<asp:SqlDataSource runat="server" ID="DATABASE" ProviderName="System.Data.OleDb" ConnectionString="Provider=SQLOLEDB;Data Source=SERVER\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=DATABASE" SelectCommand="SELECT [Fullname], [EmployeeID] FROM [qryFullName]">
	</asp:SqlDataSource>

Open in new window

Microsoft SharePoint Microsoft SQL Server 2008

Last Comment

roofin

9/17/2010

antonybrahin

9/13/2010

I guess, the service account you are using to run SharePoint does not have permission to the sqlsource db u are using for dropdown. Check and revert.

roofin

9/13/2010

ASKER

I have tried to usse SQL Server Authentication and it still gives me the same error.

antonybrahin

9/13/2010

have a look at this https://www.experts-exchange.com/questions/22643312/SQL-and-SharePoint-Could-not-instantiate-error.html

roofin

9/13/2010

ASKER

Thanks , but I have already had a look at that solution and it doesn't help me other then saying I shoudl ne using webparts and not SQLDataSource, which I disagree with.

ASKER CERTIFIED SOLUTION

zephyr_hex (Megan)

9/13/2010

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

roofin

9/14/2010

ASKER

Hi zepphyr hex,

Thanks for your response.

I am running sharepoint 2.0 on a SBS 2003 server. The SQLExpress database is alos on this server.
I have been able to modify and add pages using sharepoint designer 2007 for our current intranet.

After doing some more testing this morning I have found out that I can get a DVWP working but still no luck with the drop down list which I am assuming is cause it is a ASP.NET controla nd needs ASP.NET 2.0 or later to work where Sharepoint 2.0 is only running ASP.NET 1.1.

I will see if I can upgrade ASP.NET for sharepoint today and see how this goes.

roofin

9/14/2010

ASKER

Hi Again,

I have WSS 2.0 running ASP.NET 2.0 and this havs solved my problem, but now I have another one.
When i run the site now I get the following error

Access to the ADO.net Managed Provider 'OleDbFactory' was denied in the data source with ID 'Database' because of security settings.

I know there is most probably s simple setting to change but I cannot find it anywhere.

I am not sure if this ahs anythign to do with it but when I create the SQLDataSource I get the following message from Sharepoint Designer -

Storing connection information in the web application configuration file (web.config) is not supported on Windows Sharepoint Services sites. The connection will be stored in the data source control instead.

I will keep looking and try and figure it out, but any help would be great.

Thanks Roofin

zephyr_hex (Megan)

9/14/2010

sorry i can't be of more help. you're on a really old version of sharepoint.

sharepoint 2007 / wss 3.0 DVWP stores the connection info in the web part. not sure what wss 2.0 did.

and i'm surprised sharepoint designer works on that old version of sharepoint.

roofin

9/17/2010

ASKER

Hi,

Thanks zephyr hex for all your help, You have helped me solve the problem.

Cheers

Roofin