Solved

Date insert into MS SQL fails

Posted on 2013-01-17
6
237 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

832 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