Problem in fetching excel sheet - ASP.NET Discussion Boards

I find the easiest thing is to write a script block in your code behind such as

"var ddlRoleId = " + ddlRoleId.ClientID + " ;"

Then refer to those variables in your blocks of javascript.

Christian Graus

Driven to the arms of OSX by Vista.

Hi,
I've created a web portal and i am planning to host it. I am using AsP.NET 2005 and SQL SERVER 2000
and my problem is that:

I want to know how to set the connection string in web.config file ,to be accessed by the application in a remote webserver
(in the case of local connection it's OK)
Plz help me out.

Thankx in Advance !!

Hi,

In web.config file ,put the server credentials in the connection string...

S Kumar

cud plz get me one example .

sample here:

add name="MapsaLoanConnectionString" connectionstring="Data Source=192.168.0.220;Initial Catalog=Mapsa;Persist Security Info=True;User ID=databaseUser;Password=123" providername="System.Data.SqlClient"

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

thanx ! but i wud like to get the DataSource to be autodetected by the code not with IP Address

Hi,
you can also use different connections at the runtime ,just you have to declare your connection string at runtime ie; in .vb or .cs file

S Kumar

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..

Check It[^]

cheers,
Abhijit
CodeProject MVP

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

Check this out:
d*\.dd

Sorry it's not wrking
Thanks

Hi
This one should work, Smile | :)

^\d+.\d{2}$

Thanks