Hi Ken,
As suggested by you I created a Web Service with ASP.NET & an Excel
Application Which would use this Web SErvice. But I am getting error in the
WEB SERVICE & the Excel Project too. PLEASE HELP!
MY CODE IS ::: WEB SERVICE::
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
namespace SQLQuery
{
/// <summary>
/// Summary description for Service1.
/// </summary>
public class Service1: System.Web.Services.WebService
{
public Service1()
{
//CODEGEN: This call is required by the ASP.NET Web Services Designer
InitializeComponent();
}
private System.Data.SqlClient.SqlConnection sqlConnection1;
private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
private System.Data.SqlClient.SqlCommand sqlCommand1;
#region Component Designer generated code
//Required by the Web Services Designer
private IContainer components = null;
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlCommand1 = new System.Data.SqlClient.SqlCommand();
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=TRIOEX;packet
size=4096;user id=sa;data source=TRIOEX;persist secu" +
"rity info=True;initial catalog=Sorting;password=icode";
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"Sort", new System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("Company", "Company"),
new
System.Data.Common.DataColumnMapping("Ceo", "Ceo"),
new
System.Data.Common.DataColumnMapping("Month", "Month")})});
this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
//
// sqlDeleteCommand1
//
this.sqlDeleteCommand1.CommandText = "DELETE FROM Sort WHERE (Company =
@Original_Company) AND (Ceo = @Original_Ceo OR " +
"@Original_Ceo IS NULL AND Ceo IS NULL) AND (Month = @Original_Month OR
@Original" +
"_Month IS NULL AND Month IS NULL)";
this.sqlDeleteCommand1.Connection = this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_Comp any",
System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Company",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_Ceo" ,
System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Ceo",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_Mont h",
System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Month",
System.Data.DataRowVersion.Original, null));
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "INSERT INTO Sort(Company, Ceo,
Month) VALUES (@Company, @Ceo, @Month); SELECT Com" +
"pany, Ceo, Month FROM Sort WHERE (Company = @Company)";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Company", System.Data.SqlDbType.VarChar,
10, "Company"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Ceo", System.Data.SqlDbType.VarChar, 10,
"Ceo"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Month", System.Data.SqlDbType.VarChar,
10, "Month"));
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT Company, Ceo, Month FROM
Sort";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
//
// sqlUpdateCommand1
//
this.sqlUpdateCommand1.CommandText = @"UPDATE Sort SET Company =
@Company, Ceo = @Ceo, Month = @Month WHERE (Company = @Original_Company) AND
(Ceo = @Original_Ceo OR @Original_Ceo IS NULL AND Ceo IS NULL) AND (Month =
@Original_Month OR @Original_Month IS NULL AND Month IS NULL); SELECT
Company, Ceo, Month FROM Sort WHERE (Company = @Company)";
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Company", System.Data.SqlDbType.VarChar,
10, "Company"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Ceo", System.Data.SqlDbType.VarChar, 10,
"Ceo"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Month", System.Data.SqlDbType.VarChar,
10, "Month"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_Comp any",
System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Company",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_Ceo" ,
System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Ceo",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_Mont h",
System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "Month",
System.Data.DataRowVersion.Original, null));
//
// sqlCommand1
//
this.sqlCommand1.CommandText = "INSERT INTO Sort (Company) VALUES (@a)";
this.sqlCommand1.Connection = this.sqlConnection1;
this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@a", System.Data.SqlDbType.VarChar, 10,
"Company"));
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
[WebMethod]
public string DbEntry()
{
return (sqlCommand1.Parameters["@a"].Value=obj.rng);
}
}
}
BUT I AM GETTING THE ERROR::
C:\Inetpub\wwwroot\DEMO\SQLQuery\Service1.asmx.cs( 134): The type or namespace
name 'My_Excel_Chart_Options' could not be found (are you missing a using
directive or an assembly reference?)
MY EXCEL PROJECT CODE IS ::
using System;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using MSForms = Microsoft.Vbe.Interop.Forms;
// Office integration attribute. Identifies the startup class for the
workbook. Do not modify.
[assembly:System.ComponentModel.DescriptionAttribut e("OfficeStartupClass,
Version=1.0, Class=My_Excel_Chart_Options.OfficeCodeBehind")]
namespace My_Excel_Chart_Options
{
/// <summary>
/// Contains managed code extensions for the workbook.
/// </summary>
public class OfficeCodeBehind
{
/// <summary>
/// Application object.
/// </summary>
internal Excel.Application ThisApplication
{
get { return thisApplication;}
}
/// <summary>
/// Workbook object.
/// </summary>
internal Excel.Workbook ThisWorkbook
{
get { return thisWorkbook;}
}
private Excel.Application thisApplication = null;
private Excel.Workbook thisWorkbook = null;
private Excel.WorkbookEvents_OpenEventHandler openEvent;
private Excel.WorkbookEvents_BeforeCloseEventHandler beforeCloseEvent;
private MSForms.CommandButton btn;
public Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook(Worksheet[1]);
public Excel.Range rng;//,rng1,rng2;
//public Excel.Range rng1;
#region Generated initialization code
/// <summary>
/// Default constructor.
/// </summary>
public OfficeCodeBehind()
{
}
/// <summary>
/// Required procedure. Do not modify.
/// </summary>
/// <param name="application">Application object.</param>
/// <param name="workbook">Workbook object.</param>
public void _Startup(object application, object workbook)
{
this.thisApplication = application as Excel.Application;
this.thisWorkbook = workbook as Excel.Workbook;
openEvent= new Excel.WorkbookEvents_OpenEventHandler
(ThisWorkbook_Open);
thisWorkbook.Open += openEvent;
beforeCloseEvent = new
Excel.WorkbookEvents_BeforeCloseEventHandler(ThisW orkbook_BeforeClose);
thisWorkbook.BeforeClose += beforeCloseEvent;
}
/// <summary>
/// Required procedure. Do not modify.
/// </summary>
public void _Shutdown()
{
thisApplication = null;
thisWorkbook = null;
}
/// <summary>
/// Finds the control with the specified name in the active worksheet.
/// </summary>
/// <param name='name'>Name of the control to find.</param>
/// <returns>
/// Returns the specified control, or null if it is not found.
/// </returns>
object FindControl(string name )
{
return FindControl(name, (Excel.Worksheet)
ThisWorkbook.ActiveSheet);
}
/// <summary>
/// Returns the control with the specified name in the specified
worksheet.
/// </summary>
/// <param name='name'>Name of the control to find.</param>
/// <param name='sheet'>Worksheet object that contains the
control.</param>
/// <returns>
/// Returns the specified control, or null if it is not found.
/// </returns>
object FindControl(string name, Excel.Worksheet sheet )
{
Excel.OLEObject theObject;
try
{
theObject = (Excel.OLEObject) sheet.OLEObjects(name);
return theObject.Object;
}
catch
{
// Returns null if the control is not found.
}
return null;
}
#endregion
protected void ThisWorkbook_Open()
{
this.btn =(MSForms.CommandButton)this.FindControl("btn");
// Attaches the events after making sure the variables
// have been properly initialized.
if (this.btn != null )
{
this.btn.Click += new
MSForms.CommandButtonEvents_ClickEventHandler(btn_ Click);
}
//rng = (Excel.Range)ws.Cells[1,1];
//localhost.SQLQuery.Service1 ser = new localhost.SQLQuery.Service1();
}
private void btn_Click()
{
localhost.SQLQuery.Service1 ser = new localhost.SQLQuery.Service1();
rng = (Excel.Range)ws.Cells[1,1];
ser.DbEntry(rng);
//this.textboxShowText.Text += "Hello World! ";
}
protected void ThisWorkbook_BeforeClose(ref bool Cancel)
{
Cancel = false;
}
}
}
ERROR IN THIS EXCEL PROJECT IS::
1. C:\Documents and Settings\pmudl\My Documents\Visual Studio Projects\My
Excel Chart Options\ThisWorkbook.cs(43): The name 'Worksheet' does not exist
in the class or namespace 'My_Excel_Chart_Options.OfficeCodeBehind'
2. C:\Documents and Settings\pmud\My Documents\Visual Studio Projects\My
Excel Chart Options\ThisWorkbook.cs(142): The type or namespace name 'ser'
could not be found (are you missing a using directive or an assembly
reference?)
3. C:\Documents and Settings\pmud\My Documents\Visual Studio Projects\My
Excel Chart Options\ThisWorkbook.cs(140): The type or namespace name
'localhost' could not be found (are you missing a using directive or an
assembly reference?)
PLEASE HELP ME!
Thanks
"Ken Cox [Microsoft MVP]" wrote:
[color=blue]
> Your best bet would be to create a Web service with ASP.NET and have users
> access it using Excel on their desktop.
>
> How To Use an XML Web Service by Using ASP.NET from an Office VBA Macro in
> Word or Excel
>
> "This article demonstrates how to use an XML Web service using ASP.NET from
> a Visual Basic for Applications (VBA) macro in Word or Excel. "
>
>
http://support.microsoft.com/default...b;en-us;307033
>
>
http://msdn.microsoft.com/library/de...l/odc_xlws.asp
>
>
http://www.codeproject.com/vb/net/Webservices.asp
>
> Ken
> Microsoft MVP [ASP.NET]
>
>
> "pmud" <pmud@discussions.microsoft.com> wrote in message
> news:E9003183-5441-408D-8008-21702EAAC427@microsoft.com...[color=green]
> > Hi,
> >
> > I need to use an Excel Sheet in ASP.NET application so that the users can
> > enter (copy, paste ) large number of rows in this Excel Sheet. Also,
> > Whatever
> > the USER ENETRS needs to go to the SQL DATABASE, probably by the click of
> > a
> > button.
> >
> > Is this possible? & what is the BEST APPROACH for doing this? & also if
> > any
> > links are there do tell those to me too coz I have no idea how to go about
> > doing it.
> >
> > Thanks
> > --
> > pmud[/color]
>
>[/color]
