Solved

Date insert into MS SQL fails

Posted on 2013-01-17
6
236 Views
Last Modified: 2013-04-19
Hi

I get an error when ever I try to insert a date. The error is:

"Microsoft OLE DB Provider for SQL Server error '80040e07'

Conversion failed when converting date and/or time from character string."

Below is the code.


<%
        'Hele denne SQL og efterfølgende udføres hvis betalende medlemskab er valgt og betalt med dankort
        ok_fkIntUserID2 = rsUser("pkIntUserID")
        ok_strFName2 = rsUser("strFName")
        ok_strLName2 = rsUser("strLName")
        ok_strKlub2 = rsKlubProlong("strKlub")
        ok_fkIntSeasonID2 = Session("season")
        ok_fkIntItemID2 = Session("vare")
        ok_dmyApproveDate2 = date()
        ok_strApproved2 = 1
        ok_intAccessLevel2 = 2	
        
            Dim con2, sql2, data_source2
        
            data_source2 = "Provider=SQLOLEDB;Data Source=xxxxxxxx;Initial Catalog=xxxxxx;User ID=xxxxxx;Password=xxxxxxx"
            sql2 = "INSERT INTO dbo.tbl103TransactionsManual (fkIntUserID, strFName, strLName, strKlub, fkIntSeasonID, fkIntItemID, dmyApproveDate, strApproved, intAccessLevel) VALUES " & "('"& ok_fkIntUserID2 &"', '"& ok_strFName2 &"', '"& ok_strLName2 &"', '"& ok_strKlub2 &"', '"& ok_fkIntSeasonID2 &"', '"& ok_fkIntItemID2 &"', '"& ok_dmyApproveDate2 &"', '"& ok_strApproved2 &"', '"& ok_intAccessLevel2 &"')"
        
            ' Creating the Connection Object and opening the database
            Set con2 = Server.CreateObject("ADODB.Connection")
            con2.Open data_source2
        
            ' Executing the sql insertion code
            con2.Execute sql2
        
            ' Done. Now Close the connection
            con2.Close
            Set con2 = Nothing		'execute sql
        %>

Open in new window

0
Comment
Question by:jensjakobsen
  • 5
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38789461
Do a Debug.Print sql2 so we can see the entire SQL statement.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 38789471
And the MS SQL server is a 2012. EE didn't have that option.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 38789472
How do I do a debug?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:jensjakobsen
ID: 39077744
As this problem still is very relevant for me, I sincerely hope that someone will help me.

I have googled a LOT about the problem (and even how to do a debug), but it's still not that clear to me what I need to do to solve the problem.
0
 
LVL 1

Accepted Solution

by:
jensjakobsen earned 0 total points
ID: 39078145
Having found this link which works I suggest that this solution is accepted.

http://p2p.wrox.com/classic-asp-basics/32475-asp-sql-server-datetime-field.html
0
 
LVL 1

Author Closing Comment

by:jensjakobsen
ID: 39093782
Excellent solution, perfect for me.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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