Delete Recordsets With Same Date And Line

Jul 23, 2005

Hi All!

I need help with a Statement!
I am working with an Access2000 DB.
I have the following Problem.


ChNrLinieDatum Code 39 Stückzahl BHL1 BHL2 BMRH
582-064L2.1008.03.2005 02:30:00FCAA14821701
582-064L2.1008.03.2005 02:30:00FCAA14871701
582-114L2.1208.03.2005 01:00:00FAC827501240
582-114L2.1208.03.2005 01:00:00FAC827441240
582-114L2.1208.03.2005 01:00:00FAC827501240
582-094L2.707.03.2005 19:45:00FAE74323481
582-094L2.707.03.2005 19:45:00FAE74489481
582-094L2.707.03.2005 19:45:00FAE74489481
581-294L2.807.03.2005 18:20:00FA8V2658221
581-294L2.807.03.2005 18:20:00FA8V2652221
581-294L2.807.03.2005 18:20:00FA8V2658221
582-114L2.1207.03.2005 17:45:00FAAR20721236
As you can see I have a few recordsets that are double. The Thing is, there
is an ID that makes them different.

I need a Statement that deletes the surplus records where 'Datum' and
'Linie' are identical to another record. 1 record has to remain of course.
I thought of something like this.

DELETE FROM tbAuswert
WHERE EXISTS(
SELECT *
FROM tbAuswert
WHERE (Linie AND Datum)
IN (
SELECT Linie AND Datum AS Suchkrit
FROM tbAuswert
GROUP BY Suchkrit
HAVING ((Count(Suchkrit)>1)
)
)

But I get an error:
You wanted to execute a Query that did not have the following expression
'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction

Perhaps you ccan help me.

Thanks
Julia

--
Message posted via http://www.sqlmonster.com

View 3 Replies


ADVERTISEMENT

DELETE In One Single Line...???

Sep 29, 2007

Hi,
I am executing the folowing statements in my stored procedure, I want to ask that can all these staments will be executed and combined in a single line of code, meaning can we run all these statemnts in a single line. if not, then any other optimized way to perform this task and other way which will execute with less load on the server.





Code Block

SET NOCOUNT ON
TRUNCATE TABLE WebNew_T_Dump
TRUNCATE TABLE WebNew_TOD_Dump
TRUNCATE TABLE WebNew_T_S_Dump
TRUNCATE TABLE WebNew_AT_Dump
TRUNCATE TABLE WebNew_MMM_Dump
TRUNCATE TABLE WebNew_MMT_Dump
TRUNCATE TABLE WebNew_TC_Dump
TRUNCATE TABLE WebNew_TRW_Dump
TRUNCATE TABLE WebNew_SM_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TL_Dump
TRUNCATE TABLE WebNew_TLC_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TP_Dump
TRUNCATE TABLE WebNew_TSS_Dump
TRUNCATE TABLE WebNew_IT_Dump
Thanks,

View 3 Replies View Related

Integration Services :: Delete Text File Created Through Flat Line Connection?

Jul 6, 2015

created a very basic flow in SSIS: extracted table data through ole db connection, added multicast and as end result i created a flat file destination (with .txt file) and a ole db destination.

My question is; how can i delete the .txt file before executing the flow again? Want to avoid that the .txt file has duplicated rows after a second execution of the flow. Is it possible to use scd component or is this way to complicated? A for each loop?

i need a similar solution for the data that will be transported through the ole db destination task....

View 2 Replies View Related

Date On One Line

Jan 8, 2008

I have this report that I am trying to create, below is my statement. My problem is that the billing address and provider address is in the same field. I wanted to create another column as I did called “BillingAddress1� so that I can have the Prodider address on the same line as the billing address, is this possible? How does it work?

Here is an example of my data.

I want to keep the Provider office in the provider column which is fine(300 Community Dr), but I want to have the billing address (P.O. Box 5200) to be in the Billingaddress1 column on the same line as the provider address. Can this be done? Even though we are working from the same field?


Select Distinct
p.pro_id1,
p.PRO_TAX1 as "TaxID",
p.Pro_XTYP as "ProviderType",
p.Pro_XTYP as "ProviderTypebill",
p2.Pro_XTYP as "ProviderTypebill",
p.PRO_Office as "PracticeName",
p.Pro_Lname as "ProviderFirstName",
p.Pro_Fname as "ProviderLastName",
p.Pro_sys as "System affiliate Field367",
--v.val_desc,
p.pro_pcs as PCS#,
p.pro_spec1 as "Specialty1",
p.pro_spec2 as "Specialty2",
p.pro_spec3 as "Specialty3",
p.pro_spec4 as "Specialty4",
p.pro_degree as "ProviderDegree",

p.pro_addr1 as "ProviderAddress1",
p2.pro_addr1 as "BillingAddress1",

p.pro_addr2 as "ProviderAddress2",
p2.pro_addr2 as "BillingAddress2",

p.pro_city as "ProviderCity",
p2.pro_city as "Billing Address2",

p.pro_state as "ProviderState",
p2.pro_state as BillingState,

p.pro_zip as "ProviderZip",
p2.pro_zip as "BillingZip",

p.pro_phone as "ProviderPhone",
p.pro_fax as "ProviderFax",
p.PRO_BTAG
--pro2.PRO_BTAG
---Need Billing address, billing city, billing zip



FROM dbo.pro p
JOIN pro p2
ON p.pro_id1 = p2.pro_id1

View 3 Replies View Related

Date Query - Produce Unique Line By Month

Oct 21, 2013

Aim –To produce a unique line by month for the FDMSAccountNo

I have created the following query (see below) which produces the following result set

FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000

SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate

[Code] ....

Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08

[Code] ....

View 1 Replies View Related

Use Script To Read Date From Specific Line Of Flat File

May 10, 2007

I have a few flat files that I need to read a date from. The date will always be on Line No 4 and will be in YYYYMMDD string format in chars 2-9 of line 4



Here's what I have so far to convert the string YYYYMMDD into date format... I don't know enough VB to be able to just hit Line 4... I am sure it's simple so could someone pelase put me out of my googling misery






Code Snippet

Public Sub Main()

'

Dim oStream As New IO.StreamReader("Z:TreasuryFilesdeals.dat")

Dim strReadLine As String, filedate As Date



'??? how to just read a date from the fourth line of the file ??

'strReadLine = oStream.ReadLine()

strReadLine = "A20050331" 'sample string variable

'convert the fourth line of the flat file to a date format

filedate = New DateTime( _

CInt(Strings.Mid(strReadLine, 2, 4)), _

CInt(Strings.Mid(strReadLine, 6, 2)), _

CInt(Strings.Mid(strReadLine, 8, 2)))

'write filedate to package variable

Dts.Variables.Item("User::Load_Date_Loan").Value = filedate

System.Windows.Forms.MessageBox.Show(CStr(Dts.Variables.Item("User::Load_Date_Loan").Value))

'

Dts.TaskResult = Dts.Results.Success

End Sub

View 4 Replies View Related

Line Of Code For &"find & Replace/delete&"

Jan 14, 2007

I have mysql type db in which I need computer to search for a key phrase of text that has several hundred occurances inside product table descriptions of products on live site. Once it finds the text, I would like for it to simply remove it, and look for the next occurance of the phrase and repeat the process until all are flagged and removed. I was told their may be a line of sql code that employs such a feature. Can anyone tell me what such a line would be?

View 1 Replies View Related

Reporting Services :: Date Labels On X Axis In SSRS Line Chart

Jul 27, 2015

What is the best optimised options to display the date labels with bi-weekly intervals starting date Date from one month to three months future from the current date?

As per above requirements, I have built the below line graph query and graph, currently it only displays the date labels as per data but I need to display date labels with bi-weekly intervals starting date from one month to three months future from the current date. Also another issue found, if there are few more date labels then does not show all labels on horizontal axis.

Select
/* Set Week End date as Sunday */
DATEADD(DAY, 7 - DATEPART(WEEKDAY, t.TaskBaseline0FinishDate), CAST(t.TaskBaseline0FinishDate
+1 AS DATE)) as[WeekEnd]
,count(t.TaskBaseline0FinishDate) as Baseline
,count(t.TaskFinishDate) as Finish

[Code] .....

View 3 Replies View Related

Match And Delete From Max(date)

Oct 8, 2007

I want to match dates from two tables, and I want to match the six latest dates. If I have a match a will delete it from one of the tables.
I will do a while loop and do a delete for every date that already exist. If ,for example, the third date not have a match the loop must do a break.
but if all six is a match they all will be deleted from one table.

how to check it?
max(date)
max(date) - 1
max(date) - 2
...
Some one ho know and understand the question?



View 4 Replies View Related

Find The Date For Yesterday, And Delete?

Feb 2, 2008

Hello there,Im not quiet sure this is the right forum, but what the...I got a database with eg. dates in..(Day-Month-Year)01-02-200802-02-2008 <-- Today03-02-2008
Then i want to find the date for yesterday, in this example 01-02-2008, and delete the record.. - How is this done?Hope you understand and can help me,Regards Jeppe Richardt

View 2 Replies View Related

Why Recordsets

Aug 8, 2005

why should one use Recordsets and not SQL itself in database work. Isn't the latter muvch faster, more portable, and needs to be learnt only once?

View 1 Replies View Related

How To Concatenate Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all 

View 5 Replies View Related

Multiple Recordsets

Mar 16, 2001

Is it possible to return multiple recordsets from SQL to ASP in a single query?

TIA
Pilot

View 5 Replies View Related

Returning One Of Three Recordsets

Jun 21, 2007

Hi all,

I've written a query which needs to return different records depending on its argument.

I've used an IF statment to test which of the three SELECT statments should be returned. However, when I try to access the data from VBA in Access I find that the recordset is empty, whereas the query returns thr correct data if I run it in the SQL environment. WHy is this? More importantly, how do I make it return records?


Code:


ALTER PROC [dbo].[spFilteredContractListByUser]

@UserID varchar(7) --The user's 'T' number

AS

PRINT 'User ID is' + @UserID

--Get this user's filter type from tbl

DECLARE @FilterType int

SELECT @FilterType = FilterID
FROM tblFindContractUserFilter
WHERE UserID = @UserID

-- Standard filter
-- Returning all queries matching the username
IF @FilterType = 1
BEGIN
SELECT DISTINCT [Acc No], Business, Type, [Log Date], [Logged By], [Ack. Date], [Ack. By], [Late?]
FROM qryQueriesWorkList
WHERE (suser_sname = @UserID)
END
ELSE IF @FilterType = 2
-- Return only queries acknowledged by @UserID
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] = dbo.getEmployeeName(@UserID))
END
ELSE IF @FilterType = 3
-- Return all the queries which have not been acknowledged
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] IS NULL)
END



