How would I connect this Excel Spreadsheet with the SQL Server table and update or insert the records into the table?
Posted on 2006-06-28
I am writing a VB.NET program and I understand I would need an ole db connection string for the Excel spreadsheet and a sqlconnection string for the SQL Server database and table. My question for you is how would I get them to work together? Here is the problem. Let's say the fields are identical, meaning each column has the same name..how would I use the excel spreadsheet to upload into the SQL Server Database. I'm going to write a query, now assume the Excel spreadsheet is a table, and the SQL Server table is in the SqL Server database. How do I get them to work together? Below is an update query, but if there are new records, I want them inserted from the Excel spreadsheet. All this has to be done programmatically from a vb.net application. But here goes.
Assume the excel spreadsheet is named MARCHDETAILS, the SQL Server database name is Work and the table in the SQL Server database is named Valuation.
Now I haven't put the Insert query in because I am not sure how to go about it. Please help!
Work.Valuation.Insured = MARCHDETAILS.Insured,
Work.Valuation.LossDescription = MARCHDETAILS.LossDescription
INNER JOIN Work.Valuation ON MARCHDETAILS.LOB = Work.Valuation.LOB
and MARCHDETAILS.PolicyNO = Work.Valuation.PolicyNO
and MARCHDETAILS.OccuranceNO = Work.Valuation.OccuranceNO
and MARCHDETAILS.PolOccNO = Work.Valuation.PolOccNO
and MARCHDETAILS.PolComm = Work.Valuation.PolComm
and MARCHDETAILS.LossDay = Work.Valuation.LossDay
and MARCHDETAILS.RiskState = Work.Valuation.RiskState
and MARCHDETAILS.FirstNotice = Work.Valuation.FirstNotice
and MARCHDETAILS.ValnMo = Work.Valuation.ValnMo
and MARCHDETAILS.AccYr = Work.Valuation.AccYr