|
Subject:
|
Newby need to import Excel to SQL2005
|
|
Posted By:
|
TashaAT
|
Post Date:
|
7/23/2008 4:34:34 AM
|
Hi
I bought D2Store and now I want to add some features to the exsisting package. One of the main features that I need to add is to upload bulk products to the database.
I need help in how to design a form (step by step for dummies help) that will be able to do that.
Thank you for the help!
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/23/2008 4:43:53 AM
|
C# 2005 for Dummies  
Sounds like you are going to be doing a lot of work here. It would be best if you got started yourself and then ask for pointers with the code.
Forms programming can start simple, but quickly get complicated, so its best to start with a firm idea/spec, and then work from there..
Edit:
You can of course opt for 2008, but some of the newer 2008 features may be confusing if new to C# (IMO).
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 4:51:59 AM
|
Okay here is the code for the form that I did:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ProductUpload.aspx.cs" Inherits="ProductUpload" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:Button ID="ButtonUpload" runat="server"
Text="Upload Excel Spreadsheet" />
</td>
<td>
<asp:Button ID="ButtonView" runat="server"
text="View Excel Data" />
</td>
<td>
<asp:Button ID="ButtonImport" runat="server"
text="Import Excel Data" />
</td>
</tr>
</table>
<asp:Panel ID="PanelUpload" runat="server" Visible="false" Width="240px">
<asp:FileUpload ID="FileUploadExcel" runat="server" /><br />
Please select an excel file to import<br />
<asp:Button ID="ButtonUploadFile" runat="server" Text="Upload File" OnClick="ButtonUploadFile_Click" />
<br /><br />
<asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="PanelImport" runat="server" Visible="false">
</asp:Panel>
<asp:Panel ID="PanelView" runat="server" Visible="false">
</asp:Panel>
</div>
</form>
</body>
</html>
NOW HERE IS WHERE I AM NOT SURE:
The Code BEHIND
CAN I do this (more vb than C#)
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class ProductUpload : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ButtonUploadFile_Click(object sender, EventArgs e)
{
FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));
LabelUpload.Text = "Upload File Name:" & _;
FileUploadExcel.PostedFile.FileName & "<br>" & _;
"Type: " & _;
FileUploadExcel.PostedFile.ContentType & _;
"File Size:" & _;
FileUploadExcel.PostedFile.ContentLength & " kb <br>";
}
}
}
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 4:57:57 AM
|
It does not seem to like the
("~ExcelImport.xls)); part it has red lines under it....
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/23/2008 4:59:39 AM
|
OK, so you have two problems here. First off, you need to upload the Excel file.
Secondly, you need to pipe the data in the XLS file into the database correct?
I have to be honest, I've not done much file uploading in ASP.NET, but at a rough
glance the code looks ok. Does it work? Can you upload files using the form?
If so, move to problem 2, if not, what goes wrong?
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/23/2008 5:00:24 AM
|
quote:
Originally posted by TashaAT
It does not seem to like the
("~ExcelImport.xls)); part it has red lines under it....
And what does the compiler say? You need to ensure you give us as much information as possible! 
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 5:50:40 AM
|
Okay I compiled it 
This is the MAIN error it gives in my browser
Compiler Error Message: CS0103: The name '_' does not exist in the current context
Source Error:
Line 19: {
Line 20:fileUploadExcel.SaveAs(Server.MapPath("~ExcelImport.xls"));
Line 21:LabelUpload.Text = "Upload File Name:" & _; FileUploadExcel.PostedFile.FileName & "<br>" & _;
Line 22:"Type: " & _;
Line 23:FileUploadExcel.PostedFile.ContentType & _;
And then there are these:
Error 1 The name '_' does not exist in the current
Error 2 Only assignment, call, increment, decrement, and new object expressions can be used as a statement
Error 3 Operator '&' cannot be applied to operands of type 'string' and 'string'
Error 4 The name '_' does not exist in the current
Error 5 Only assignment, call, increment, decrement, and new object expressions can be used as a statement
Error 6 The name '_' does not exist in the current
Error 7 Only assignment, call, increment, decrement, and new object expressions can be used as a statement
Error 8 The name '_' does not exist in the current context
Error 9 Only assignment, call, increment, decrement, and new object expressions can be used as a statement
Error 10 The name '_' does not exist in the current context
Error 11 Only assignment, call, increment, decrement, and new object expressions can be used as a statement
Error 12 Operator '&' cannot be applied to operands of type 'int' and 'string'
Okay now where to start?
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/23/2008 5:55:54 AM
|
Ah, your from a VB background aren't you?
Scratch the underscores, we dont use them in the C# camp. Whitespace is ignored.
Also, strings are concatenated using "+" rather than "&".
You then mark the end of the statement using a ";".
e.g:LabelUpload.Text = "Upload File Name: " +
FileUploadExcel.PostedFile.FileName + "<br>";
Hope that makes sense 
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 5:56:52 AM
|
The Only assignment, call, increment, decrement, and new object expressions can be used as a statement error refers to:
FileUploadExcel
"Type: "
and the
"File Size:"
|
|
Reply By:
|
samjudson
|
Reply Date:
|
7/23/2008 6:05:04 AM
|
The following:
LabelUpload.Text = "Upload File Name:" & _;
FileUploadExcel.PostedFile.FileName & "<br>" & _;
"Type: " & _;
FileUploadExcel.PostedFile.ContentType & _;
"File Size:" & _;
FileUploadExcel.PostedFile.ContentLength & " kb <br>";
Should read:
LabelUpload.Text = "Upload File Name:"
+ FileUploadExcel.PostedFile.FileName
+ "<br>"
+ "Type: "
+ FileUploadExcel.PostedFile.ContentType
+ "File Size:"
+ FileUploadExcel.PostedFile.ContentLength
+ " kb <br>";
As Rob tried to tell you, you do not use '_' or '&' in C#, and the semi-colon only appears a the end of a 'statement', not at the end of every line (i.e. in the above example there is only one at the end of the last line, not one at the end of the other line).
I find it helps readability to put the '+' at the start of the following line, and indent it.
/- Sam Judson : Wrox Technical Editor -/
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 6:14:04 AM
|
I changed the code behind to:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class ProductUpload : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ButtonUploadFile_Click(object sender, EventArgs e)
{
FileUploadExcel.SaveAs(Server.MapPath("~ExcelImport.xls"));
LabelUpload.Text = "Upload File Name: " +
FileUploadExcel.PostedFile.FileName + "<br>";
FileUploadExcel.PostedFile.ContentType+ "Files Size:";
FileUploadExcel.PostedFile.ContentLength+"kb <br>";
Now it only gives me two errors :) PROGRESS!
Only assignment, call, increment, decrement, and new object expressions can be used as a statement and it is for both
FileUploadExcel.PostedFile.ContentType+ "Files Size:";
FileUploadExcel.PostedFile.ContentLength+"kb <br>";
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 6:16:51 AM
|
I just have to say you guys are AWESOME I live in South Africa and I always find it really hard to get help online - never mind such FAST responses!!!
THANK YOU SO MUCH!!!
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/23/2008 6:22:15 AM
|
Happy to be of help
I dont think I am alone here when I say:
"The more people actively using the forum and wanting to learn (not "please send code") the better."
Please give us a shout if you get stuck again.
On a side note:
I would strongly recommend getting youself a reference book though..
I find it invaluable when getting to grips with new things to actually
sit there and work through a chapter or two with examples. Yeah,
sometimes you hit duplicate stuff, but actually running through basic
examples can often get you asking questions in your own mind that can
lead on to better things
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 6:31:48 AM
|
I actually just got the digital download
ASP.NET 2.0 Website Programming: Problem - Design - Solution
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/23/2008 6:36:35 AM
|
Ah! Cool
Good thing about online docs is that you can search quicker
Take it from me though, NEVER underestimate the value of getting away from the screen with a tea/coffee and just reading
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/23/2008 12:13:38 PM
|
I stepped away from the screen
But now I am back and...
I have done the form and I can now upload an excel spreadsheet BUT I now want to show this data in a grid view!
(Never make it to easy :) )
So I assume I need to start looking at Connection Objects?
But everything I look at is SQL SQL SQL?
Can I do Dim xConnStr AS String?
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/24/2008 2:10:05 AM
|
Great, glad to hear you are making progress.
So we have the xls file uploaded to the server and stored away ok right?
Great, so then we need a way to open the xls file and parse its data, and commit it to a database for storage.
As some pointers:
You need to understand what a ADO.NET Data Provider is (specifically the Sql Server flavour as that is that you will be working with).
Now personally, I have not done much xls parsing with .NET, my first instinct would be to grab the PIA's and open up the xls file using the Excel DOM but, I am pretty sure you need Office installed on the server, and I am not too sure what the performance/reliability would be like in that environment (only done it via Windows apps). Other than that, you would need to understand the file structure to be able to extract the data from it.
Perhaps someone else could offer more insight there?
But yes, definately need to get your head round ADO.NET and the Sql Server Data Provider. If you have any queries with these, then please ask
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
7/24/2008 2:53:36 AM
|
You have two main choices if the Excel file is a binary one, if it's the more modern XML format then that offers another possibility.
- Open the file using Excel automation, this has the advantage of reasonable simplicity but requires Excel on the server and is notoriously flaky. It's not supported by Microsoft and unless you're very careful then an error can freeze the whole application.
- Connect via an OleDbProvider. There are specific connection strings for Excel, see www.connectionstrings.com. The trouble with this method is that the provider has to guess the data types from the initial rows of data and often gets it wrong. Depends on what your file contains.
Either way, once you have the data I would put it into a real database for manipulation, searching etc.
--
Joe (Microsoft MVP - XML)
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/24/2008 2:56:07 AM
|
OleDbProvider of course! I knew there must have been a better way! I would have been against using automation on a web server!
Thanks Joe!
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/24/2008 3:21:27 AM
|
What I need to do is have a spreadsheet where the client can capture all their products and then do a bulk upload this will then update stock levels prices and add new items.
This spreadsheet will then get pulled into the SQL DB.
Okay now I need you to move a few steps back here what do you mean by OleDbProvider?
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/24/2008 3:25:47 AM
|
Like I said, check out what a ADO.NET Data Provider is. This will explain the concept of what a Provider is. There are then spe*****ed versions of this depending on the type of data you are connecting to (which allows you to use DBMS-specific features as well as the core provider functionality). For example, the Sql provider for Sql Server, and OleDb for any OLE compliant data source.
Have a Google, learn what you can, give us a shout if any is unclear :)
Edit
Why is "c i a l i s" censored?!
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
7/24/2008 4:21:56 AM
|
quote:
Originally posted by robzyc
Edit
Why is "c i a l i s" censored?!
Rob
http://cantgrokwontgrok.blogspot.com
Search on WikiPedia for the s-c-u-n-t-h-o-r-p-e problem
Stupid really as you can always write v-i-a-g-a-r-a etc. as you did.
--
Joe (Microsoft MVP - XML)
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/24/2008 4:30:16 AM
|
Oh yeah, I understand the mechanics of it. Just seems odd. Dunno why they dont check to see if its part of a word first, and it is, then is the word whitelisted?
Doesn't seem like rocket science to me
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/24/2008 4:35:01 AM
|
It made me giggle !
Okay I am busy reading up on all the OleDB stuff at the moment!
|
|
Reply By:
|
samjudson
|
Reply Date:
|
7/24/2008 4:42:07 AM
|
Isn't "C i a l i s" a viagra like drug, commonly included in SPAM emails...
/- Sam Judson : Wrox Technical Editor -/
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/24/2008 4:45:37 AM
|
Apparantly so.. Which seems to lead to strange hallucinations of asterisks when mixed with "SPE ED"
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
Alex030203
|
Reply Date:
|
7/31/2008 9:05:24 AM
|
I am sorry that i am not able to help you /
I am just a beginner of Visual C#/
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/31/2008 9:17:28 AM
|
Erm, so the point of your post was what Alex030203?
Which reminds me, I need to get around to calling all those Hollywood babes and tell them they wont date me..
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
samjudson
|
Reply Date:
|
7/31/2008 9:18:39 AM
|
Could you tell them I wont even be calling them while you're on the phone...
Ta.
/- Sam Judson : Wrox Technical Editor -/
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/31/2008 9:20:15 AM
|
Sure! Anyone else need anything else not doing while I'm being helpfully unhelpful?
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/31/2008 2:40:19 PM
|
Yes all you helpfull people!
What would you recommend for an intranet system for a meduim sized company? It needs to be something that can cope with a lot of TIBKIS!
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/31/2008 2:45:29 PM
|
OK, at the risk of sounding stupid, WTF is TIBKIS?!
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
7/31/2008 2:49:16 PM
|
The Idiot Behind Keyboard In Seat!
TIBKIS
|
|
Reply By:
|
robzyc
|
Reply Date:
|
7/31/2008 2:59:54 PM
|
Oh! I've never heard that one..
Generally I thought they were just referred to as "Rob"
Rob
http://cantgrokwontgrok.blogspot.com
|
|
Reply By:
|
Imar
|
Reply Date:
|
7/31/2008 3:50:16 PM
|
quote:
Generally I thought they were just referred to as "Rob"
;-) Very funny..... ;-)
Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
|
|
Reply By:
|
TashaAT
|
Reply Date:
|
8/3/2008 2:45:50 PM
|
So I have managed this entire thing in C# screamed and slammed my table often!
ONLY TO HAVE THE CLIENT SAY:
Oh sorry is there a diffrence between C# and VB? (just put every swear word you know in here)
And now I am doing it in VB and just to be difficult the thing does not like me either.
I need to WALK AWAY FROM THE SCREEN SLOWLY..........
|
