Inserting DateTime into SQL Server CE database via VB.NET

Question

I am trying to insert into a database using the code below, however, every time I get an error:

There was a syntax error in the date format. [ Expression = @DOB ]

I have tried pretty much every date format available, tolongdate, toshortdate etc & no matter what I use this error shows.

The database column format is datetime. Any ideas where I am going wrong?

Code:

' Insert New User - Create Connection
Dim sqlConn As New SqlCeConnection(My.Settings.CompDbConnectionString)

' Open Connection
sqlConn.Open()

' Query DB
Dim sqlComm As New SqlCeCommand("INSERT INTO Profiles(Title, FirstName, LastName, DOB) VALUES(@Title, '@FirstName', '@LastName', '@DOB')", sqlConn)

' Add Parameters
sqlComm.Parameters.Add(New SqlCeParameter("@Title", SqlDbType.NVarChar)).Value = ComboTitle.SelectedItem.ToString()
sqlComm.Parameters.Add(New SqlCeParameter("@FirstName", SqlDbType.NVarChar)).Value = txtFirstName.Text
sqlComm.Parameters.Add(New SqlCeParameter("@LastName", SqlDbType.NVarChar)).Value = txtLastName.Text
sqlComm.Parameters.Add(New SqlCeParameter("@DOB", SqlDbType.DateTime)).Value = DteDOB.Value.ToUniversalTime

' Insert Into Database
Try
    sqlComm.ExecuteNonQuery()
Catch ex As Exception
    MessageBox.Show("Failed To Update Your Details:" & vbCrLf & ex.Message)
    Exit Sub
End Try

' Close Connection
sqlConn.Close()

In this code VALUES(@Title, '@FirstName', '@LastName', '@DOB') try removing the apostrophes — tcarvin
– tcarvin, Commented Jul 12, 2012 at 18:44

LarsTech · Accepted Answer · 2012-07-12 18:46:13Z

5

Try killing the apostrophies:

INSERT INTO Profiles(Title, FirstName, LastName, DOB) VALUES (@Title, '@FirstName', '@LastName', '@DOB')

should be just:

INSERT INTO Profiles(Title, FirstName, LastName, DOB) VALUES (@Title, @FirstName, @LastName, @DOB)

answered Jul 12, 2012 at 18:46

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jim Over a year ago

You beat me to it! Didn't notice before I posted my answer.

LarsTech Over a year ago

@Jim tcarvin's comment beat me as well.

SOL1102 Over a year ago

Damn, for some reason I thought this was best practive. Will approve your answer in 5 mins when I am allowed to! Thanks for your help :-)

LarsTech Over a year ago

@Chris Parameters are definitely best practice (to avoid sql injection), but by adding the single quotes to the sql, it turned the parameters into a text and your database was looking for a date type.

Jim · Accepted Answer · 2012-07-12 18:47:25Z

0

Take the single quotes out from the @DOB in the INSERT statement. They aren't needed.

answered Jul 12, 2012 at 18:47

Jim

6,88113 gold badges48 silver badges72 bronze badges

Collectives™ on Stack Overflow

Inserting DateTime into SQL Server CE database via VB.NET

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related