asked on

Date insert into MS SQL fails

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

Jim Horn

Do a Debug.Print sql2 so we can see the entire SQL statement.

jensjakobsen

ASKER

And the MS SQL server is a 2012. EE didn't have that option.

jensjakobsen

ASKER

How do I do a debug?

jensjakobsen

ASKER

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.

ASKER CERTIFIED SOLUTION

jensjakobsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jensjakobsen

ASKER

Excellent solution, perfect for me.