Solved

Connection strings for two different sources (Excel & MS SQL)

Posted on 2014-03-19
2
273 Views
Last Modified: 2014-04-13
I have a connection string to read from a SQL data base and another to read from an Excel spreadsheet. However how do I handle a situation where I want to read records from an excel spreadsheet and insert them into my SQL database

example
Insert into DCR_transaction_master  SELECT * from [DCR_transaction_master$] where T2_transactionId='&TransactionId'

What connection string do I provide with this type of statement which is reading from Excel and writing to SQL DB?

regards
Pat
0
Comment
Question by:pclarke7
2 Comments
 
LVL 20

Accepted Solution

by:
Daniel Van Der Werken earned 500 total points
ID: 39939548
Pat:

This article seems to provide a really good way to do what you want, and it also shows the use of multiple connection strings, as you are asking about that:
http://code.msdn.microsoft.com/office/Imoprt-Data-from-Excel-to-705ecfcd

However, if that's not enough, I would do it this way. It might not be the fastest or most efficient, but it would work:

1. Write a method to insert the data into the SQL database using the connection string using SQLConnection and SQLCommand. Use the "using" statement:

Using (SqlConnection conn = new SqlConnection("SQL DB Connection String))
{
   // do the insert using a SqlCommand
}

Have this method take in the appropriate data structure (a Class or Struct as you would define it).

Then, in a separate method, read each row from the Excel sheet and populate the structure or class. Pass in that populated structure/class to the method that inserts it into SQL.
0
 

Author Closing Comment

by:pclarke7
ID: 39997785
Thanks Dan7el,
apologies for delay in replying

regards
Pat
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

791 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