|
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=1 that 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
|
|
|
|
|
My error try 1 = -1
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? 
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Now 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 
0 will always beats the 1.
|
|
|
|
|
i just put the sqlstat in a seperate string and it worked fine 
hello all,
when i run this sql stat, in query design, Access 2007 , it works fine and return records.
SELECT fname, lname FROM names WHERE (location = 'there' AND id In (select id FROM name_street WHERE street = 11))
But when i put it in recordset as
set rcset = db.openrecorset("SELECT fname, lname FROM names WHERE (location = 'there' AND id In (select id FROM name_street WHERE street = 11))")
the record set brings back no records ... WHYYY ????
0 will always beats the 1.
modified on Tuesday, February 8, 2011 3:08 AM
|
|
|
|
|
Could be any number of reasons.
Such as
- You are not using the same database
- There are uncommitted records.
- You are not running the code that you think you are
- You are using some process to determine whether records and that process, not the number of records, is wrong.
|
|
|
|
|
I have a colleague, that has a bit of trouble creating a UDF heres there code but it fails:
CREATE FUNCTION GetData(@Param int)
RETURNS TABLE
AS
RETURN
IF @Param = 1
SELECT * FROM Emp
ELSE
SELECT * FROM Offices
END
The function shall return different table based oppon the condition of the @Param value.
Can someone help ???
With great code, comes great complexity, so keep it simple stupid... 
|
|
|
|
|
I haven't written a UDF or a while, and I don't like that design, but can you use a UNION?
SELECT * FROM Emp WHERE @Param=1
UNION ALL
SELECT * FROM Pffices WHERE @Param=0
|
|
|
|
|
|
No this isn't possible with either an inline or multi statement TVF (unless the 2 tables are union compatible?)
so the above reply is only workable solution, if tables are union compatible.
|
|
|
|
|
This is not a valid use for a function, a stored proc maybe but not a function. There is no way that emp and offices are going to be union compatible, if they are then why are they in different tables. You need to rethink your design.
Select * from anytable
This is frowned upon, explicitly declare the fields.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear ALl,
i have a record set lets say: set rs = db.openrecordset ("SELECT Name, Location FROM tb1")
I need to add a new field "salary" to rs, so i used : rs.fields.append "salary" , adinteger
salary is of integer datatype
This is not working, it is giving me Typemismatch Error and it highlights "salary", i also googled, all says the same method to append, but still not working .....
AM not sure whats the error, or why , Any one can guide how to append this coloumn to the recordset ??? any work around ???
Note: I want to fill the values of this "salary" field with numbers from calculations.
Regards,
0 will always beats the 1.
modified on Monday, February 7, 2011 5:48 AM
|
|
|
|
|
scorp_scorp wrote: any work around ???
How about adding a dummy-column in the select-statement? Something like below;
set rs = db.openrecordset ("SELECT Name, Location, 0 As [Salary] FROM tb1")You could then overwrite the values in the new column as required
I are Troll 
|
|
|
|
|
Greaaaat, thanks alot, this worked excellent but hence i got another problem:
when now i want to fill this feild (after creation), am doing:
With rs
.AddNew
.Fields("salary") = x
.Update
End With
where x is an integer from some where, i have the following error: Field cannot be updated .
i also used :
rs.Edit
rs("salary").Value = x
rs.Update
also same error
Any hint why this error showing up ??? is it the right way to fill in this field ?? i think it is 
0 will always beats the 1.
modified on Monday, February 7, 2011 11:36 PM
|
|
|
|
|
scorp_scorp wrote: Any hint why this error showing up ???
I don't know, would have to look it up. My guess is that the recordset-object is readonly.
What are you trying to achieve, and in what language? If you need to update some records in a database, you'd best execute the Sql against the database-server
I are Troll 
|
|
|
|
|
Thaks eddy, i found that recordsets are read only by default ... and also are not modifiable if sql depends on two tables of one-to-many relation or invlove sumation of rows ...
Also, if above conditions are not true, then a recordset to be modifiable, i think attribs should be set like:
set st = db.openrecordset("SQL",2,0,3)
or
set st = db.openrecordset("SQL",adOpenDynamic,adUseNone, adLockOptimistic)
0 will always beats the 1.
|
|
|
|
|
hi everyone,
i have sql server db with a table which contains a field for a workplace and the number of people employed in a specific month and year separated by gender.
it kinda looks like :
workplace month year male female
Microsoft feb 2011 5 1
Google feb 2011 6 2
Intel feb 2011 7 3
Microsoft march 2011 8 4
obviously with different actual data.
is there a way to concatenate the year and month columns and pivoting the data so it'll look something like :
workplace date male female date male female
Microsoft feb 2011 5 1 march 2011 8 4
Google feb 2011 6 2
Intel feb 2011 7 3
sorry if that was a bit too much, I'll be happy to explain myself more clearly if needed.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
