-----------------------------------

asteffes

Thu Jun 29, 2006 9:12 am


SQL Insert Command, just learning; need help with syntax

-----------------------------------

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!


-----------------------------------

asteffes

Thu Jun 29, 2006 12:27 pm



-----------------------------------

Ok, I was able to fix my INSERT problem by specifying it more concisely as:

'2002/12/13 12:12:23:343'

Now..

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

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? If it exists, I will have to use UPDATE vs INSERT

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!


-----------------------------------

r.3volved

Thu Jun 29, 2006 2:51 pm



-----------------------------------

You would need to have a select statement with some input value for the where clause

ex.
SELECT * FROM TimeEntry WHERE id=@id

if rows returned > 0 then update, otherwise if rows returned == 0 then insert
------------
that is MSSQL stored procedure syntax, but basically `@id` is the value you're sending into the statement and comparing to the column named `id`


-----------------------------------

wtd

Fri Jun 30, 2006 12:05 pm


Re: SQL Insert Command, just learning; need help with syntax

-----------------------------------

TimeEntryID (primary) 

This is the very first thing you need to figure out how to do.

Oh, and when you name the primary key for a table named TimeEntry "TimeEntryID", it makes good programmers sad.  Just call it "id".