Hi am fetching data from sharepoint 2010 list by asmx webservices but when i there is no data in any column it says below error on sql: Lets says "ows_ClientContactPerson" column has not any record then it will give me below error if has then it will not give me error..
Msg 6260, Level 16, State 1, Line 2
An error occurred while getting new row from user defined Table Valued Function :
System.ArgumentException: Column 'ows_ClientContactPerson' does not belong to table row.
System.ArgumentException:
at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.get_Item(String columnName)
at SharePointClients.GetClientsItemInfo(Object obj, SqlString& ID, SqlString& Title, SqlString& ParentClientVertical, SqlString& NoOfStores, SqlString& Complexity, SqlString& Location, SqlString& ClientContactPerson, SqlString& IsActive).
Please see my C# code from when i am creating my assemblies.
using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
using System.Net;
using System.Xml;
public class SharePointClients
{
[SqlFunction(SystemDataAccess = SystemDataAccessKind.Read, FillRowMethodName = "GetClientsItemInfo")]
public static IEnumerable GetClientsItems(SqlString url, SqlString listName, SqlString viewName)
{
DataTable t = new DataTable();
WindowsImpersonationContext ctx = null;
WindowsIdentity id = SqlContext.WindowsIdentity;
try
{
ctx = id.Impersonate();
WSS.Lists svc = new WSS.Lists();
svc.Url = url.ToString();
//svc.Credentials = new NetworkCredential("barley", "pass@word1", "VS");
svc.Credentials = CredentialCache.DefaultNetworkCredentials;
XmlNode node = svc.GetListItems(listName.ToString(), viewName.ToString(), null, null,"150000", null, null);
XmlTextReader rdr = new XmlTextReader(node.OuterXml,
XmlNodeType.Element, null);
DataSet ds = new DataSet();
ds.ReadXml(rdr);
t = ds.Tables[1];
}
finally
{
if (ctx != null)
ctx.Undo();
}
return t.Rows;
}
public static void GetClientsItemInfo(
object obj,
out SqlString ID,
out SqlString Title,
out SqlString ParentClientVertical,
out SqlString NoOfStores,
out SqlString Complexity,
out SqlString Location,
out SqlString ClientContactPerson,
//out SqlString ClientContactEmailID,
//out SqlString PhoneNumber,
out SqlString IsActive)
{
DataRow r = (DataRow)obj;
ID = new SqlString(Convert.ToString(r["ows_ID"]));
Title = new SqlString(Convert.ToString(r["ows_Title"]));
ParentClientVertical = new SqlString(Convert.ToString(r["ows_ParentClientVertical"]));
NoOfStores = new SqlString(Convert.ToString(r["ows_NoOfStores"]));
Complexity = new SqlString(Convert.ToString(r["ows_Complexity"]));
Location = new SqlString(Convert.ToString(r["ows_Location"]));
ClientContactPerson = new SqlString(Convert.ToString(r["ows_ClientContactPerson"]));
//ClientContactEmailID = new SqlString(Convert.ToString(r["ows_ClientContactEmailID"]));
//PhoneNumber = new SqlString(Convert.ToString(r["ows_PhoneNumber"]));
IsActive = new SqlString(Convert.ToString(r["ows_IsActive"]));
}
}
Please see below my SQL function creation code
/****** Object: UserDefinedFunction [dbo].[fn_GetListItemsClients] Script Date: 10/19/2012 05:45:43 ******/
CREATE FUNCTION [dbo].[fn_GetListItemsClients](@SiteUrl [nvarchar](256), @ListName [nvarchar](256), @viewName [nvarchar](256))
RETURNS TABLE (
[ID] [nvarchar](50) NULL,
[Title] [nvarchar](256) NULL,
[ParentClientVertical] [nvarchar](256) NULL,
[NoOfStores] [nvarchar](256) NULL,
[Complexity] [nvarchar](256) NULL,
[Location] [nvarchar](256) NULL,
[ClientContactPerson] [nvarchar](256) NULL,
--[ClientContactEmailID] [nvarchar](70) NULL,
--[PhoneNumber] [nvarchar](70) NULL,
[IsActive] [nvarchar](256) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PMTFunction].[SharePointClients].[GetClientsItems]
GO
and please find below where i am using select command
select * from dbo.fn_GetListItemsClients('http://wks10953:1000/_vti_bin/Lists.asmx','Clients','')
This is really important, Please help me..