Access Database Insert Error

[tate]

Ok, I'm really tired banging my head on the monitor over the correct syntax for this one. I'm trying to add a record to an Access database and I receive the following error;

 

Syntax error (missing operator) in query expression '8:00:00 AM'.

 

Here is the associated SQL statement I'm using;

 

datEventDate = CDate(txtDate.Text)

datStart = CDate(txtStart.Text)

datEnd = CDate(txtEnd.Text)

datToday = DateTime.Today

Dim strCmd As String

strCmd = "INSERT INTO tblEvent (AdminName, AdminDate, Event, EventDate, StartTime, EndTime, Location, Childcare, AddDetail) VALUES ('" & strAdmin & "', " & datToday & ", '" & txtEvent.Text & "', " & datEventDate & ", " & datStart & ", " & datEnd & ", '" & lstLocation.SelectedValue & "', '" & strChildcare & "', '" & txtComments.Text & "')"

 

Here is the data I'm attempting to add;

 

AdminName = determined in application

AdminDate = determined in application

Event = Test event

EventDate = 04/09/2005

StartTime = 08:00

EndTime = 21:00

Location = Other

Childcare = False

AddDetail = Its my birthday

 

 

Thanks for taking the time to help.

Tate

[SonicBoomAu]

In the Access database set the "EventDate & StartTime & EndTime" as a date / time and then set the format to either short date and short time.

 

Hope this helps

[tate]

The format within the Access table for the date fields is mm/dd/yyyy and the time fields were set to ShortTime. Yet, I receive the error?

[SonicBoomAu]

Just on the off chance try changing the format of those fields to text.

 

I have taken date formats off a form before but I used a datetimepicker not a text box.

 

Just had a quick thought. With the Access DB format being set to Short Date. When you convert the text box into a date using the CDate function, what format does it leave the date as? I.E. Short or Long.

Edited February 1, 2005 by SonicBoomAu

[tate]

The CDate(txtDate.Text) conversion will result in 4/9/2005. Based on the error it looks like something is wrong with the two time values (datStart and datEnd) not the date field.

 

Thanks again for your suggestions.

[tarheel_fan]

If your date fields are setup as date/time fields then you need to use the date delimiter (#) in front of and behind your date/time fields in your sql statement. I have run into that problem before and that has fixed it.

[tate]

I have tried the following with no luck;

 

VALUES ('" & strAdmin & "', #" & datToday & "#, '" & txtEvent.Text & "', #" & datEventDate & "#, #" & datStart & "#, #" & datEnd & "#, '" & lstLocation.SelectedValue & "', '" & strChildcare & "', '" & txtComments.Text & "')"

[tate]

After way too many hours I have been able to get the code to work. The problem was with a combination of things;

 

1. Dropdown list value needed to use Test property instead of Value

2. Checkbox field (boolean field) didn't need quotes surrounding it in the SQL

3. CDate conversion of time fields didn't work for Access ShortTime, so forced the value

 

 

CODE:

 

Dim strChildcare As String

If chkYes.Checked Then

strChildcare = "True"

ElseIf chkNo.Checked Then

strChildcare = "False"

Else

strChildcare = "False"

End If

 

Dim datToday As DateTime

Dim datEventDate As DateTime

Dim strStart As String

Dim strEnd As String

Dim strListItem As String

strListItem = lstLocation.SelectedItem.Text

datToday = DateTime.Today

datEventDate = CDate(txtDate.Text)

strStart = txtStartHr.Text + ":" + txtStartMin.Text

strEnd = txtEndHr.Text + ":" + txtEndMin.Text

Dim strCmd As String

strCmd = "INSERT INTO tblEvent (AdminName, AdminDate, Event, EventDate, StartTime, EndTime, Location, Childcare, AddDetail) VALUES ('" & strAdmin & "', " & DateTime.Today & ", '" & txtEvent.Text & "', #" & txtDate.Text & "#, #" & strStart & "#, #" & strEnd & "#, '" & strListItem & "', " & strChildcare & ", '" & txtComments.Text & "')"