Sharepoint List Data from SQL Table

rhcellxion
rhcellxion used Ask the Experts™
on
I am creating a PO form in MOSS 2007...  As part of the functionality, I would like the Vendor Number to be pulled from a SQL Table in a dropdown list, so the user can either hit the dropdown list.... Not sure how to connect the list to the sql table...... Is there a way to do this???

thanks
randy
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
There is no out of box solution for this. You may try BDC and create a BDC field. It involves a lot of works too.
Another solution is you use Data View Web Part to retrieve data from your SQL table and then use javascript to populate the choice field. Not a simple solution either.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
There's no ez way to do this out of the box. The MS way is to use the BDC, but this (for me) is more trouble than it's worth.

I have successfully written and implemented a Custom Field, that accepts 2 parameters "finder stored proc name" & "description stored proc name"

When the field is added to any list it displays a blank search field, or it can have a default value
> user clicks the search icon
> field looks in the web.config for the connection string
> connects to the DB
> executes the  "finder stored proc"
> renders a drop down with the return values
> user selects an item from the drop down
> field executes the  "description stored proc"

This project will help you get started: http://blogs.code-counsel.net/Wouter/Downloads/WSS3Fields%20-%20Source.zip
This describes the project: http://msdn.microsoft.com/en-us/library/cc889345.aspx

You will need to replace the CAML stuff with SQL stuff.

I would give you my field, but it belong to the company I worked for and it's mixed in with A LOT of other fields and dll's.

Commented:
Doh!
thman got to it before me.
Actually found a product called SharePoint Business Data List Connector from Layer2 Technologies...

http://www.layer2.de



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial