Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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

0
jensjakobsen
Asked:
jensjakobsen
  • 5
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Do a Debug.Print sql2 so we can see the entire SQL statement.
0
 
jensjakobsenAuthor Commented:
And the MS SQL server is a 2012. EE didn't have that option.
0
 
jensjakobsenAuthor Commented:
How do I do a debug?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
jensjakobsenAuthor Commented:
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
 
jensjakobsenAuthor Commented:
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
 
jensjakobsenAuthor Commented:
Excellent solution, perfect for me.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now