Unwanted recordset behaviour !!!
-
Dear all, I have the following: set rs = db.openrecordset ("select fname, lname from employee") while not rs.EOF with rs .delete end with rs.movenext loop When the code is run, data is directly deleted from my actuall table??? i dont want this to happen, i want to initialize this recordset and refill it with another data. it is deleting the table records directly even without updating (.Update) !!! I need an empty recordset to work on. The only way (i know) to create a recordset is to create one from a given table through an SQL stat. I couldnt find a way to create an empty recordset from scratsh or a dummy recordset without a predefined data source. Help !!!!
0 will always beats the 1.
-
Dear all, I have the following: set rs = db.openrecordset ("select fname, lname from employee") while not rs.EOF with rs .delete end with rs.movenext loop When the code is run, data is directly deleted from my actuall table??? i dont want this to happen, i want to initialize this recordset and refill it with another data. it is deleting the table records directly even without updating (.Update) !!! I need an empty recordset to work on. The only way (i know) to create a recordset is to create one from a given table through an SQL stat. I couldnt find a way to create an empty recordset from scratsh or a dummy recordset without a predefined data source. Help !!!!
0 will always beats the 1.
Try
select fname, lname from employee where 1=1that should get you back an empty recordset. When deleting data you are changing the content of the collection and usually you start from the end and work forward. If you delete record[0] what is the current record you want to move next from?Never underestimate the power of human stupidity RAH
-
Try
select fname, lname from employee where 1=1that should get you back an empty recordset. When deleting data you are changing the content of the collection and usually you start from the end and work forward. If you delete record[0] what is the current record you want to move next from?Never underestimate the power of human stupidity RAH
i will not movenext after deletion, but i will add new record.
Mycroft Holmes wrote:
select fname, lname from employee where 1=1
is returning all records in the table.
0 will always beats the 1.
modified on Tuesday, February 8, 2011 5:54 AM
-
Try
select fname, lname from employee where 1=1that should get you back an empty recordset. When deleting data you are changing the content of the collection and usually you start from the end and work forward. If you delete record[0] what is the current record you want to move next from?Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
where 1=1
Since the condtion is true for all rows, won't all rows be returned in the set? :confused:
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Dear all, I have the following: set rs = db.openrecordset ("select fname, lname from employee") while not rs.EOF with rs .delete end with rs.movenext loop When the code is run, data is directly deleted from my actuall table??? i dont want this to happen, i want to initialize this recordset and refill it with another data. it is deleting the table records directly even without updating (.Update) !!! I need an empty recordset to work on. The only way (i know) to create a recordset is to create one from a given table through an SQL stat. I couldnt find a way to create an empty recordset from scratsh or a dummy recordset without a predefined data source. Help !!!!
0 will always beats the 1.
Can you explain further what your purpose is for requiring an "empty recordset"? Your question comes across somewhat odd. You have code that will delete records from the recordset and you express surprise that data is deleted and that you don't intend for that to happen. If you don't want data to be deleted, why do you have a delete statement on the recordset? :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Mycroft Holmes wrote:
where 1=1
Since the condtion is true for all rows, won't all rows be returned in the set? :confused:
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
My error it should be 1 = -1 or ID = -1
Never underestimate the power of human stupidity RAH
-
i will not movenext after deletion, but i will add new record.
Mycroft Holmes wrote:
select fname, lname from employee where 1=1
is returning all records in the table.
0 will always beats the 1.
modified on Tuesday, February 8, 2011 5:54 AM
My error try 1 = -1
Never underestimate the power of human stupidity RAH
-
Can you explain further what your purpose is for requiring an "empty recordset"? Your question comes across somewhat odd. You have code that will delete records from the recordset and you express surprise that data is deleted and that you don't intend for that to happen. If you don't want data to be deleted, why do you have a delete statement on the recordset? :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Sure.... The delete statement is just to empty the record set, Why is that? it is because it came from an sql statement or a certain data source, and since i need an empty one , so am deleting the records in it. My purpose is to pass a recordset to a report, but this recordset is a collection or records from different sources ,lets say two coloumns form an sql statement and another coloumn which i need to append and populate. which is in my case the salary coloumn . .append , didnt work . so my guess was to start from with a recordset from any sql source, and delete its rows (initializing) , then add my data. but deleting rows end up in deleting rows from my actual table the recordset came from :~ so i need to start with an empty recordset in order to fill it up and pass it to report.
0 will always beats the 1.
modified on Tuesday, February 8, 2011 10:56 PM
-
Sure.... The delete statement is just to empty the record set, Why is that? it is because it came from an sql statement or a certain data source, and since i need an empty one , so am deleting the records in it. My purpose is to pass a recordset to a report, but this recordset is a collection or records from different sources ,lets say two coloumns form an sql statement and another coloumn which i need to append and populate. which is in my case the salary coloumn . .append , didnt work . so my guess was to start from with a recordset from any sql source, and delete its rows (initializing) , then add my data. but deleting rows end up in deleting rows from my actual table the recordset came from :~ so i need to start with an empty recordset in order to fill it up and pass it to report.
0 will always beats the 1.
modified on Tuesday, February 8, 2011 10:56 PM
Assuming you're collecting data from several tables in ONE database. Use a union query[^] instead, to fill the record set with the required data.
-
Assuming you're collecting data from several tables in ONE database. Use a union query[^] instead, to fill the record set with the required data.
Thanks Andersson , but i think UNION is not the right solution here, since it envolve a lot of constraints :( , like number of coloumns of the two SQL stat. should be the same. Other wise i think it save alot of lines of code :)
0 will always beats the 1.
-
Thanks Andersson , but i think UNION is not the right solution here, since it envolve a lot of constraints :( , like number of coloumns of the two SQL stat. should be the same. Other wise i think it save alot of lines of code :)
0 will always beats the 1.
The queries that are unioned together needs to have the same number of columns of the same type with the same names in the same order, that's correct. But that could be easily fixed using alias and dummy columns.
select a,b,c
from tbl1
union
select x as a,y as b,null as c
from tbl2 -
The queries that are unioned together needs to have the same number of columns of the same type with the same names in the same order, that's correct. But that could be easily fixed using alias and dummy columns.
select a,b,c
from tbl1
union
select x as a,y as b,null as c
from tbl2Now this does make sense .... nice work around It worked fine (even the result data is not that much comprehensive) but it do the job, and save some extra lines of code ... great man, thanks alot :thumbsup:
0 will always beats the 1.