Challenges:
I tried to connect to Oracle database in SharePoint (MOSS 2007). But got the following error when I tried to create a Database connections in Designer 2007.
1), Use custom connection string;
2), Select Microsoft .NET Framework Data Provider for Oracle as the Provider Name;
3) Post the below as the Connection String:
Password=<passwd>;User ID=<userID>; Data Source = <mydatasource>; Persist Security Info=True; Provider=OraOLEDB.Oracle;
4), It seemed the Oracle db can be connected, but could not find the root catalog since the "Initial Catalog=root;" was appended to the above connection string automatically.
5), and the error message is:
Server Error: An error occurred while retrieving the list of Tables from the root Database: The query you are trying to execute contains syntax errors.
I searched on the Internet, and was told there is a way to use BDC(Business Data Catalog). But I just need a simple way to show Oracle data on a SharePoint page. BDC connection is too much for me at this point.
Finally I got a simple solution from a forum (could not find the original link now) which uses SqlDataSource. It works for me based on what I needed.
1), Open the page in SharePoint designer 2007;
2), In the code view, find a place to paste the following code:
<asp:SqlDataSource runat="server" id="sqlDS1" ProviderName="System.Data.OracleClient" ConnectionString="Password=<passwd>; User Id=<userID>; Data Source=<TSN>;" DataSourceMode="DataReader" SelectCommand="SELECT * From Table”>
</asp:SqlDataSource>
Of course, replace your own Oracle server information at the above.
3), To test, you can drop a GridView in the same page as the below.
<asp:GridView runat="server" id="gv1" AutoGenerateColumns="False" DataSourceID="sqlDS1" >
<Columns>
<asp:boundfield DataField="TEST" HeaderText="TEST">
</asp:boundfield> </Columns>
</asp:GridView>
Good to know. Thanks for sharing. However, I wonder why your original method didn’t work. Can’t you modify the connection string by changing “Initial Catalog=root;” to your own database name?
[Reply]
I am using SD trying to connect to an Oracle database.
I tried adding the code but my page will display. Does it matter where?
[Reply]