PDA

Click to See Complete Forum and Search --> : Inserting Date Into SQL Server

Arc
Jun 20th, 2003, 06:42 PM
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

:confused:

Thanks!:D
DevGrp
Jun 20th, 2003, 07:41 PM
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
Jun 21st, 2003, 12:39 AM
Nope i'm getting the same thing.

I'm using a DateTime datatype.

:confused:
DevGrp
Jun 21st, 2003, 06:40 AM
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
Jun 21st, 2003, 01:49 PM
don't you need to put hashes around the date or is that just access?
Arc
Jun 22nd, 2003, 11:45 AM
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.
cim3
Jun 23rd, 2003, 05:49 AM
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
Jun 23rd, 2003, 05:51 AM
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
Jun 23rd, 2003, 05:55 AM
I just did a web app, and I had no problem inserting dates.
Arc
Jun 23rd, 2003, 10:17 AM
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
Jun 23rd, 2003, 10:23 AM
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.
techgnome
Jun 23rd, 2003, 11:02 AM
*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
Jun 23rd, 2003, 11:10 AM
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
DevGrp
Jun 23rd, 2003, 11:13 AM
I was just about to suggest using parameters. It'll save you alot of headaches.
techgnome
Jun 23rd, 2003, 12:24 PM
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.