|
Hi all ,
I need a function that imports data from excell 2007 to sql 2005 using ASP.net 2.0
Thanks in advance
|
|
|
|
|
private DataSet GetExcelData(string fileName)
{
try
{
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0;\"";
OleDbConnection conn = new OleDbConnection(sConnectionString);
conn.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds, "Final");
conn.Close();
return ds;
}
catch (Exception ex)
{
lblError.Text = ex.Message;
return null;
}
}
protected void Upload_Excel(object sender, EventArgs e)
{
if (UPExcel.PostedFile != null)
{
try
{
string fileName = UPExcel.PostedFile.FileName.Substring(UPExcel.PostedFile.FileName.LastIndexOf(@"\") + 1);
fileName = Server.MapPath(".") + @"\UploadedFiles\" + fileName;
UPExcel.PostedFile.SaveAs(fileName);
DataSet ds = GetExcelData(fileName);
if (ds.Tables["Final"] != null && ds.Tables["Final"].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables["Final"].Rows)
{
string sql = "";
if (dr["user_id"] != null && dr["user_id"] != System.DBNull.Value && dr["user_id"].ToString().Trim() != string.Empty)
{
sql = "INSERT INTO USER_MASTER (USER_ID,PASSWORD,FNAME,LNAME,ADDRESS1,ADDRESS2,CITY_ID,STATE_ID,COUNTRY_ID,EMAIL,EMAIL_VERIFIED) values(" + dr["user_id"] + ",'password','" + dr["user_firstname"] + "','" + dr["user_lastname"] + "','" + dr["user_address_1"] + "','" + dr["user_address_2"] + "'," + dr["user_region_id"] + "," + dr["user_state_id"] + "," + dr["user_cid"] + ",'" + dr["user_email"] + "',1)";
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql);
}
}
}
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
}
}
Regards,
Mayank Parmar
Senior Software Engineer
Amba Tech
Gandhinagar, India
|
|
|
|
|
I will created dynamic check boxes in panel but it losing the checked values when i reloads
Example :
no problem with bind data but when i click submit i need to reload the binddata() definitely other wise panel doesn't show any items.
if i reload then checked values are losing when i calling the submit button.
sub binddata()
dim ds as dataset // here i will take data source from which from database.
Try
ds=obj.GetSource()
For index = 0 To ds.Tables(0).Rows.Count - 1
chklist = New CheckBox
chklist.Text = ds.Tables(0).Rows(index).Item(0).ToString
chklist.ID = ds.Tables(0).Rows(index).Item(0).ToString
chklist.EnableViewState = True
Panel1.Controls.Add(chklist)
Panel1.Controls.Add(New LiteralControl("
"))
Next
end sub
problem below when i reloads the databind(). but i need to be definitely reloads the databind other wist panel doen't show any thing
Private Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles submit.Click
While index < ds.Tables(0).Rows.Count
DisplayPanel = CType(FindControl("panel1"), Panel)
chklist1 = CType(DisplayPanel.FindControl(ds.Tables(0).Rows(index).Item(0).ToString), CheckBox)
If Not chklist1 Is Nothing Then
chklist1.Text = ds.Tables(0).Rows(index).Item(0).ToString
If chklist1.Checked = True Then //here showing that not checking even thought i will check check boxes
objWeb.Addid(drlist.SelectedItem.Value, chklist1.ID)
End If
End If
index = index + 1
End While
end sub
this is haneef
|
|
|
|
|
how do i do full text search on a dataset
without using sql server full text search
can any one give me code
|
|
|
|
|
<br />
SELECT MT.strTextFildColumn<br />
FROM MyTable MT WITH (NOLOCK)<br />
WHERE MT.strTextFildColumn LIKE '%searhText%'<br />
This will search the strTextFildColumn for all the rows that contains the
text searhText.
if you change it to:
WHERE MT.strTextFildColumn LIKE 'searhText%'
it will search the strTextFildColumn for all the rows that starts with the
text searhText.
if you change it to:
WHERE MT.strTextFildColumn LIKE '%searhText'
it will search the strTextFildColumn for all the rows that ends with the
text searhText.
Hope it helps
Thomas
|
|
|
|
|
i have one excel need to upload to mySQL thru asp.net web application. How to import excel file to mySQL ? or how to import excel to datatable or xml? because alternatively i have code to convert xml to mysql.
Thanks.
Regards,
Chee ken
|
|
|
|
|
I am not sure if following is helpful or not but I first converted Excel file int .CSV using MS-Excel and then used following query.
LOAD DATA LOCAL INFILE '<file path="" and="" name="">' INTO TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
My query
LOAD DATA LOCAL INFILE 'C:\\ukpostcodes\\uk.pc.ll.csv' INTO TABLE postcodes FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
|
|
|
|
|
Hi all ,
I am developing a web project which has two phases (English,andArabic).
The english phase works well. The problem in the arabic one which appears in strange letters .
The strange in the matter is that the arabic project works good in localhost but when upload it to the server it appears as strange symbols.
I added the line below and the problem still .
Can any one help me ...
|
|
|
|
|
Does the server have the appropriate language pack installed?
|
|
|
|
|
Hello,
I have made a javascript code for validating some inputs when the user click on an insert button which inserts a new row in a database but i can't
associate the script function to the "onclick" event because it hase an other event I have tried an html server button but it doesn't work.
thanks.
Dad
|
|
|
|
|
Try OnClientClick event
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
thanks,but onclientcliect is a property when i tried it no action occur.
if you have a code exemple that will be great.
Dad
|
|
|
|
|
Here is an example.
All this is part of your aspx page (not the code-behind)
<code><script type="text/javascript">
function =ConfirmClick()
{
alert("On Client Click event! I am in =ConfirmClick function")
}
</script>
<asp:Button ID="SubmitBtn" runat="server" OnClientClick="ConfirmClick()"
OnClick="SubmitBtn_Click" Text="Submit" /></code>
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
Thanks alot,it works really good.
thank u again.
Dad
|
|
|
|
|
New user looking at sample code and I can't differentiate when to use the "'s on server and when not to.Sometimes I see runat=server, other times runat="server". What's the difference?
Yeppr
|
|
|
|
|
Yeppr wrote: What's the difference?
There isn't any difference other than A) Inconsistent coding or B)Buggy Visual Studio not always surrounding server in quotes.
|
|
|
|
|
i have no problem in sending the email, but my problem is that theres some unicode characters in the email which is not readable when viewed at the client. however i used UTF-8 encoding like:
mMessage.SubjectEncoding = System.Text.Encoding.UTF8;
mMessage.BodyEncoding = System.Text.Encoding.UTF8
heres what i do from the begining
i first open an html file and get its content in a string
sr = File.OpenText(System.Web.HttpContext.Current.Server.MapPath(@"~\Controls\Mailer\Templates\common.htm"));
mystring = sr.ReadToEnd();
then i replace some words in the string with my own, like:
mystring = mystring.Replace("%name%","unicode word");
and finally i send this string as html in the email body.
only the replaced words is not readable but the other unicode characters that was originally in the htm file is fine !
am using c# asp.net 2.0
i also want to note that everythin was going fine in .net 1.1 , that happened only when i switched to .net 2.0
can u please find any solution ?
i will really appreciate any idea
thanx in advance.
We never stop learning
|
|
|
|
|
Protected Sub btn_sub2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_sub2.Click
'view type selection
view_type = ddl_cesum.SelectedItem.Value
title_view = ddl_cesum.SelectedItem.Text
Session("view_type") = view_type
'estimate type selection
If rb_int.Checked Then
est_typ = "INITEST"
title_est = "Initial LRE"
ElseIf rb_scp.Checked Then
est_typ = "SCOPEEST"
title_est = "Scope LRE"
ElseIf rb_ph1.Checked Then
est_typ = "PHSE1EST"
title_est = "Phase I LRE"
ElseIf rb_ph2.Checked Then
est_typ = "PHSE2EST"
title_est = "Phase II LRE"
ElseIf rb_ph3.Checked Then
est_typ = "PHSE3EST"
title_est = "Phase III LRE"
ElseIf rb_ph4.Checked Then
est_typ = "PHSE4EST"
title_est = "Phase IV LRE"
ElseIf rb_ofct.Checked Then
est_typ = "OFCLEST"
title_est = "Official Cost"
End If
Session("est_typ") = est_typ
'period
'month and year selection
If rb_cemon.Checked Then
objBaseClass = New BusinessObjectBase
month_year = objBaseClass.get_month_year(ddl_ceyrs1.SelectedItem.Value, ddl_cemon.SelectedItem.Value)
from_date = month_year(0)
to_date = month_year(1)
title_mon_year = month_year(2)
'Quater and year selection
ElseIf rb_ceqtr.Checked Then
objBaseClass = New BusinessObjectBase
quater_year = objBaseClass.get_quater_year(ddl_ceyrs2.SelectedItem.Value, ddl_ceqtr.SelectedItem.Value)
from_date = quater_year(0)
to_date = quater_year(1)
title_quater_year = quater_year(2)
' Only year selection
ElseIf rb_ceyrs.Checked Then
objBaseClass = New BusinessObjectBase
year = objBaseClass.get_year(ddl_ceyrs3.SelectedIndex)
from_date = Year(0)
to_date = Year(1)
title_year = year(2)
End If
Session("from_date") = from_date
Session("to_date") = to_date
page_title = title_view & " " & title_est & " Estimate Totals for " & " " & title_mon_year & title_quater_year & title_year
Session("page_title") = page_title
Response.Redirect("Ce_sumlist.aspx")
Response.Redirect("Ce_sumlist.aspx?v_type=" &
view_type & "&e_typ=" &
est_typ )
End Sub
|
|
|
|
|
You forgot to ask a question.
---
single minded; short sighted; long gone;
|
|
|
|
|
No he didn't, he keeps posting crap on other boards too, and then votes you a 1 if you mention this to him...
What an idiot.
|
|
|
|
|
Hi
I am testing an ASP.NET site on Vista (it runs fine on XP and Server 2003).
We get a fault in the published site (not under debug though, on Vista) that I think is due to file permissions.
This was resolved on Server 2003 and XP bu granting permissions to the ASP.NET Machine Account (appears as ASPNET when granting permisions).
VS 2005, .NET 2 etc etc are all installed and (apparently) working fine, we can debug the site too.
I'd expect the account to have been setup as part of VS 2005 install, but it seems to be absent.
I did run aspnet_regiis -i too, but to no avail.
Any info much appreciated.
Hugh
Advanced Persistence
http://www.morantex.com
|
|
|
|
|
How can we combine a transparent GIF image with a JPG file and save the resulting image in disc.
I want the GIF to act as a frame that we can see the JPG image through it . Help me please.
Thanks.
|
|
|
|
|
You can't combine the files, you have to unpack them into images and combine them.
Load the JPG file into a BitMap object and the GIF file into another. Create a Graphics object for the first image and use that to draw the second image into the first. Save the result as a new image file.
---
single minded; short sighted; long gone;
|
|
|
|
|
Thanks , Can you mention function calls needed ??
|
|
|
|
|
The FromFile and Save method in the Image class and the FromImage and DrawImage methods in the Graphics class.
---
single minded; short sighted; long gone;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
