|
hi all
im trying to fetch excel sheet and store it in to sql server2000 which contains 20000+ records.
i tried thid code but it wont work
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;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class Excel2Sql : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void insertdata_Click(object sender, EventArgs e)
{
OleDbConnection oconn = new OleDbConnection
(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("sun16.xls") + ";Extended Properties=Excel 8.0");//OledbConnection and
// connectionstring to connect to the Excel Sheet
try
{
//After connecting to the Excel sheet here we are selecting the data
//using select statement from the Excel sheet
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
oconn.Open();
//Here [Sheet1$] is the name of the sheet
//in the Excel file where the data is present
OleDbDataReader odr = ocmd.ExecuteReader();
string fname = "";
string lname = "";
string mobnum = "";
string city = "";
string state = "";
string zip = "";
while (odr.Read())
{
fname = valid(odr, 0);//Here we are calling the valid method
lname = valid(odr, 1);
mobnum = valid(odr, 2);
city = valid(odr, 3);
state = valid(odr, 4);
zip = valid(odr, 5);
//Here using this method we are inserting the data into the database
insertdataintosql(fname, lname, mobnum, city, state, zip);
}
oconn.Close();
}
catch (DataException ee)
{
lblmsg.Text = ee.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Inserted Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Green;
}
}
protected string valid(OleDbDataReader myreader, int stval)//if any columns are
//found null then they are replaced by zero
{
object val = myreader[stval];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
protected void viewdata_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Integrated Security=true;Initial Catalog=FuelDB;Data Source=" + Server.MapPath("example.xls") + "Extended Properties=Excel 8.0");
try
{
SqlDataAdapter sda = new SqlDataAdapter("select * from emp", conn);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (DataException de)
{
lblmsg.Text = de.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Shown Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Green;
}
}
public void insertdataintosql(string fname, string lname,
string mobnum, string city, string state, string zip)
{//inserting data into the Sql Server
SqlConnection conn = new SqlConnection("Integrated Security=true;Initial Catalog=FuelDB;Data Source=" + Server.MapPath("example.xls") + "Extended Properties=Excel 8.0");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into emp(fname,lname,mobnum,city,state,zip)values(@fname,@lname,@mobnum,@city,@state,@zip)";
cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
cmd.Parameters.Add("@zip", SqlDbType.Int).Value = Convert.ToInt32(zip);
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void deletedata_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Integrated Security=true;Initial Catalog=FuelDB;Data Source=" + Server.MapPath("example.xls") + "Extended Properties=Excel 8.0");
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "delete from emp";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteScalar();
conn.Close();
}
catch (DataException de1)
{
lblmsg.Text = de1.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Deleted Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Red;
}
}
}
above code gives a error sheet$ not found, so
plz help me out in this plz
|
|
|
|
|
This is insane. Why would a page, when you load it, read an excel sheet and store it in a DB ? You'd only have to do this once, right ?
Your code is a nightmare on many levels. What are you trying to do ?
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
Now I am developing a website in asp.net with visual basic
my databases in MS SQL-Server 2005
my server is WINDOW S2003 R2
my web.confg file setting in authorization is
<authorization>
<deny users="?"/>
<allow users="*"/>
</authorization>
my website open in client system
ask the username and password (server password)
supply the username as "administrator" and password="xyz"
then the site is open
now we want is..
the site open without asking for username and password in the client system
please give me a solution for above problem is rectified
thanking you in advance
abglorie
|
|
|
|
|
What is the Authentication Settings in Virtual Directory ?
cheers,
Abhijit
CodeProject MVP
|
|
|
|
|
Hi,
In IIS ,under project properties check the anonymous access checkbox option .... so that it will never ask the username and password when you open in client system
S Kumar
|
|
|
|
|
already enabled anonymous access checkbox
my website description is XYZ
IP address is (all unassigned)
TCP port is 150
|
|
|
|
|
i have three fields ....
i want something like this
image image image image
name name name name
i m using datalist
wat this showing like this
image
name
image
name
....
....
which control suit for my requirement.....
|
|
|
|
|
can you put some code of it or you must better view the properties of it
and by the way it provies you to change the column position.
|
|
|
|
|
i am using this in my source
<asp:DataList ID="DataList1" runat="server" RepeatColumns="4" RepeatDirection="Horizontal">
<ItemTemplate>
<tr>
<td>
<asp:Image ID="Image1" ImageUrl='<%#"~/Images/EmpOM/"+Eval("TE_VC50_EmpImg") %>' runat="server" Width="132" Height="160"/>
<tr>
<td>
<%# Eval("TE_VC50_EmpName")%>
</td>
</tr>
<tr>
<td>
<%# Eval("TE_VC50_EmpDesignation")%>
</td>
</tr>
</td>
</tr>
</ItemTemplate>
</asp:DataList><br />
wat should i change
|
|
|
|
|
A data list won't do that, you need to use a repeater to try to build a table that conforms to your data layout.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
actually i m binding dat from database and i have three fields... in rep data also come like that.. wat i want in one row..
|
|
|
|
|
|
OK, so do what I said.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
As CG suggest, you can go for Repeater, or you can also use XSL.
cheers,
Abhijit
CodeProject MVP
|
|
|
|
|
Hi
i want to write regular expression for two decimal places,
Exact Like 1.23 or 3.21 only
Thanks
|
|
|
|
|
|
Sorry it's not wrking
Thanks
|
|
|
|
|
Hi
This one should work,
^\d+.\d{2}$
Thanks
|
|
|
|
|
Hi all,
I have datagrid dg1, paging is true and at a time it is displaying 10 records.I want to export this to excel.Whatever the data is displaying the datagrid at a time that will only export to excel.I dont want to display all the data from database.Is there any solutions .
I am using C#.
Thanks is advance
|
|
|
|
|
Just write a CSV, Excel will open that fine.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
|
Here is the coding for to display the gridview items to the excel when the button is clicked.
Using System.IO;
Using System.Text;
Button1_Click()
{
String attachment="attachment;filename=Gridview.xls";
Response.ClearContent();
Response.AddHeader("content-disposition",attachment);
Response.ContentType="application/ms-excel";
StringWriter sWriter=new StringWriter();
HtmlTextWriter htwWriter=new HtmlTextWriter(sWriter);
Gridview1.RenderControl(htWriter);
Response.Write(sWriter.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
|
|
|
|
|
hi.
on my project i need to carry the value of datagrid to next page.
the datagrid i have is edited by me and i want the value of link to next page
(the datagrid contains a linklabel and a imagebutton)
i tried it with sessions but dont know about fecthing value of selected row
|
|
|
|
|
If you want to carry a value to the page you're linking to, just put it on the query string. Don't abuse the session object.
And try posting code when you want help, so we can see what you're trying to do, and where it's broken.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
maybe i dont put the clear message
well i have a datagrid view extracting values name(linklabel) and imagename(imagebutton) from database
the paging is enabled i just want the value of linklabel to get fecthed for use in next page.
|
|
|
|