Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

sql server data in lists

  Asked By: Hiral    Date: Oct 17    Category: Sharepoint    Views: 3081

can someone tell me that how can we get data from sql server in a list...my requirement is that say i have 3 columns in a list i want that when user enters data in 2 columns the 3rd should auto populate ...all the 3 columns are there in the sql server as tables..and also in another list's column i want a drop down that shows data from sql server and which is always updated...let me know if i need to clarify more



7 Answers Found

Answer #1    Answered By: Kristy Hicks     Answered On: Oct 17

1. Get the Id from the URL encode or below query:

WHERE (Title LIKE '%your list  name%')

2. Get the column  values from the SQL:

SELECT dbo.UserData.tp_column_name,dbo.UserData.*
FROM dbo.Lists
dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId
(dbo.UserData.tp_ListId = ‘{list id}’)

Answer #2    Answered By: Amanda Brown     Answered On: Oct 17

For benefit to others:

so in simple words and changing the requirement  of my below statement :) what i want is that when user  is filling the columns  of a custom list  he should get a drop  down and the data  of that drop down should be coming from sql  server ie say we have a custom list and the user clicks on new item and i have a column  in custom list say ID so when user is filling this ID column of custom list he should get a drop down and the data in the drop down should be from The ID column of a table in SQL Serverview entire scrapcollapse scrap

Ok, as you are looking for real time data from SQL server  i.e. if anything changes in SQL it should be immediately available that very micro-second :), you will have to either go for a custom FIELD TYPE, that will pull the data from SQL Server or in case if you are not looking for real time data then you can create a custom hidden list with only one column (ID) that is autopopulated by a timer-job every 1 hour or whatever interval you need and in turn this column is referred by your custom list.Unfortunately BDC columns cannot be used in SharePoint List (not available in MOSS), so no BDC list column :(. My first bet would be a custom sharepoint Field Type that talks to SQL and gets the information.

Answer #3    Answered By: Carey Everett     Answered On: Oct 17

Thnaks for updating i was about to update here ....whenever i make a connection to database say a datagrid or data  view from sharepoint designer and add it to a sharepoint site as webpart .. then it works fine when i see the data in sharepoint designer but when i save the changes and go back to my sharepoint site page it gives me an error that

this control does not allow connections with the following keywords : 'integrated security','trusted connection'

Can anyone point me to the right direction...i googled around and found different tweaks but just want to know some expert advice which won't give me any problems in the production environment

Answer #4    Answered By: Anuj Lakhe     Answered On: Oct 17

you placing connection object on the same page thats why it is giving error, cos of MOSS security model u cant not place custom connection string or object directly on the page ,
i was facing the same problem when i was trying to do this . when i removed sql  connection object then data  was not coming bt the error was also not coming, so to serve the purpose i hv used feature and used dll files .

if this is why u r facing the prob then the solution is this .

do u have any link which tells about creating custom filed which fetch datafrom sql at the run time , or cascaded dropdown on the list  form, please share.............

Answer #5    Answered By: Lee Dickerson     Answered On: Oct 17

What kind of features and dll have u used to solve this problem ?

Answer #6    Answered By: Aditiya Kapale     Answered On: Oct 17

rather than writing custom code to page , i have created a class file complied in to dll, then used feature to bind it to the action,
and if not dll then asp page directives can be used to use the custom class file which is complied into dll , like tag prefix

Answer #7    Answered By: Cristopher Gould     Answered On: Oct 17

Use a custom webservice to get the req. data  from sql  and put in a data set and then populate  it dynamically into the sp list  columns. Have an event recevier such that when the 2 cols are filled in list the 3rd  column triggers an event which fetchs data from the database.

Didn't find what you were looking for? Find more on sql server data in lists Or get search suggestion and latest updates.