Solved

Loading data into SQL Server from Excel using ADO

Posted on 2011-09-15
3
259 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
[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
3 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 250 total points
ID: 36542789
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
ID: 36542913
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
ID: 36543282
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

632 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