Recordset results
-
Dear All, Am running an sql query in Access db , 2007 , and it returns the expected reocrds. Now i copied the same query into a dataset , but it is generating diff numnber of records. Any body can guide me where should i look to solve this. Am writing the query in case it helps :omg: SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.c_scope, tb_c.c_number, tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE (((tb_c.c_scope)="Base Scope") AND ((tb_e_to_c.e_id)=1)); And Here how i wrote in the data set (its runing and returning records, which means the syntes is true, but the numer of records are different): sqls = "SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.[c_scope], tb_c.[c_number], tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE(((tb_c.[c_scope])= '" & scope & "' AND ((tb_e_to_c.e_id)= " & eid & ")))" set rcs = db.openrecordset (sqls)
0 will always beats the 1.
-
Dear All, Am running an sql query in Access db , 2007 , and it returns the expected reocrds. Now i copied the same query into a dataset , but it is generating diff numnber of records. Any body can guide me where should i look to solve this. Am writing the query in case it helps :omg: SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.c_scope, tb_c.c_number, tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE (((tb_c.c_scope)="Base Scope") AND ((tb_e_to_c.e_id)=1)); And Here how i wrote in the data set (its runing and returning records, which means the syntes is true, but the numer of records are different): sqls = "SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.[c_scope], tb_c.[c_number], tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE(((tb_c.[c_scope])= '" & scope & "' AND ((tb_e_to_c.e_id)= " & eid & ")))" set rcs = db.openrecordset (sqls)
0 will always beats the 1.
if you set a break point in your code and take a copy of the sqls variable's value and run that directly on the access database. How many records does it return? Is the outputted SQL Different to what you are expecting?
As barmey as a sack of badgers Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
-
if you set a break point in your code and take a copy of the sqls variable's value and run that directly on the access database. How many records does it return? Is the outputted SQL Different to what you are expecting?
As barmey as a sack of badgers Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
The variables scope and eid are in the code are the same as the one run in the access database. I did one step, that i added one more record to my tables that meets the conditions in the sql statement and run both on database and in the recordset, the sql returns true result (expected i.e: 3 records) and the recordset returns only 1 single record. seems that number of records in the record set is stuck to 1 single record. :confused: dont know why And there is nothing special about this record, i mean its not the first record in the tables. its really weird :doh:
0 will always beats the 1.
-
Dear All, Am running an sql query in Access db , 2007 , and it returns the expected reocrds. Now i copied the same query into a dataset , but it is generating diff numnber of records. Any body can guide me where should i look to solve this. Am writing the query in case it helps :omg: SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.c_scope, tb_c.c_number, tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE (((tb_c.c_scope)="Base Scope") AND ((tb_e_to_c.e_id)=1)); And Here how i wrote in the data set (its runing and returning records, which means the syntes is true, but the numer of records are different): sqls = "SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.[c_scope], tb_c.[c_number], tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE(((tb_c.[c_scope])= '" & scope & "' AND ((tb_e_to_c.e_id)= " & eid & ")))" set rcs = db.openrecordset (sqls)
0 will always beats the 1.
Hi When you say that the query returns only one record, have you looked at RecordCount -property? Also have you tried just using MoveNext method. I ask this since depending on the cursor type the RecordSet object may not know how many records there are. For example if using ForwardOnly-cursor the total amount of records may be unknown until you've iterated through all of them.
The need to optimize rises from a bad design.My articles[^]
-
Hi When you say that the query returns only one record, have you looked at RecordCount -property? Also have you tried just using MoveNext method. I ask this since depending on the cursor type the RecordSet object may not know how many records there are. For example if using ForwardOnly-cursor the total amount of records may be unknown until you've iterated through all of them.
The need to optimize rises from a bad design.My articles[^]
I was taught to use MoveLast then MoveFirst to let the program know about all the records.
-
I was taught to use MoveLast then MoveFirst to let the program know about all the records.
Yes, that's one way of finding out the total amount of records, but not possible always. Consider a situation where your cursor is a forward only version. You can move to the end but you cannot go back since the result set is lost for those records that you have already passed.
The need to optimize rises from a bad design.My articles[^]
-
Yes, that's one way of finding out the total amount of records, but not possible always. Consider a situation where your cursor is a forward only version. You can move to the end but you cannot go back since the result set is lost for those records that you have already passed.
The need to optimize rises from a bad design.My articles[^]
You are correct. It was a long time ago when I learned that and the memory is not so good. :)
-
You are correct. It was a long time ago when I learned that and the memory is not so good. :)
I hear you. Having the same problem and I'm staring to realize that the quality of my memory correlates negatively to the amount of gray hair, simply can't have both at the same time :)
The need to optimize rises from a bad design.My articles[^]
-
Hi When you say that the query returns only one record, have you looked at RecordCount -property? Also have you tried just using MoveNext method. I ask this since depending on the cursor type the RecordSet object may not know how many records there are. For example if using ForwardOnly-cursor the total amount of records may be unknown until you've iterated through all of them.
The need to optimize rises from a bad design.My articles[^]
Thanks again Mika I usually use movelast then move first after my recordset query ... seems i missed this time :sigh: , i used it and the recordcount brings true value (expected number of records). Thanks agian :)
0 will always beats the 1.
-
Thanks again Mika I usually use movelast then move first after my recordset query ... seems i missed this time :sigh: , i used it and the recordcount brings true value (expected number of records). Thanks agian :)
0 will always beats the 1.