The VBA code is:


Code:


rst.Open ("EXEC spFilteredContractListByUser '" & Me.cmbUserFilter.Value & "'")



Me.cmbUserFilter.Value contains the argument.

Thanks all

Dave

View 2 Replies View Related

Nested Recordsets

Nov 19, 2004

I need to decrease the amount of time it takes to return a set of data of the following format:

State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]

current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn

As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.

View 8 Replies View Related

How To Concatenate 2 Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ?

i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.

if i use union, all the results will be mixed.

thanks for the help!

View 7 Replies View Related

Passing Recordsets

Sep 26, 2007

Greetings,

I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.

What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.

I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).

What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?

Thanks for your ideas

View 7 Replies View Related

How Can I Remove The Line Feed/carriage Return In The Last Line Of The Exported Text File ?

Feb 27, 2007

Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:

original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line

The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90

Should I use script component to do it ? I am new for VB . Anyone would help me ?

Thank you all.

View 1 Replies View Related

Reporting Services :: Draw Trend Line For SSRS Line Chart 2005

May 4, 2012

I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.

This is the link [URL] ....

I need the same trend line for the Bar-Chart in SSRS 2005.

View 5 Replies View Related

Storing And Retrieving Line Breaks/newlines From Multi-line Textbox (C#)

Aug 31, 2007

I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it.  I wrote a basic commenting system in which someone my post a comment about something written on the site.  I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that.  Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!

View 4 Replies View Related

Delete Rows From A Table Matching A Calculated Date

Apr 15, 2008





Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated.

I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK.

When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.


Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is:

SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT
FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)

