Inserting Date Into SQL Server

[Arc]

I have a field set as Datetime and i have a variable that gets todays date... when i try to insert the date i get this error..

System.Data.OleDb.OleDbException: Syntax error converting datetime from character string.

Here is my code

Imports System.Data.OleDb
Public Class WebForm2
    Inherits System.Web.UI.Page
    Dim Conn As OleDbConnection, Command As OleDbCommand, DataRead As OleDbDataReader
 
 
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Title As String, Teaser As String, Full As String, TheDate As String
        Title = Request.Form("txtTitle")
        Teaser = Request.Form("txtTeaser")
        Full = Request.Form("txtFull")
        TheDate = CDate(Date.Today.ToString)'Todays date i.e... 6/20/2003
 
        ExecuteQuery("INSERT INTO tblNews (NewsTitle,NewsTeaser,NewsFull,NewsDate) VALUES ('Title','Teaser','Full','TheDate')", "Test")
 
        Conn.Close()
 
    End Sub
    Public Sub ExecuteQuery(ByVal SQL As String, ByVal DB As String)
        If Application("Connection") Is Nothing Then
            Try
                Conn = New OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings.Get(DB))
                Conn.Open()
                Command = New OleDbCommand(SQL, Conn)
                DataRead = Command.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                Response.Write(ex)
            End Try
        Else
            Response.Write("Couldn't execute Query, Already Connected. Close the connection and try again.")
        End If
    End Sub
 
End Class

I dont domprendeh why it's saying it cant convert the date



Thanks!

[DevGrp]

Change this

TheDate = CDate(Date.Today.ToString)'Todays date i.e... 6/20/2003

to

TheDate = Date.Today.ToShortDateString() 'Todays date i.e... 6/20/2003

Also what is the data type of the Date column in the Database?

[Arc]

Nope i'm getting the same thing.

I'm using a DateTime datatype.

[DevGrp]

If the datatype for the DB column is DateTime, then you dont have to use a string variable. Do this

Dim TheDate As DateTime
TheDate = DateTime.Today

That should work.

[nswan]

don't you need to put hashes around the date or is that just access?

[Arc]

Still getting the same error

System.Data.OleDb.OleDbException: Syntax error converting datetime from character string

I Tried

Dim TheDate as DateTime

TheDate = Datetime.Now

and

TheDate = DateTime.Today

But anyways i don't want to store the time just the date.

[cim3]

Dates and SQL server - what a nightmare..... The only reliable way I have found is to convert the date to the following format when inserting - dd/mmm/yy i.e. 12 apr 03. Hope this helps.

[cim3]

Also just noticed that in the title u are saying thast your are inserting into SQL server. If so use the sqldata objects and the parameter objects. Much faster.

[DevGrp]

I just did a web app, and I had no problem inserting dates.

[Arc]

Originally posted by DevGrp
I just did a web app, and I had no problem inserting dates.

And umm did you do it differently than i have already tried?

[Arc]

It's very odd that i can manually go into SQL Manager and manualy type in the string 6/20/2003 into the date field and it works just fine, but if i try to send the exact same string thru code it gives an error...

This is really annoying.

[techgnome]

*bitting my tounge*
Ah, screw it....
Why in the world do people insist on doing SQL this way?! And why do they possibly think that this will even work?
And 10 replies, and not a single right one...
For crying out loud. This will not work! Never has and NEVER WILL!

ExecuteQuery("INSERT INTO tblNews (NewsTitle,NewsTeaser,NewsFull,NewsDate) VALUES ('Title','Teaser','Full','TheDate')", "Test")

It pisses me off to see this kind of programming, and then no-one understands why it won't work.
Fleck! Pull your smeggin heads out of your arses!
THIS is what you need.

ExecuteQuery("INSERT INTO tblNews (NewsTitle,NewsTeaser,NewsFull,NewsDate) VALUES ('" & Title & "','" & Teaser & "','" & Full &"','" & TheDate &"')", "Test")

The reason it doesn't work is because you ARE PASSING IN THE LITTERALSTRINGS.... NOT THE VARIABLES..... they have to be concatenated together to form the full SQL ....

[Arc]

Ahh yep, that would be it . See i've been programming with PHP for the last 2 years where you pass variables as strings as i was trying to do. Getting used to VB again!

Thanks mate

[DevGrp]

I was just about to suggest using parameters. It'll save you alot of headaches.

[techgnome]

Originally posted by Arc
Ahh yep, that would be it . See i've been programming with PHP for the last 2 years where you pass variables as strings as i was trying to do. Getting used to VB again!

Thanks mate

I wondered about that after I posted. I re-looked at the code, and thought "I wonder if he's thinking PHP?" I'm also glad that you didn't take my rant too seriously, but it's such a common mistake and I don't understand why people think that it will work.... The other part that kinda ticked me off was that every one went all over the place......
Glad you got your answer though.