Inserting Date Into SQL Server

Printable View

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

VB 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

:confused:

Thanks!:D

Change this
VB Code:
TheDate = CDate(Date.Today.ToString)'Todays date i.e... 6/20/2003
to
VB Code:
TheDate = Date.Today.ToShortDateString() 'Todays date i.e... 6/20/2003
Also what is the data type of the Date column in the Database?

Jun 21st, 2003, 12:39 AM
Arc

Nope i'm getting the same thing.

I'm using a DateTime datatype.

:confused:
Jun 21st, 2003, 06:40 AM
DevGrp
If the datatype for the DB column is DateTime, then you dont have to use a string variable. Do this
VB Code:
```
Dim TheDate As DateTime
TheDate = DateTime.Today
```
That should work.
Jun 21st, 2003, 01:49 PM
nswan

don't you need to put hashes around the date or is that just access?
Jun 22nd, 2003, 11:45 AM
Arc

Still getting the same error:eek:

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.
Jun 23rd, 2003, 05:49 AM
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.
Jun 23rd, 2003, 05:51 AM
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.
Jun 23rd, 2003, 05:55 AM
DevGrp

I just did a web app, and I had no problem inserting dates.
Jun 23rd, 2003, 10:17 AM
Arc

Quote:

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?
Jun 23rd, 2003, 10:23 AM
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...:confused:

This is really annoying.
Jun 23rd, 2003, 11:02 AM
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!
VB Code:
```
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.
VB Code:
```
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 ....
Jun 23rd, 2003, 11:10 AM
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:D
Jun 23rd, 2003, 11:13 AM
DevGrp

I was just about to suggest using parameters. It'll save you alot of headaches.
Jun 23rd, 2003, 12:24 PM
techgnome

Quote:

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:D

I wondered about that after I posted. I re-looked at the code, and thought "I wonder if he's thinking PHP?" :D 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.... :rolleyes: The other part that kinda ticked me off was that every one went all over the place......
Glad you got your answer though.