Insert Query on table with identity column

I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.

tbl_appointment
------
1. appt_id (pk) -- identity column, seed 25, increment 1
2. date_id
3. time_start
4. time_end
5. appt_details
6. lkp_emp_id

Private Sub btnAddAppts_Click()
On Error GoTo Err_btnAddAppts_Click
Dim strsql As String
DoCmd.SetWarnings False
strsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True
DoCmd.Close

Exit_btnAddAppts_Click:
Exit Sub

Err_btnAddAppts_Click:
MsgBox Err.Description
Resume Exit_btnAddAppts_Click
End Sub

I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'.

It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."[posted and mailed, please reply in news]

Dave (dcermelixx@.ucwphilly.rr.com) writes:
> I cannot insert into my appointments table because the primary key and
> identity column, appt_id, cannot be added. What do I have to change in
> my SQL statement to add new records into this table? I'm using SQL
> Server 2000 BE with Access Data Project FE.
> tbl_appointment
> ------
> 1. appt_id (pk) -- identity column, seed 25, increment 1
> 2. date_id
> 3. time_start
> 4. time_end
> 5. appt_details
> 6. lkp_emp_id
>
> Private Sub btnAddAppts_Click()
> On Error GoTo Err_btnAddAppts_Click
> Dim strsql As String
> DoCmd.SetWarnings False
> strsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start,
> time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID &
> "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails &
> "')"
> DoCmd.RunSQL strsql
> DoCmd.SetWarnings True
> DoCmd.Close

This is not a good way of writing SQL statements. Try to enter
the value "It's good" in txtApptDetails to see what happens.

The above is open for an attack known as SQL injection, whereby an
attacker can change your SQL statement to do something you did not intend.

The remedy is to add parameterized statments:

INSERT [tbl_appointments)
(lkp_emp_id, date_id, time_start, time_end, appt_details)
VALUES (?, ?, ?, ?, ?)

The client library then takes care of necessary quoting, converting of
date formats etc. (The above presumes that SQL Server will interpret
the dates, which may not work well.)

I don't really know which client library you are using, so I can't tell
how you would do it. But should definitely investigate the possibilities.

> I did check through Access and through Enterprise Manager and it is
> setup correctly. So I returned all rows in enterprise manager to
> manually enter an appointment to the table. I get the same error when
> doing data-entry straight to the table.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity
> column 'appt_id'.

Since the INSERT statement looks OK, I would look into whether there is
a trigger on the table.

If you run the INSERT statement from Query Analyzer, do you get the
same error message? In such case, pay attention on whether the error
message includes a procedure name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> INSERT [tbl_appointments)
> (lkp_emp_id, date_id, time_start, time_end, appt_details)
> VALUES (?, ?, ?, ?, ?)

I agree, and I used to think this was silly.

What you need to do is set this up as a command object, and then add
five parameter objects to it. Right before you fire the command, you
plug in their values. Example:

Dim cmd as new oledb.oledbCommand("Insert...", dbConn)
cmd.Parameters.Add( New Paremeter("@.lkp_emp_id", dbVarChar)

Then

cmd.Parameters(0).Value = 7
cmd.ExecuteNonQuery

> If you run the INSERT statement from Query Analyzer, do you get the
> same error message? In such case, pay attention on whether the error
> message includes a procedure name.

Another thing is if you're using Access as your client, even though
it's basically just sending the SQL through to the backend database,
sometimes it'll parse it first, so it's a good idea to surround all
your field-names with [square brackets] to make it clear you're
talking about a field.