The flat file destination was mainly used to confirm the select.

Having confirmed my select, I changed the select in the OLE DB SOURCE as follows:

DELETE FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)

I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns.

Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters?

Thank you and God Bless.








View 9 Replies View Related

ISQL: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect Syntax Near ' '

Nov 8, 2006

G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies

View 3 Replies View Related

How Can I Use The Last Recordset In A SP That Return 3 Recordsets ?

Mar 9, 2005

Hello,

I have a SP1 that is calling another SP2,
SP2 is using select and returning records

So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)

When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."


How can I access the last recordset ?

View 3 Replies View Related

Browser Updateable Recordsets ?

Mar 26, 2000

I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net

View 2 Replies View Related

How To Output Multiple Recordsets On One Row Using SQL?

Nov 8, 2001

Hello Folks,

Using just SQL (within SQL Server 2000), I'm looking to output 2 (or more) recordsets from a one-to-many join between table all on one row. Help! Can someone please show me how to do this if it can be done.

Below is an example of the situation.

Thanks a lot folks, - Jerry


************* Tables *******************

** ITEMS **
ItemID
=======
10
11
12

** ITEMSTOPARTS **
ItemID ---- PartID ---- Funktion
=================================
10 -------- 100 ------- headlight
10 -------- 120 ------- taillight

** PARTS **
PARTID ---- Manf ---- ProductName
=================================
100 ------- M1 ------ Halogens
120 ------- M2 ------ Red Lights


************* Queries *******************

-- QUERY 1 --
==============
(dashes preserve indentation)

SELECT
- i.itemID AS ItemID,
- p1.partID AS HLPartID,
- p1.manf AS HLManf,
- p1.name AS HLName,
- p2.partID AS TLPartID,
- p2.manf AS TLManf,
- p2.name AS TLName


FROM - ZItems i
- - - - INNER JOIN ZItemsToParts ip
- - - - - - ON i.itemID = ip.itemID AND
- - - - - - (ip.funktion = 'headlight' OR
- - - - - - ip.funktion = 'taillight') AND
- - - - - - i.itemID = 10
- - - - LEFT OUTER JOIN ZParts p1
- - - - - - ON ip.partID = p1.partID AND
- - - - - - ip.funktion = 'headlight'
- - - - LEFT OUTER JOIN ZParts p2
- - - - - - ON ip.partID = p2.partID AND
- - - - - - ip.funktion = 'taillight'


************* Output *******************

Using "Query 1" I get this:
(Note: HL = Headlight; TL = Taillight)

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== ==============
10 ----- 100 ------ M1 ----- Halogens - NULL ----- NULL ---- NULL
10 ----- NULL ----- NULL --- NULL ----- 120 ------ M2 ------ Red Lights


I wish to output the proceeding two record set in one row
Any ideas how this can be done with SQL Server 2000
(with JOINS in the FROM clause)?
- DESIRED OUTPUT:

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== =============
10 ----- 100 ------ M1 ----- Halogens - 120 ------ M2 ------ Red Lights



-= End-O-Message =-

View 2 Replies View Related

Paging Large Recordsets

Oct 21, 2007

Here's a question you'll never quit hearing: is there a convenient way to page through large recordsets in SQL Server 2000?

I'm writing some software which, for all intents and purposes, works like a messageboard: users can create threads, leave replies, and so on.

I have about a half million records in a few of my tables, and some of my queries return 1000s of results. I'd prefer not to return 1000s of records all at once, so I don't want to page my records in code; I'd rather page them in SQL Server. Naturally, I want to page replies. However, I don't know of a convenient way to page records in SQL Server 2000.

View 1 Replies View Related

