Identity column: retrieve

Showing posts with label retrieve. Show all posts

Wednesday, March 28, 2012

IF ELSE statement in WHERE clause

actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.

i have try to use:

if @.IssueUserID = ' ' Select...(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help....

================================================== ==
SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
FROM tbl_Receipt WITH (nolock) INNER JOIN
tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID

WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @.DateFrom, 103) AND CONVERT(datetime, @.DateTo, 103)) AND
(tbl_Receipt.IssueUserID = @.IssueUserID)
ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeIDHi,

A trick I have used in this case would be to code:

...
AND (tbl_Receipt.IssueUserID =
ISNULL(@.IssueUserID, tbl_Receipt.IssueUserID )
)
...

In other words, if @.IssueUserId is not present, you join the column to itself, which gets all the data...

I've done this in stored procedure reports where I can default the passed parameters, e.g. :

create proc myproc (... , @.IssueUserID int = NULL, ...)

HTH,

Bill|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(

so @.IssueUserID is a character data type, and when it's "blank" you don't want it to be considered in the where clause...?

try

AND ( (@.IssueUserID = ' ') OR (@.IssueUserID != ' ' AND table.column = @.IssueUserID))

HTH

Bill|||;) actually i really appreciate your help. the way u teach is working when i try in SQL Query Analyzer. When comes to the MS.NET reporting, the query is not working..so sad. :cool:|||Very confusing here whether you are checking for NULLs, zero-length strings ('') or values with just a single space (' '). Your MS.NET reporting layer may be interpreting things differently than Query Analyzer.

Your code indicates a single space, but that is an unusual value to be submitted by an interface to indicate the absence of data.

This code handles both NULL values and zero-length strings:

AND (nullif(@.IssueUserID, '') is null OR (nullif(@.IssueUserID, '') is not null AND table.column = @.IssueUserID))

Wednesday, March 21, 2012

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
--
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>sql

Wednesday, March 7, 2012

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @.@.IDENTITY statement.

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

It turns out, that the false behavior only occurs if a foreign key column is set. So I've included this in the snippet. I've also noticed, that the CursorLocation Property of the Connection has to be set to adUseServer for the SQL-Server 2005 to make this work, for SQL-Server 2000 it has to be adUseClient.

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestIt;Data Source=.")
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM TestView WHERE ID = 0", con, adOpenKeyset, adLockOptimistic, adCmdText
rs.AddNew
rs("Nr") = "1"
rs("Description") = "Hello Nr. 1"
If addlink = True Then rs("Test2_ID") = 1
rs.Update
MsgBox "ID of new record is " & CStr(rs("ID"))
rs.Close
con.Close
GoTo quit
fail:
MsgBox Err.Description
quit:
End Sub

Here comes the T-SQL code for creating the sample database:

use master
go
create database TestIt
go
USE [TestIt]
GO
CREATE TABLE [dbo].[Test2]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
)
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Test2_ID] [int] NULL,
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Test2] FOREIGN KEY([Test2_ID])
REFERENCES [dbo].[Test2] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Test2]
go
CREATE VIEW [dbo].[TestView]
AS
SELECT dbo.Test.ID, dbo.Test.Nr, dbo.Test.Description, dbo.Test.Test2_ID, dbo.Test2.Nr AS Nr2, dbo.Test2.Description AS Desc2
FROM dbo.Test LEFT OUTER JOIN
dbo.Test2 ON dbo.Test.Test2_ID = dbo.Test2.ID

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?

It's used in a complex bound form with an n-m relationship (3 records).

Needless to say changing this code to reference the tables directly is

a big change and lots of work.|||

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.
Thank you for any help
I found the source of the error.
Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).
The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn
where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

Im having the EXACT same problem. We have not changed our code. When migrating from SQL Server 2000 to 2005 we immediately noticed the following database update pattern start to fail in a number of areas.

1. A new record is added to a recordset (The record contains an Identity
column)
2. The recordset is posted back to the database (RS.UpdateBatch)
3. A value is changed at the UI
4. The recordset is again posted back to the database
5. > Boom < The BatchUpdate fails - Source="Microsoft Cursor Engine"
Description="Row cannot be located for updating. Some values may have been
changed since it was last read.">

We have traced this down to a difference in the way identity column
information is returned to a recordset after new records are inserted into
the database. I examined this issue from 2 perspectives. 1, I ran SQL
profiler on both databases for the same transaction, and 2, I persisted my
recordset to XML before and after the first batch update (the one in which
the record is INSERTED) to look for differences between SQL Server 2000 and
2005. Here's what I found:

In the traces below, you'd notice a SELECT @.@.IDENTITY which occurs
immediately after the initial INSERT statement for SQL Server 2000 but not
for 2005. Why should this be missing? Presumably ADO itself issues this
SELECT statement. So why doesn't it do so with 2005?

SQL Server 2000 Profile Trace - This works!
-

INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (8.803915900000000e+007, 47, 2000, NULL, NULL, 0, 1, 1, 'Aug 8 2006
3:10PM', 'I')

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=54 <<-- Good! ADO has gotten this value
into its recordset.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

SQL Server 2005 Profile Trace - This FAILS!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (4.51692e+008, 47, 2000, NULL, NULL, 0, 1, 1, '2006-08-08
15:21:24:000','I')

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

WHERE "PatientProcedureID"=0 <<-- Wrong! This value is not 0; As an
Identity column this is set to some value.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1

Here are the relevant abstracts from the recordsets serialized as XML
(complete XML attached, if you are interested). In the following snippets
you'll notice the following. Before the INSERT statement, the identity
column is not represented in the <z:row>. After the INSERT it appears.
However, with SQL Server 2000 this column is populated with the correct
value, but with SQL Server 2005, this column shows a value of 0 - incorrect.

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Can anyone help with this? This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted. This is a significant deviation of functionality between SQL Server 2000 and SQL Server 2005. Anyone using RS.BatchUpdate and identity columns will be affected. This should account for a significant number of Microsoft's customers. Has this been fixed?

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="451692044" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:55:12" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

<z:row PatientProcedureID="55" PatientID="451692044" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:55:12"
RecModType="I"/>

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

<z:row PatientID="759886920" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:59:57" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>

After update: PatientProcedureID shows as 0 in the <z:row> This is WRONG!!!:
-
<z:row PatientProcedureID="0" PatientID="759886920" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:59:57"
RecModType="I"/>|||

OK, I've found the solution. Basically, with SQL Server 2005, you need to
explicitly set the Resync behavior, although with SQL Server 2000,
developers who didn't set this explicitly have been getting away with the
default behavior in many (most?) cases. Here's a sample which is working for
me on SQL Server 2005, and I've checked as well that this is backward
compatible with SQL Server 2000.
RS.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
RS.Properties("Resync Command") = "SELECT * FROM VALLPATIENTSPROCEDURES

WHERE PATIENTPROCEDUREID IN

(SELECT @.@.IDENTITY FROM PROCEDURES)"
http://windowssdk.msdn.microsoft.com/en-us/library/ms676738.aspx
The *default* behavior for a recordset in which these properties are
unspecified has definitely changed from SQL Server 2000 to SQL Server 2005.
<grrr>Thanks Microsoft. It's always nice when the newer technology works
differently than the older technolgy did, and of course, if you can make it
more difficult for us as well, that's great too! :-\ </grrr>
Hope this helps someone else avoid the pain I just went through with this.
But I still think that MS should release a service pack to put this back to the way it used to be!

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

I ask this because if there is a bug (as Zoya Bashirova - MSFT says) I will try to escalate to point some attention on it (hoping to get some attention)
If this is a documented "feature" I will make my programmers work on it following Joseph Geretz suggestions.

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

|||As my developers says (according to Joseph Geretz findings), this has to do with the default behaviourof an ADO recordset keyset, where the default behaviour has changed from "autoresync" to something else...

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

Your developer is right, the default behavior changed. But the strange thing is, the behavior changed for views only. From my point of view this is not consistent. A view should behave exatly the same way as a table, which is not the case, so I call this a bug.

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Here comes the T-SQL code for creating the sample database:

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?
It's used in a complex bound form with an n-m relationship (3 records). Needless to say changing this code to reference the tables directly is a big change and lots of work.
|||

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.

Thank you for any help

I found the source of the error.

Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).

The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn

where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

SQL Server 2000 Profile Trace - This works!
-

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

WHERE PATIENTPROCEDUREID IN

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Here comes the T-SQL code for creating the sample database:

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?

It's used in a complex bound form with an n-m relationship (3 records).

Needless to say changing this code to reference the tables directly is

a big change and lots of work.|||

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.
Thank you for any help
I found the source of the error.
Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).
The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn
where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

SQL Server 2000 Profile Trace - This works!
-

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

WHERE PATIENTPROCEDUREID IN

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Friday, February 24, 2012

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Hi,

What is the reason for this behavior?

How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?

Best regards,

George

Hi,

this is not the best solution but try:

me.RecordSource="SELECT * FROM table1"

instead of

me.RecordSource="SELECT * FROM query"

It works for me.

Best regards, Matjaz

|||

Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.

From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?

Anyone any ideas?

Best regards, George

|||

Thanks

|||

Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.

George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si

Best regards, Matjaz

|||

Here is a small sample to reproduce the behavior:

Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Here comes the T-SQL code for creating the sample database:

GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go

Johnny

|||I have encountered this same problem too. Has anyone found a solution?

It's used in a complex bound form with an n-m relationship (3 records).

Needless to say changing this code to reference the tables directly is

a big change and lots of work.|||

Thank you for any help

|||

OdilonS wrote:

I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.
Thank you for any help
I found the source of the error.
Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).
The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....

oRs.ActiveConnection = oCn
where oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.

|||ADO 2.7, SQL Server 2005

SQL Server 2000 Profile Trace - This works!
-

SELECT @.@.IDENTITY

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>

UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1

This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.

Thanks very much for your help!

- Joseph Geretz -

SQL Server 2000: Before and after RS.Update - This works!

Before update: PatientProcedureID is null, not represented in the <z:row>:

After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:

SQL Server 2005: Before and after RS.Update - This FAILS!
-

Before update: PatientProcedureID is null, not represented in the <z:row>:

WHERE PATIENTPROCEDUREID IN

Nov 21, 2006:

FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us

I haven't tested this, but the lierature suggests that this will resolve the issue.

Thanks IgorB for bringing this to our attention (post on page 2 of this thread).

|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....

This is a very impacting thing on my environment...

Thanks to all!

|||

From my point of view this is a bug, although one could argue it is a "feature":

The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.

If you decide to escalate this issue, please keep us informed.

Thanks,

George

I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...

The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||

The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.

It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8

George

Wednesday, March 28, 2012

IF ELSE statement in WHERE clause

Wednesday, March 21, 2012

Identy Number

Identy Number

Wednesday, March 7, 2012

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Friday, February 24, 2012

Identity column not returnd after AddNew / Update with ADO and SQL 2005

Identity column

Blog Archive

About Me