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_Company",
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_Month",
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_Company",
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_Month",
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.DescriptionAttribute("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(ThisWorkbook_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 said:
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.aspx?scid=kb;en-us;307033
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlws.asp
http://www.codeproject.com/vb/net/Webservices.asp
Ken
Microsoft MVP [ASP.NET]
pmud said:
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
