TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
gouthami vasala
NA
14
0
excel insert if not exists in sql server
Oct 30 2009 2:42 AM
i want to export excel records to my sql server if not exists else it should show the exitsting records.
here where i code this is not workign properly .
is there any method to export excel records if not exists
here lblfilename constist excel fle and
this
.ddlSheets.SelectedItem.Text is takes by default excel first sheet
i want to insert those records which are not exists int the sql database?
how could i implement this?
try
{
p =
this
.lblFileName.Text;
this
.ddlSheets.SelectedIndex = 1;
//Create connection string to Excel work book
string
excelConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ Server.MapPath(p) +
@";Extended Properties=""Excel 8.0;HDR=YES;"""
;
//Create Connection to Excel work book
OleDbConnection
excelConnection =
new
OleDbConnection
(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand
cmd =
new
OleDbCommand
(
"Select * from ["
+
this
.ddlSheets.SelectedItem.Text +
"]"
,
excelConnection);
excelConnection.Open();
dReader = cmd.ExecuteReader();
sqlBulk =
new
SqlBulkCopy
(strConnection);
sqlBulk.DestinationTableName =
"track_new"
;
bool
flag =
true
;
while
(dReader.Read())
{
string
s =
""
;
string
excel =
""
;
SqlConnection
obj =
new
SqlConnection
(strConnection);
// SqlCommand objcmd = new SqlCommand("select * from track_new", obj);
SqlDataAdapter
da =
new
SqlDataAdapter
(
"select * from track_new"
, obj);
DataSet
ds =
new
DataSet
();
obj.Open();
da.Fill(ds,
"track_new"
);
excel = dReader[0].ToString();
if
(excel !=
""
)
{
int
i = 0;
for
(i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
//this.lblMessage.Text = objDataSet.Tables[0].TableName.ToString();
s = ds.Tables[0].Rows[i][0].ToString();
if
(ds.Tables[0].Rows[i][0].ToString() == excel)
{
s = ds.Tables[0].Rows[i][0].ToString();
flag =
true
;
break
;
}
else
{
flag =
false
;
continue
;
}
}
}
if
(flag ==
true
)
{
this
.lblMessage.Text =
"\n"
+
"These Records Already Exists in the Database"
+
"<br/>"
;
// this.ListBox1.Visible = true;
this
.ListBox1.Items.Add(s);
for
(
int
j = 0; j <
this
.ListBox1.Items.Count; j++)
{
this
.ListBox1.Items[j].Selected =
true
;
if
(
this
.ListBox1.Items[j].Selected)
{
this
.lblMessage.Text =
this
.lblMessage.Text +
this
.ListBox1.Items[j].Value.ToString() +
",\n"
;
}
}
}
//else
//{
// sqlBulk.WriteToServer(dReader);
// ClientScriptManager script = Page.ClientScript;
// script.RegisterStartupScript(this.GetType(), "alert", "alert('Records Successfully exported')", true);
// // dReader.Close();
//}
//obj.Close();
}
if
(flag ==
false
)
{
sqlBulk.WriteToServer(dReader);
ClientScriptManager
script = Page.ClientScript;
script.RegisterStartupScript(
this
.GetType(),
"alert"
,
"alert('Records Successfully exported')"
,
true
);
// dReader.Close();
}
//sqlBulk.WriteToServer(dReader);
//ClientScriptManager script = Page.ClientScript;
//script.RegisterStartupScript(this.GetType(), "alert", "alert('Records Successfully exported')", true);
}
catch
(
SqlException
s)
{
this
.lblMessage.Visible =
true
;
this
.lblMessage.Text = s.Message.ToString();
}
plesae i really need help and its very urgent i need to build it
Reply
Answers (
4
)
Need Help! No data when I export my crystal report into pdf file
Select multiple items from DataTable based on multiple criteria...