Computer Science Canada SQL Insert Command, just learning; need help with syntax |
| Author: | asteffes [ Thu Jun 29, 2006 9:12 am ] |
| Post subject: | 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! |
|
| Author: | asteffes [ Thu Jun 29, 2006 12:27 pm ] |
| Post subject: | |
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! |
|
| Author: | r.3volved [ Thu Jun 29, 2006 2:51 pm ] |
| Post subject: | |
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` |
|
| Author: | wtd [ Fri Jun 30, 2006 12:05 pm ] |
| Post subject: | Re: SQL Insert Command, just learning; need help with syntax |
asteffes wrote: TimeEntryID (primary) [int] (should be an auto increment...not sure how to do this)
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". |
|