SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date Error problem-need help urgent

    Hello
    I am having the following problem,
    I am using a asp front end and sql server 2000 backend and i am inserrting the data from asp to sql and while inserting , take the current date from the system by using library function Now().

    IT works fine in my machine where the date get added. but when it was run in server i get this error message
    Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.
    I have converted the date format in regional settings in that machine to the format which i am using in my machine.
    but still no luck. Need help very urgently

    thanks in advance
    with regards
    vimal
    Knowledge is all about Sharing

  2. #2
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post your code? Try making sure the server is running the same Locale as your local computer as the default could be different on both machines.

    Set it using session.LCID = 2057

    2057 is the locale ID for the United Kingdom. YOu will need to find out what it is for Germany.

    The LCID alters the way that dates, times etc are formatted so this is possibly what the error is.
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  3. #3
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is my insert query


    QryStr="Exec " & SPName & " 0," & GenCode & "," & _
    FormValues(1) & "," & _
    FormValues(2) & "," & _
    FormValues(3) & ",'" & _
    FormValues(4) & "','" & _
    FormValues(5) & "','" & _
    FormValues(6) & "','" & _
    FormValues(7) & "','" & _
    FormValues(8) & "','" & _
    FormValues(9) & "','" & _
    GeneralID & "','" & _
    now & "','" & _
    FormValues(11) & "'"

    and i am sending this to a stored procedure, and the problem i have is passing the value from application to stroed procedure.

    for in stored procedure i used getdate() function to insert into the table and so the problem is passing the value from application to stroed procedure.
    I had this problem even when directly adding the data from asp using an insert query

    with regards
    vimal
    Knowledge is all about Sharing

  4. #4
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Locale in my machine is german and locale of the server is norwegian,
    So how do i design in my machine so that it works both in my machine and in server
    plz explain me in detail how to set this in asp.

    with regards
    vimal
    Knowledge is all about Sharing

  5. #5
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this

    QryStr="Exec " & SPName & " 0," & GenCode & "," & _
    FormValues(1) & "," & _
    FormValues(2) & "," & _
    FormValues(3) & ",'" & _
    FormValues(4) & "','" & _
    FormValues(5) & "','" & _
    FormValues(6) & "','" & _
    FormValues(7) & "','" & _
    FormValues(8) & "','" & _
    FormValues(9) & "','" & _
    GeneralID & "','" & _
    "12/1/2005" & "','" & _
    FormValues(11) & "'"


    And then try this


    Try this

    QryStr="Exec " & SPName & " 0," & GenCode & "," & _
    FormValues(1) & "," & _
    FormValues(2) & "," & _
    FormValues(3) & ",'" & _
    FormValues(4) & "','" & _
    FormValues(5) & "','" & _
    FormValues(6) & "','" & _
    FormValues(7) & "','" & _
    FormValues(8) & "','" & _
    FormValues(9) & "','" & _
    GeneralID & "','" & _
    "1/12/2005" & "','" & _
    FormValues(11) & "'"


    If one works and not the other the error will be an inconistency between server and database locale settings.
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  6. #6
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vimalpercy
    Locale in my machine is german and locale of the server is norwegian,
    So how do i design in my machine so that it works both in my machine and in server
    plz explain me in detail how to set this in asp.

    with regards
    vimal
    create a global.asa

    in the global.asa put

    <script language="vbscript" runat="server">
    sub Session_OnStart
    session.lcid = 2057
    end sub
    </script>

    You will need to change the 2057 to be the same as the database server locale.

    Look here for a full list http://www.microsoft.com/globaldev/r.../lcid-all.mspx
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  7. #7
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am getting same error in both cases
    Knowledge is all about Sharing

  8. #8
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont really know how you format your dates in German/Norwegian. Did you try setting the LOCALE for Norwegian? If this fails you will need to try differnt formats of inserting the date. until it works. SQL Server is a nightmare when it comes to this as dates must be pretty much perfect for it to work with.
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  9. #9
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now i have added the norwegian locale in my global asa 1044.
    ANd now i tried to add that record in my machine and i am getting the same error which i got in server.(earlier it was working in my machine before addding the locase in global asa)

    the long date format in my machine and in server is dddd, MMMM dd, yyyy
    and short date format in my machine and server is M/d/yyyy

    so could you tell me how to format now into this format and insert into sql
    Knowledge is all about Sharing

  10. #10
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK try this

    Create a test page on your local machine and run it.
    THe test page should have nothing other than this in it

    <%response.write session.lcid%>

    now upload that page to your server and run it.

    DO you get the same reult on both your local machine and the server? I am also assuming that you are using the same SQL Server whether you are working locally or remotly. Is this correct?
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  11. #11
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Mr Wulie
    Thanks mate for your help.The trick is in the formating and i formated the date into mm/dd/yyyy format while inserting and it get added in both in my machine and in server. So hereafter i have to do this. when doing the project have to set the locale in which i am going to host the application in my global asa and develop that in my machine and do the formatting as it would for that locale.

    When comes to formatting dates in asp i found this excellent site which has almost all aspects of formating date.

    http://www.webthang.co.uk/tuts/tuts_...date_time1.asp

    thanks and regardsv
    vimal
    Knowledge is all about Sharing

  12. #12
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad you got it going Vimal
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •