Insert Query on table with identity column

[Dave]

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_Cli ck()
On Error GoTo Err_btnAddAppts _Click
Dim strsql As String
DoCmd.SetWarnin gs False
strsql = "INSERT INTO [tbl_appointment s] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"
DoCmd.RunSQL strsql
DoCmd.SetWarnin gs True
DoCmd.Close

Exit_btnAddAppt s_Click:
Exit Sub

Err_btnAddAppts _Click:
MsgBox Err.Description
Resume Exit_btnAddAppt s_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."

[Erland Sommarskog]

[posted and mailed, please reply in news]

Dave (dc********@ucw philly.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_Cli ck()
On Error GoTo Err_btnAddAppts _Click
Dim strsql As String
DoCmd.SetWarnin gs False
strsql = "INSERT INTO [tbl_appointment s] (lkp_emp_id, date_id, time_start,
time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID &
"', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails &
"')"
DoCmd.RunSQL strsql
DoCmd.SetWarnin gs 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_appointment s)
(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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Thug Passion]

> INSERT [tbl_appointment s)

(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.oledbComm and("Insert..." , dbConn)
cmd.Parameters. Add( New Paremeter("@lkp _emp_id", dbVarChar)

Then

cmd.Parameters( 0).Value = 7
cmd.ExecuteNonQ uery
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.