Opening Mulitple Recordsets

Jul 20, 2005

I have a single .asp page that opens a connection and then sequentiallyopens and closes 14 recordsets from stored procedures to obtain variousproduct information before closing the connection.Is it common practice to do something like this? Or is opening 14recordsets going to become a real problem when the page goes live and startsgetting high web traffic?Thank you in advance for any information you might provide.Dave

View 2 Replies View Related

Asp / Sql Server Dates In Recordsets

Jul 20, 2005

Hey,I've got an Classic ASP app pulling some records out of a DB, whichworked fine when it was Access. When we upped to SQL Server, all of asudden, when the Recordsets came back, all the dates were empty, eventhough the data was showing up in the DB.About a year ago (only code asp apps once and a while), I rememberseeing this and finding something that just had something to do with theorder in which something was being referenced, but I just can't for thelife of me remember what it was.The Dates are set as datetime, length of 8.Anyone know why this happens and a solution?many thanks,rob

View 1 Replies View Related

T-SQL (SS2K8) :: Dynamically Delete Data Based On Date Column

May 19, 2015

DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)

DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'

Actually the year stating form july.

Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.

So what I need is dynamically I want to delete the data every year prior to current year.

View 4 Replies View Related

Displaying A Trend Line (in Line Chart) In SSRS

Feb 7, 2007

We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.

View 1 Replies View Related

Dynamically Change The Color Of The Line On A Line Graph

Oct 26, 2007

I have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?

kam

View 4 Replies View Related

Closed Recordsets From Stored Procedures

Jun 18, 2001

Hello all,

I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.

So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.

Has anyone got any ideas on how to get round this?

Thanks
Dave Sykes

View 2 Replies View Related

@@IDENTITY Problem With Multiple Recordsets

Apr 24, 2008

Can anyone help with this problem.

I am doing some mods to an existing db web app in asp. I am providing a facility to copy a subset of related records in a few tables. I am using one recordset to read in the selected records and then copying the data into another recordset creating a new record in the same table. I have to then pick up the @@IDENTITY of the new record so that I can update linked records in other tables.

The @@IDENTITY value returned is null unless I close the selection recordset.

I have created a simplified version of the code to demonstrate this.

The underlying table is called 'test' and it has 2 fields, 'test_id', integer, identity and 'test_desc' varchar(50)

ASP Code


<%
dim objConn, objRS1, objRS2, objRS3

sub OpenDatabase()
'open database object here instead of in page
Set objConn = Server.CreateObject("ADODB.Connection")
'Connect object to database through global.asa application variable
objConn.Open = Application("test_ConnectionString")
'Create recordset object to use
Set objRS1 = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
end sub

sub CloseDatabase
Set objRS1 = Nothing
Set objRS2 = Nothing
objConn.Close
Set objConn = Nothing
end sub

function UnNull(numval)
if isnull(numval) then
UnNull="NULL"
else
UnNull=cstr(numval)
end if
end function


OpenDatabase

strSQLQuery = "select * from test where test_id in (1,2) order by test_id"
objRS1.Open strSQLQuery, objConn, 1, 3

do while not objRS1.eof
objRS2.Open "test", objConn, 1, 3
objRS2.AddNew
old_test_id = objRS1("test_id")

objRS2("test_desc") = objRS1("test_desc")
objRS2.Update
objRS2.Close

' *1 objRS1.Close

strSQLQuery = "select @@IDENTITY as new_id"
objRS2.Open strSQLQuery, objConn
new_test_id=objRS2("new_id")
objRS2.close

response.write("Old ID = " & UnNull(old_test_id) & ", New ID = " & UnNull(new_test_id))
response.write("<p>")

' *2 response.end

objRS1.movenext
loop

objRS1.close

CloseDatabase

%>



Output

Old ID = 1, New ID = NULL
Old ID = 2, New ID = NULL

If I unrem the lines *1 and *2 then the output is

Old ID = 1, New ID = 11

I realise that there are other ways of doing this but this is only a simplified example whereas the actual application is far more complicated.

Thanks is advance

Kennedy

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved