Comments by Member 14183767 (Top 45 by date)

the excel sheet is uploaded to data table then i use sql bulkcopy to insert it into my database table

hi this is for excel , the duplicates get inserted into the database table , they are not on the excel sheets

hi , how can i do that? can the data not be inserted into the datatable once only?

hi guys , i am also inserting excel sheet to db almost the same way as trunglb but i insert it into my database table, problem is the data is being inserted mutliple times casuing duplicates e.g. if my sheet has two rows of data then this is what will be inserted : ParentId Date Caption Filename User

record 1 record 1 record 1 record 1 record 1

record 2 record 2 record 2 record 2 record 2

record 1 record 1 record 1 record 1 record 1

record 2 record 2 record 2 record 2 record 2

can i not check the datatable to see if the data is there first before its inserted to database ?

hi marciej , i managed to put the code together an it works , the problem is that it inserts the records from the excel sheets multiple times causing dupliacte records in my table, how can i prevent that ? like in the datatable? heres the snippet below: string ssqltable = "[dbo].[April]";


try{
string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName);
DataTable dt = new DataTable();
SqlConnection sqlconn = new SqlConnection(strConnString);

sqlconn.Open();
using (OleDbConnection connection = new OleDbConnection(sConStr))
{
connection.Open();
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
foreach(var sheet in sheets) //loop through the collection of sheets ;)
{
//your logic here...
string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
//get data

OleDbConnection oledbconn = new OleDbConnection(sConStr);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();


OleDbDataReader dr = oledbcmd.ExecuteReader();
{
//sql bulk copy here!

DataTable table = new DataTable("benlist");
table.Load(dr);


//set path
string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName);


Session["filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath);

SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
bulkcopy.DestinationTableName = ssqltable;


////Mapping Table column


bulkcopy.ColumnMappings.Add("IDNumber", "[IDNumber]");
bulkcopy.ColumnMappings.Add("DOB", "[DOB]");
bulkcopy.ColumnMappings.Add("BranchnameID", "[BranchnameID]");
bulkcopy.ColumnMappings.Add("BranchCode", "[BranchCode]");




bulkcopy.WriteToServer(table);

// }
connection.Close();
sqlconn.Close();

}

}
}


}
catch (Exception){}
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);

}

Hi , how can i fix 'OleDb SqlException: The Microsoft Access database engine cannot open or write to Excel file. It is already opened exclusively by another user, or you need permission to view and write its data.'

Many Thanks , it works

Hi Thank you it works! i had a bracket that was causing the syntax error

hi , i get the following error 'Syntax error in FROM clause.' and the number of rows in my sheets vary

hi thanks , i tried that first , i got an error saying 'syntax error in from clause'

hi , is there a way to get workbook name and store it in a db table?

the data import from excel is done on a separate page, then its redirected the another page where i have my gridview

yes BenReport is a stored procedure that i want to display on gridview

is there no way i can clear my tables/gridview before a new sheet is uploaded and the stored procedure is called?

hi i added the try catch block, the gridview is still showing the old data

hi thanks for ur input , my gridview is empty , but the tables have data

alright thank you

hey Maciej thanks for the changes , i implemented the code along with a try catch block it doesn't insert and i don't even get an error

i am confused Maciej, the loop checks for the sheet and then the sqlbulkcopy will copy all the data from the sheet in the loop. meaning i no longer need to specify string myexceldataquery?

hi , does this mean i no longer need this line of code : string myexceldataquery = "Select * FROM [$] ";?

what do i put in this line of code: string myexceldataquery = "Select * FROM [$] "; ?

thank you MadMyche it works!!!!

it exists i just checked

"An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code" Additional info 'Department'

i fixed that it throws an error on this code : if(rd["Departmet"].ToString() == "EPIP")

Department

i understand now what its doing thanks for that! but issue is that even when ive used ur solution i still get an error which is confusing me, plz help i am still learning

its still giving me the same error , this is my login page, i would like for the code to check what department is in PB_Register for each user and redirect to appropriate page

it doesnt work , i get an error "An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code" also rd seems to be casted as an integer

it doesnt work , how do i use the value from database table

yes

Hi , if i implement your solution it only shows one record

yes i have a break point on page load that helps me debug line by line, both BenReport and spTest skip the first row in the table

hi , thanks for ur response however i am using EFP PLUS and i have 9 sheets that i need to be renamed to a list of names that i chose, please help