Solved

Loading data into SQL Server from Excel using ADO

Posted on 2011-09-15
3
252 Views
Last Modified: 2012-05-12
I'm trying to load data into SQL server from Excel using ADO to run a stored procedure.  I'm passing parameters into the SP that will be inserted into the table.

I have it working okay except for a cell I have formatted as a date on the worksheet.

The VBA I'm using is :

cmd.Parameters.Append cmd.CreateParameter("TargetDate", adDate, adParamInput, Target_Date, 8)

Target_Date is defined as DATE in VBA.

The field in SQL server is of type Datetime

The parameter in th stored procedure is of type : Datetime

I think the ADO data type adDate is different to sql Datetime.   When I load data such as "01/01/2011"

It appears as 1900-01-07 00:00:00.000 in SQL Server.

Any ideas on how I can convert the Excel date to a date that is compatible with ADO & SQL Server?
0
Comment
Question by:lee_jd
3 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 250 total points
Comment Utility
Not sure about SQL server but Oracle often prefers a date in the unambiguous' 01-JAN-2011' format.  Might be worth a try.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 250 total points
Comment Utility
Before load convert date or Excel to SQL compitible format i.e as per the SQL Culture or default 'YYYY-MM-DD' format.
0
 
LVL 2

Author Comment

by:lee_jd
Comment Utility
The only way I can pass yyyy-mm-dd is be changing my Excel type to String.

When I pass this in to the parameter I get a type mismatch error.

If I change the parameter command to :

cmd.Parameters.Append cmd.CreateParameter("TargetDate", adVarChar, adParamInput, Target_Date, 8)

I also get a type mismatch error.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

763 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

6 Experts available now in Live!

Get 1:1 Help Now