MS SQL Server ODBC - Insert failed

Author

Insert failed

Mark A. Sam

2005-08-05, 8:23 pm

I am running the code below on two similar tables. They both have an
identity insert field and both have a timestamp. The first routine labeled
Insert Product Materials runs fine, the second, Insert products mateirals,
fails.
This is the error:

error 3155: ODBC -Insert on linked table 'Customer ST Products Tasks '
Failed.

********************
********************

'Insert product's materials
strSQL = "INSERT INTO [Customer ST Products Materials] ( custProdID,
prodmatType, prodmatManuf, prodmatDesc, prodmatNotes, EntryDate,
TestRecord ) " & _
"SELECT " & newCustProdID & " AS custProdID, prodmatType,
prodmatManuf, prodmatDesc, prodmatNotes, #" & setEntryTime & "# AS
EntryDate, " & bolTestRecord & " AS TestRecord " & _
"FROM [Customer ST Products Materials] " & _
"WHERE ((([custProdID])=" & rstProds![CustProdID] & "));"

Debug.Print strSQL

CurrentDb.Execute strSQL

'Insert product's tasks
strSQL = "INSERT INTO [Customer ST Products Tasks] ( custProdID, prodTask,
[Benchmark Time], [Avg Time], EntryDate, TestRecord ) " & _
"SELECT " & newCustProdID & " AS custProdID, prodTask,
[Benchmark Time], [Avg Time], #" & setEntryTime & "# AS EntryDate, " &
bolTestRecord & " AS TestRecord " & _
"FROM [Customer ST Products Tasks] " & _
"WHERE ((([custProdID])=" & rstProds![CustProdID] & "));"

Debug.Print strSQL

CurrentDb.Execute strSQL

********************
********************

If I open the table 'Customer ST Products Tasks ' I cannot enter a record.
I get this error:

ODBC -Insert on linked table 'Customer ST Products Tasks ' Failed.
[Microsoft][ODBS SQL Server Driver][SQL Server]Explicit value must be
specified for identity column in table 'Customer ST Products Tasks' when
IDENTIY_INSERT is set to ON (#545)

but if i open table 'Customer ST Products Materials' directly I can enter a
new record.

There are several other tables in this procedure with Indentity insert
fields and they aren't giving me any trouble.

Any help will be appreciated.

God Bless,

Mark A. Sam

Sue Hoegemeier

2005-08-08, 3:23 am

It's hard to give you more specifics without the DDL for the
tables, the connection settings, etc but the error is pretty
much what it is telling you: the connection sets an
identity_insert on for the table and the insert command does
not specify a value. If you aren't specifying a value for
the identity column, then you need to turn identity_insert
off for that insert statement. You can find more information
in books online under the topic: SET IDENTITY_INSERT

-Sue

On Fri, 5 Aug 2005 15:28:30 -0400, "Mark A. Sam"
<msam@Plan-It-Earth.Net> wrote:

>I am running the code below on two similar tables. They both have an
>identity insert field and both have a timestamp. The first routine labeled
>Insert Product Materials runs fine, the second, Insert products mateirals,
>fails.
>This is the error:
>
>error 3155: ODBC -Insert on linked table 'Customer ST Products Tasks '
>Failed.
>
> ********************
********************

> 'Insert product's materials
> strSQL = "INSERT INTO [Customer ST Products Materials] ( custProdID,
>prodmatType, prodmatManuf, prodmatDesc, prodmatNotes, EntryDate,
>TestRecord ) " & _
> "SELECT " & newCustProdID & " AS custProdID, prodmatType,
>prodmatManuf, prodmatDesc, prodmatNotes, #" & setEntryTime & "# AS
>EntryDate, " & bolTestRecord & " AS TestRecord " & _
> "FROM [Customer ST Products Materials] " & _
> "WHERE ((([custProdID])=" & rstProds![CustProdID] & "));"
>
> Debug.Print strSQL
>
> CurrentDb.Execute strSQL
>
> 'Insert product's tasks
> strSQL = "INSERT INTO [Customer ST Products Tasks] ( custProdID, prodTask,
>[Benchmark Time], [Avg Time], EntryDate, TestRecord ) " & _
> "SELECT " & newCustProdID & " AS custProdID, prodTask,
>[Benchmark Time], [Avg Time], #" & setEntryTime & "# AS EntryDate, " &
>bolTestRecord & " AS TestRecord " & _
> "FROM [Customer ST Products Tasks] " & _
> "WHERE ((([custProdID])=" & rstProds![CustProdID] & "));"
>
>
> Debug.Print strSQL
>
> CurrentDb.Execute strSQL
>
> ********************
********************

>
>If I open the table 'Customer ST Products Tasks ' I cannot enter a record.
>I get this error:
>
>ODBC -Insert on linked table 'Customer ST Products Tasks ' Failed.
>[Microsoft][ODBS SQL Server Driver][SQL Server]Explicit value must be
>specified for identity column in table 'Customer ST Products Tasks' when
>IDENTIY_INSERT is set to ON (#545)
>
>but if i open table 'Customer ST Products Materials' directly I can enter a
>new record.
>
>There are several other tables in this procedure with Indentity insert
>fields and they aren't giving me any trouble.
>
>Any help will be appreciated.
>
>God Bless,
>
>Mark A. Sam
>
>
>

Mark A. Sam

2005-08-08, 7:23 am

Sue,

This doesn't make sense. The Identity Insert is a primary key intended to
be an auto number. The value should be assigned. It only happend on one
table. I have other tables with the same fields without a problem. This
happened to me a couple years ago when I first changed the tables from
Access to SQL Server. I don't recall if this table was the problem, but I
worked around it someohow, but I can't remember that either.

I'm working with an .mdb and linked tables, and not a project. Access
should handle this.

I guess I'll have to plan around with it.

Thanks and God Bless,

Mark

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:84idf1lpivjlhst
mqq64vf8kn8up3tv4dv@
4ax.com...
> It's hard to give you more specifics without the DDL for the
> tables, the connection settings, etc but the error is pretty
> much what it is telling you: the connection sets an
> identity_insert on for the table and the insert command does
> not specify a value. If you aren't specifying a value for
> the identity column, then you need to turn identity_insert
> off for that insert statement. You can find more information
> in books online under the topic: SET IDENTITY_INSERT
>
> -Sue
>
> On Fri, 5 Aug 2005 15:28:30 -0400, "Mark A. Sam"
> <msam@Plan-It-Earth.Net> wrote:
>
labeled[color=darkre
d]
mateirals,[color=dar
kred]
prodTask,[color=dark
red]
record.[color=darkred]
a[color=darkred]
>

Mark A. Sam

2005-08-08, 9:23 am

This issue cleared up.

Sponsored Links