SOL1102
SOL1102

Reputation: 512

Inserting DateTime into SQL Server CE database via VB.NET

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()

Upvotes: 1

Views: 3815

Answers (2)

Jim
Jim

Reputation: 6881

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

Upvotes: 0

LarsTech
LarsTech

Reputation: 81675

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)

Upvotes: 5

Related Questions