Visual Basic/SQL Date help

Discussion in 'OT Technology' started by XtremelyCanadian, Mar 22, 2004.

  1. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    Im making a little program to track outstanding service work for a company in Visual Basic with a SQL Server 7 database. Ive run into some trouble trying to pass a date to the database and get a :

    "[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime"

    Here is my code, the Finished variable is a text box which is formatted to a date variable. Then it is passed as a date variable throughout my various tiers.

    Presentation Layer (checking which radio button is pushed and entering a date if yes is selected)
    Business Layer
    Data Access Layer
    SQL Procedure
    ANY HELP ?????
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Hmm - it's still a string. Make it a date type variable and if need be convert the string (although I think VB will do it without) using CDate(finished).
     
  3. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    Tryed CDate, still the same error, an error only comes up when i enter a date like
    "20/04/2004" or "15/01/2003"...where one of the numbers is over twelve. If both of the numbers are below twelve then it goes through alright. Do VB and SQL identify the dates differently ???? because even if i try to reverse it and put "04/20/2004" it still gives me the error.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Hmmm well you're still sending the date as a string though (you're enclosing it in quotes). Once you have a lefit datetime variable, you should be able to send it through the SQL server without anything around the date. Access uses #'s to signify a date literal but SQL server I believe doesn't do this. It just needs to know what to expect.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Oh yeah and in the first layer you refer to it as Finsihed not FinishedDate
     
  6. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    Thanks for the help, but i figured out that SQL only accepts dates in the format of year/month/day :hs:. Thats what i get for losing my SQL book.
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    When I was looking it up it did mention that it was picky but there were more ways than one to send the date. But whatever, it's cool you got it working regardless.
     

Share This Page