Download the SQL servere date to excel file using ASP.net

Hi EveryOne

I would like to download the SQL server databse to excel file using ASP.Net

I need to download the one table to excel file. If anyone knows please help me.

With Regards

Nibu Abraham

hi

i found this link

http://www.theserverside.net/discussions/thread.tss?thread_id=29385

give a try

|||

Hi Nibu

Create a stored procedure to display the required columns from the give table. Grant execute permission to this stored procedure to the role that your ASP.NET account is assigned to. In your datalayer, run the stored procedure to output a dataset. Add a gridview control (or datagrid if still at ASP.NET 1.1) to an ASPX form.

Set the datasource for the control to the dataset produced by the read function in your data layer, call databind and the data will be displayed.

There is a simple modifier to make the displayed page OLE load Excel - I will find out the how of this at the weekend.

HTH

|||

You need to add the following to your Page_Load

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

You will need to turn off the sort option on the grid and any other hyperlinks on the form.

HTH

sql

Download the file stored in sqlserver table as image datatype using asp.net 2.0

I am using Asp.net 2.0 with C# and sql server 2000. I need to download the file which is stored in sql server database table as image datatype. So I need to download from cs page.

Pls reply,

Arun.

Here's a good article on storing and retrieving binary data with asp.net 2.0
http://www.beansoftware.com/ASP.NET-Tutorials/Binary-Files-To-Database.aspx

|||

Hi,

Consider, you have query like

select files,filename,filesize,fileext from filesintable where id=1

that file on dar[0] as image datatype.

while (dar.Read())
{
bFile = (byte[])dar[0];
Response.AddHeader("Content-Disposition", "attachment; filename=" +dar[1].ToString());

switch (dar[3].ToString())
{
case "ask":
Response.ContentType = "video/x-ms-asf";
break;
case "avi":
Response.ContentType = "video/avi";
break;
case "doc":
Response.ContentType = "application/msword";
break;
case "zip":
Response.ContentType = "application/zip";
break;
case "xls":
Response.ContentType = "application/vnd.ms-excel";
break;
case "ppt":
Response.ContentType = "application/vnd.ms-powerpoint";
break;
case "gif":
Response.ContentType = "image/gif";
break;
case "jpg":
case "jpeg":
Response.ContentType = "image/jpeg";
break;
case "wav":
Response.ContentType = "audio/wav";
break;
case "mp3":
Response.ContentType = "audio/mpeg3";
break;
case "mpg":
case "mpeg":
Response.ContentType = "video/mpeg";
break;
case "rtf":
Response.ContentType = "application/rtf";
break;
case "htm":
case "html":
Response.ContentType = "text/html";
break;
case "asp":
Response.ContentType = "text/asp";
break;
case "pdf":
Response.ContentType = "application/pdf";
break;
default:
Response.ContentType = "application/octet-stream";
break;
}
Response.BinaryWrite(bFile);

}

It works fine.

Doubt in " HOW TO: Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual C# .

Hi,

In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at

http://support.microsoft.com/defaul...Ben-us%3B306572

Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?

I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.

Any advice would be greatly appreciated. Thanks in advance.

Regards,
RK."RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.

The article doesn't mention SQL Server anywhere, and it sounds like your
problem is how to automate something in Excel, so I guess you'll get a
better answer in an Excel or C# newsgroup.

Simon|||RK (rakish123@.yahoo.com) writes:
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?

Of course there is.

I have not tried to get data from Excel with SELECT statements, but
would be surprised if "SELECT * FROM Sheet1" would work. I've mainly
use the OLE interface to get data from Excel in Perl. While I understand
that you don't want users to have to highlight things, I fail to see
but that you need to have some pre-defined structure that the Excel
file must have, or else you will have no idea of what you are importing.

But while you said SQL Server twice in your posting (look again Simon! :-),
your question seems more to be related to Excel, so you should find a
group about Excel.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You might look at the capabilities of SQL Server DTS Packages. They can
read from Excel spreadsheets. However, your datasheet would have to be
massaged enough that the data was a fairly straightforward import.

I suppose if I were you, I'd use a combination of VBA code to hack up the
spreadsheet, save it to a convenient spreadsheet, then use SQL-DMO to
trigger a DTS package for the import.

Otherwise, you could just do a VBA module that would pass in the interesting
data directly over an ADO, OLEDB or ODBC connection. We've done something
like that, it wasn't terribly difficult.

"RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.|||Hi,

There are a few alternatives. I recommend you to use SQL*XL an addin I
wrote for Excel to do these things. There are some other alternatives
which I will mention.

1. SQL*XL - addin for Excel to access your databases.
SQL*XL is an addin for Excel that allows you to access your database
directly from Excel. You can query data into Excel or you can pump
data from Excel into the database. SQL*XL focusses on making it easy
for the end user but it can also be used to make scripts as it is
fully compatible with Excel macro recording and VBA.

Have a look at SQL*XL at: www.oraxcel.com

2. Define your Excel workbook as a data source in ODBC
If you do this you can issue select statements against it. You can use
it in an openquery statement in SQL server or even make it a linked
server I suppose. Named ranges will appear as tables. Reserve row 1
for the column headers.
Problems with this technique: it is readonly, you may have problems
with accessing the workbook (in use)
The nice thing is that you can use it directly in SQL or DTS

3. You can write a little macro in Excel that dumps the file out in a
nominated format (CSV e.g) and a nominated directory. You could have
DTS to pickup the file and import it in your database table.

Hope this helps,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
Author of SQL*XL

does using sql parameters speed asp.net code up?

does using sql parameters speed asp.net code up?Depends on a lot of things. It's usually more secure than straight SQL code, which is what I'm assuming you're comparing this to. Also, if you are using stored procedures they are a bit speedier on the server side (but I don't know if you'd notice it, depends on the app).

But from a security perspective (to avoid SQL injection attacks), it's a good idea. IMHO|||I have a question about paramaters then.. I have only seen sample for INSERT how about UPDATE AND DELETE?

Here is my insert statement

SQL = "INSERT INTO table1(id, key_data) VALUES (@.ID, @.key)"

conn = New SQLConnection(SQLserver)
dbComm2 = New SqlCommand(SQL,conn)
dbComm2.Parameters.Add(New SqlParameter("@.ID", Session("id")))
dbComm2.Parameters.Add(New SqlParameter("@.key", KeyGeN))

How would i change these for delete and Update?

thanks|||xactly the same...just change your query

update table set col=@.var1, col2=@.var2 where col3=@.var3

then add the variables and their values like you did above

same for delete also

hth