Solved

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

Posted on 2014-03-19
2
259 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 19

Accepted Solution

by:
Daniel Van Der Werken earned 500 total points
Comment Utility
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
Comment Utility
Thanks Dan7el,
apologies for delay in replying

regards
Pat
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now