Solved

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
14
181 Views
Last Modified: 2010-04-23
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!

UPDATE
Work.Valuation
SET
 Work.Valuation.Insured = MARCHDETAILS.Insured,
Work.Valuation.LossDescription = MARCHDETAILS.LossDescription
FROM
MARCHDETAILS
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
0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 

Author Comment

by:VBBRett
ID: 17002806
is my question clear?
0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 17002962
What I would do is write a Data transformation Package in SQL2000 and and execute it from your VB.NET application
0
 

Author Comment

by:VBBRett
ID: 17002997
That's what I wanted to do but the boss wants it written programmatically.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:MacNuttin
ID: 17003016
Ask your Boss for clarification he may just want a VB.NET app that does the task. That the VB app executes a DTS as part of the solution may not matter to him. It is the standard way where the SQL server is 2000

Look here:
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21174856.html
0
 

Author Comment

by:VBBRett
ID: 17003040
Would a DTS decifer between whether it would update or insert a new record?  Does it know if there is something brand new or if something is being updated?
0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 17003233
I don't know so what I would do is use DTS to import MARCHDETAILS  into Work as a temp table then execute a stored proc from the DTS to handle the insert or updating of the records and finally drop the temp table
0
 

Author Comment

by:VBBRett
ID: 17003583
My manager wants nothing to do with DTS, so I am back to the drawing board on how I'm doing it programmatically with SQL Server and VB.NET on it's own.
0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 17003719
Too bad good luck
0
 

Author Comment

by:VBBRett
ID: 17003733
Thanks!
0
 

Author Comment

by:VBBRett
ID: 17003739
can you at least tell me how to join an excel spreadsheet and an a sql server table to join fields together?
0
 
LVL 3

Expert Comment

by:mazkot
ID: 17007093
You can place  your excel data in a dataset and work from there for the conditions to update or insert to the sql server.

0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 17009921
That's right you can pull in the SQL data table 'work' as well into a dataset in VB and join it to dataset for 'MarchDetails' using the designer in Visual Sudio
0
 

Author Comment

by:VBBRett
ID: 17010442
Well, I programmed the dataset in for both the Excel spreadsheet and the SQL Server table connection.  How do I pull that into the the designer after you code it in?
0
 
LVL 3

Accepted Solution

by:
mazkot earned 500 total points
ID: 17015039
just used the unique key as a condition to check if it exist in the sql server.. if it exist use the same key to update.. else insert.. you don't need to set data in the sql server in a dataset..
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question