How to Compare every record in Excel file and save to Database Table.

[Ehtesham Siddiq]

Hi,

I have my application in VS2008 and coded Vb.net.

My query is i want to save record from an excel sheet to my Database Table.

But i dont want to insert the Excel sheet as a whole rather i want to insert the record in my database table Row wise.Inserting the records is not the only thing but my major issue is i want to check and compare the Primary Key column of my Table with the Column in my Excel.

Suppose Table1 is my Sql Database Table,and RegistrationNo is the Primary Key Column.Similarly there is also a column of Registration Number in my Excel Sheet.

Now before inserting each record the RegistrationNo column should be compared.If the a registrationNo in the excel sheet say RegistrationNo 112 is already present in the table then it should not Insert the record and if its not present then it should insert.

Also the other issue is the number of columns in my Database table are more than the number of columns in my excel Sheet.So the columns that are not present in the excel sheet should be set to NULL while inserting the record in the Table.

Please help me with a link of similar example or any helpful code to do this.

[lolafuertes]

Just as a hint, for each row in your Excel sheet, you can write an insert statement like:

"IF NOT EXISTS (SELECT TOP 1 RegistrationNo FROM MyTable WHERE RegistrationNo = " & CurrentRowRegistrationNumber & ") INSERT INTO Mytable VALUES (..."

And for the NULL value , just set the literal NULL in the VALUES right place or change the data table to define the missing fileds in the Excel as nort required (accepting null).

Hope this helps