473,320 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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."
Jul 20 '05 #1
2 3195
[posted and mailed, please reply in news]

Dave (dc********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
> 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.
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: JP SIngh | last post by:
I am having problems inserting data into a simple table Insert into Forums (Title, PageUrl, ContractId) Values ('54249', 'test01.asp',54249) Microsoft OLE DB Provider for SQL Server error...
14
by: Andre | last post by:
Hello Can anyone help me translate this from access so that it can work in mssql (i need to get next value, but cannot use identity as if row is deleted, another must get new next column number...
8
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record...
2
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request :...
8
by: carlospedr | last post by:
I have to insert data from about 30 tables into a single table (Users), to do so i used a cursor and a bit of dynamic sql, this should work fine if the tables have to do the select from had the...
3
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code...
5
by: .Net Sports | last post by:
I'm trying to insert records into an sql database coming from a page using the request ..form method. The table "general" has a primary key 'geid .' I get the following error: Cannot insert the...
10
by: MLH | last post by:
Suppose, in a multi-user environment, you have append query SQL in a VBA procedure that looks like INSERT INTO MyTable... and the next line reads MyVar=DMax("","MyTable... You can never be...
0
by: cgiguere | last post by:
Hello, Trying to just insert a simple record in DB and it fails. When I set debugger on, I see that execution jumps out by IntakeNummer to the Catch and of course, assigns value -1 to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.