Exclude Crystal Reports from Help searches

Is there any way to keep the Help file information associated with Crystal
Reports from displaying when I search? I don't have this product and it just
gets in the way when looking for answers.
TIA
deanOn Jul 9, 4:09 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> Is there any way to keep the Help file information associated with Crystal
> Reports from displaying when I search? I don't have this product and it just
> gets in the way when looking for answers.
> TIA
> dean
I don't quite follow your question. If you are referring to SQL Server
2005 BOL (Books Online), you can filter the results by what you are
looking for (i.e., SQL Server Reporting Services 2005, T-SQL, SQL
Server 2005 Database Engine, etc). If this does not help, please
elaborate further.
Regards,
Enrique Martinez
Sr. Software Consultant|||I have the BOL Contents filter set to SQL Server Reporting Services and the
Technology filter set to SSRS but when I search for "subreport", many of the
items in the Local Help listing are from Crystal Reports for VS 2005. It
appears that the filters don't work very well.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1184022366.615675.59700@.q75g2000hsh.googlegroups.com...
> On Jul 9, 4:09 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
>> Is there any way to keep the Help file information associated with
>> Crystal
>> Reports from displaying when I search? I don't have this product and it
>> just
>> gets in the way when looking for answers.
>> TIA
>> dean
>
> I don't quite follow your question. If you are referring to SQL Server
> 2005 BOL (Books Online), you can filter the results by what you are
> looking for (i.e., SQL Server Reporting Services 2005, T-SQL, SQL
> Server 2005 Database Engine, etc). If this does not help, please
> elaborate further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jul 10, 9:34 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> I have the BOL Contents filter set to SQL Server Reporting Services and the
> Technology filter set to SSRS but when I search for "subreport", many of the
> items in the Local Help listing are from Crystal Reports for VS 2005. It
> appears that the filters don't work very well.
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1184022366.615675.59700@.q75g2000hsh.googlegroups.com...
> > On Jul 9, 4:09 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> >> Is there any way to keep the Help file information associated with
> >> Crystal
> >> Reports from displaying when I search? I don't have this product and it
> >> just
> >> gets in the way when looking for answers.
> >> TIA
> >> dean
> > I don't quite follow your question. If you are referring to SQL Server
> > 2005 BOL (Books Online), you can filter the results by what you are
> > looking for (i.e., SQL Server Reporting Services 2005, T-SQL, SQL
> > Server 2005 Database Engine, etc). If this does not help, please
> > elaborate further.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Pretty much, that's all you can do to filter the results. Sorry that I
could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Excel Issue

Does Microsoft have any plans for an excel - add on to Reporting Services?
I love the product, but my users hate the way RS renders to excel. There are
merged cells - and addition blank columns between the table columns. Sorting
is therefore a nightmare. My users would prefer reports from a visual basic
front end with all data coded to excel -- something I was hoping Reporting
Services would resolve. It has been my experience that Excel is every
accountants #1 software, it is to bad microsoft didn't take this into
consideration when developing RS.You can design reports to export properly to Excel.
What I do is the following. I add additional header rows and then merge
either the whole row together of a couple of cells and then put in what I
normally would have put in a textbox. You would think that merging cells
together this way would cause a problem with merged cells in Excel but it
does not. If you select your header and your data you can sort, while if you
have exported with a textbox above the table you get the problem with merged
cells when trying to sort.
Below is another solution from another poster. I haven't tried it but it
seems reasonable:
>>>>>>>>>>
Earlier this year I was trying to find a way around the merged cell issue.
(RS 2000 reports exported to Excel may not be sortable due to "merged
cells"). There were some newsgroup messages with ideas. I eventually decided
on this design workaround. Hopefully this can save someone else from doing
this research..
The Table can be the full width or slightly less of your page. I have best
results when it is less than the full width. I don't know if you can have
multiple tables in the body and still sort ok - I doubt it. Any/all other
objects in the body (logo images, textboxes) MUST be full width - from
leftmost edge to rightmost edge. Images can use Sizing = FitProportional to
appear ok. If any of those objects in the body is even slightly less than
full width (such as two text boxes adjacent to each other on the same row,
or a logo in the upper left and the title textbox in the upper right on the
same row), I've found that you will get merged cells in Excel and cannot
sort. I also tried all kinds of careful alignments of objects (matching the
columns in the table, etc) but it still caused merged cells. I don't believe
it matters what you have in the page header or footer - I can put multiple
textboxes on the same row in my page footer and it works ok.
Hope that helps,
Steve Nyberg
>>>>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"coding4life" <coding4life@.discussions.microsoft.com> wrote in message
news:1004438F-9090-4D8B-B900-090F83227184@.microsoft.com...
> Does Microsoft have any plans for an excel - add on to Reporting
> Services?
> I love the product, but my users hate the way RS renders to excel. There
> are
> merged cells - and addition blank columns between the table columns.
> Sorting
> is therefore a nightmare. My users would prefer reports from a visual
> basic
> front end with all data coded to excel -- something I was hoping Reporting
> Services would resolve. It has been my experience that Excel is every
> accountants #1 software, it is to bad microsoft didn't take this into
> consideration when developing RS.
>