4
Reply

excel insert if not exists in sql server

gouthami vasala

gouthami vasala

Oct 30 2009 2:42 AM
5.6k

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
 
 
 

Answers (4)