Solved

Getting Sharepoint Designer to get data from SQL using SQLDataSource

Posted on 2010-09-12
9
1,834 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:roofin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 4

Expert Comment

by:antonybrahin
ID: 33659577
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.
0
 

Author Comment

by:roofin
ID: 33659708
I have tried to usse SQL Server Authentication and it still gives me the same error.
0
 
LVL 4

Expert Comment

by:antonybrahin
ID: 33659736
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

by:roofin
ID: 33659801
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.
0
 
LVL 43

Accepted Solution

by:
zephyr_hex (Megan) earned 500 total points
ID: 33664466
a few things...

first, SQLEXPRESS does not allow remote connections by default.  if SQL is not on the same server as sharepoint, you'll need to configure SQL to allow remote connections.

also, you can't use integrated security.  you can use SSO (if you have that set up and configured in your environment), or use a specific username/password that is coded into your connection string (which means you'll want to create a specific account for this).

i had a hell of a time trying to get the connection string correct in wss 2.0 DVWP.  thankfully, in wss 3.0, there is a wizard that writes the string based on your input.

which reminds me... can you please clarify whether you're using sharepoint 2.0 or sharepoint 3.0?  because you said "sharepoint designer".  if you're using sharepoint 2.0 (aka wss 2.0), you'd be using FrontPage 2003.  if you're using sharepoint designer, you must be at least sharepoint 3.0 (aka wss 3.0).  if you're wss 3.0, just use the wizard to create your connection string.
http://www.lcbridge.nl/vision/2009/dvwp.htm  (see Adding a Database as a Data Source)
0
 

Author Comment

by:roofin
ID: 33667876
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.
0
 

Author Comment

by:roofin
ID: 33669489
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
0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 33673290
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.
0
 

Author Closing Comment

by:roofin
ID: 33698616
Hi,

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

Cheers

Roofin
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question