SQL Insert Command

Just learning, need help with syntax

[letsgetsilly]

I am creating a web application in VB where the user can enter into a database their different arrival/departure times during the day. It is my first web application with a database.

Attempted INSERT statement as a test:
INSERT INTO TimeEntry VALUES ('1', '1','10/06/2003 2:54:11 PM', '12/18/2002' ,'12/18/2003', '12/18/2004' ,'12/18/2005', '12/18/2006', '12/18/2007' ,'12/18/2008' ,'12/18/2009')
This statement recieves error:
"Syntax error converting datetime from character string."

My table columns are setup as:

TimeEntryID (primary) [int] (should be an auto increment...not sure how to do this)
UserID (foreign key) [int] (linked to another table)
CurrentDate[datetime]
ArrivalTime[datetime]
DepartLunchTime[datetime]
ReturnLunchTime[datetime]
LeaveTime[datetime]
ArrivalTimeSubmitted[datetime]
DepartLunchTimeSubmitted[datetime]
ReturnLunchTimeSubmitted[datetime]
LeaveTimeSubmitted[datetime]

I would just like to fill in some data so that I can play around with it.

I would like my TimeEntryID to be autogenerated, but I don't know how to do this.

Thanks for any help!

[LifeinAsia]

A lot depends on the database you're using.

1) How do you have the TimeEntryID field setup?

2) If you set it up for auto increment, do not pass in a value for it.

3) You will need to convert '10/06/2003 2:54:11 PM' into a time variable for input to the DB. I don't know enough about VB to help you, but I assume it must have a conversion routine of some sort to convert to/from time & string values.

4) Just curious- what's the difference between ArrivalTime & ArrivalTimeSubmitted?

[letsgetsilly]

The difference between ArrivalTime and ArrivalTimeSubmitted is the first one accepts the data input from the user and the second records the time they actually press the 'submit' button. Its kind of a way of checking their honesty, which I think is dumb.

Im using MS SQL Server 8.

[letsgetsilly]

TimeEntryID is set to auto increment, and I figured out I wasn't supposed to mess with it after a little while, thanks!

[LifeinAsia]

The difference between ArrivalTime and ArrivalTimeSubmitted is the first one accepts the data input from the user and the second records the time they actually press the 'submit' button.

Then doesn't that mean ArrivalTimeSubmitted, DepartLunchTimeSubmitted, ReturnLunchTimeSubmitted, and
LeaveTimeSubmitted are all identical?

So this is more of a timeclock for employees to clickin/clockout where people punch in/out, right? If so, you can't very well input all the values for each row each time a user submits info. (When the employee clocks in on arrival in the morning, how does he know when he's going to clock out for lunch?)

For TimeEntryID, you should set the "Identity" property to Yes.

Also, if you input all the time values with just the date parts (no time parts), it's not going to help you very much.

[john_k]

TimeEntryID is set to auto increment, and I figured out I wasn't supposed to mess with it after a little while, thanks!

So does that mean that you are no longer getting the conversion error?

[FalseDawn]

Then doesn't that mean ArrivalTimeSubmitted, DepartLunchTimeSubmitted, ReturnLunchTimeSubmitted, and
LeaveTimeSubmitted are all identical?

Not necessarily - some fields could be left NULL, to be updated later.

To go back to the original problem - I would try to specify the dates in an unambiguous manner like
YYYY-MM-DD for example.

If you use mm/dd/yyyy or dd/mm/yyyy there are potential issues with the days and months being confused. I would also prefer the "-" over the "/" character.

Try for example:'2006-06-20 18:00:00' as a test datetime string

[letsgetsilly]

That did the trick. I've got the insert to work properly, thanks for the help.

I'm using the insert command as just a starting off point because I'm new at this.

Yes, this is 'punch-in,punch-out' program that I'm making. Regarding the '...Submitted' columns, I am planning on creating a function that recieves information on which button is pressed and then records the Now.Today method to the appropriate field.

Here is what I am stuck on: I can create a new row for each day (currentdate), but how do I check to determine whether that row is in existence?

I have a function to determine whether the date is greater than/less than Now.Today, but what if the row doesn't exist?

Thanks for any help!

[LifeinAsia]

Do a SELECT statement with the appropriate date parameters. If you get no results back, the row doesn't exist.

[FalseDawn]

Bear in mind that unless the time element of the date time is always the same (eg 00:00:00), then the best way to check for a specific DAY is to use a range check on the datetime, eg

SELECT COUNT(*) FROM yourtable WHERE currentdate>='2006-20-06 00:00:00' AND currentdate<='2006-20-06 23:59:59'

will return 0 if there is no entry for 20th June.

[LifeinAsia]

Except that there may be rows for that date, but not for that employee in particular. Suggested fix:
SELECT COUNT(*)
FROM yourtable
WHERE userID=1 AND currentdate>='2006-20-06' AND currentdate<'2006-21-06'