|
Hi
I'm getting error while retrieving only a dataset <100k rows (~150MB estimate) - ran into this error: "Description: Not enough storage is available to complete this operation."
After googling seems like it's related to memory (As supposed to disk space or SQL server)
This is how I instantiate connection and command:
ADOconn.conn = actxserver('ADODB.Connection');
ADOconn.conn.ConnectionString = connStr;
ADOconn.conn.CursorLocation = 2;
ADOconn.conn.ConnectionTimeout = xxx;
ADOconn.conn.CommandTimeout = xxx;
ADOconn.conn.Open;
ADOconn.command = actxserver('ADODB.Command');
ADOconn.command.CommandTimeout = 0;
ADOconn.command.CommandType = 4;
ADOconn.command.ActiveConnection = ADOconn.conn;
Any idea if there's a property on connection or command whereby max memory limit can be set? (And is there a default?)
Thanks
ADO Connection object http://msdn.microsoft.com/en-us/library/ms807027.aspx
ADO Command object http://msdn.microsoft.com/en-us/library/ms675022(v=vs.85).aspx
Microsoft SQL Server connection string http://www.connectionstrings.com/sql-server-2005
dev
|
|
|
|
|
A dataset requires approximately ten times the space of the actual data (give or take a lot).
So if you're running a 32 bit application I would not be surprised if you hit the 2 GB program limit.
Can you use a datareader instead and page the data?
Or store the data more efficiently in a collection instead of a dataset?
|
|
|
|
|
php code to read from the database
|
|
|
|
|
what have you tried so far? which book have you studied? how many CodeProject articles have you read already?
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Your query returns lots of results: http://www.google.com/search?q=php+code+to+read+from+the+database[^]
In order to get good answers you should be able to show that you've already tried to solve things out or built something but perhaps encountered a specific problem which you would like solve with the help from people at discussion forums. One good rule of thumb is that typically the answers are as good as the question.
I'm really confident that if you pinpoint an exact problem you'll receive quality help from the forums.
|
|
|
|
|
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 
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.
|
|
|
|
|
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.  dont know why
And there is nothing special about this record, i mean its not the first record in the tables. its really weird 
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Thanks again Mika
I usually use movelast then move first after my recordset query ... seems i missed this time  , i used it and the recordcount brings true value (expected number of records).
Thanks agian
0 will always beats the 1.
|
|
|
|
|
No problem
|
|
|
|
|
Dear All, Am getting sytex error , Missing operator on the following:
sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In select c_number FROM tb_c_details WHERE e_id = " & eid
set rst = db.openrecordset (sqls)
Cant find any missing operator  , also i used a msgbox to show sqls , the statement is perfect.
Helpppp !!! 
0 will always beats the 1.
|
|
|
|
|
scorp_scorp wrote: Helpppp !!!
All examples I've seen have parentheses following the IN keyword...
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Modified to:
sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In ( select c_number FROM tb_c_details WHERE e_id = " & eid & " )"
Didnt work ... same problem 
Any different statment that would give me the same results ??? since i think the error is from the IN phrase .
0 will always beats the 1.
modified on Sunday, February 13, 2011 2:51 AM
|
|
|
|
|
If you alreasy corrected the in clause, are the scope and eid variables containing character values? If theay are they should be between ''.
You could also post the contents of the sqls variable in order to see what's the actual statement that's executed against the database.
|
|
|
|
|
scope is a string and eid is a int.
dim scope as string
dim eid as integer
scope = CStr(Forms![report_testin]![Combo42].Value)
eid =Int(Forms![report_testin]![Combo29].Value)
ami defining the string sqls wrong???
I also tried to modify sqls to be:
sqls = "SELECT distinct c_number, c_description FROM tb_c INNER JOIN tb_c_details ON tb_c.c_number = tb_c_details.c_number WHERE e_id = " & eid
It generates another Error:
"The specifies Field 'c_number' could refer to more than one table listed in the FROM clause of your sql statement"
but it doesnt make sense for me 
0 will always beats the 1.
modified on Sunday, February 13, 2011 4:08 AM
|
|
|
|
|
If scope is a string your statement should be something like
sqls = "SELECT c_number, c_description, c_scope, c_category, c_type " & _
FROM tb_c " & _
WHERE c_scope = '" & scope & "' " & _
AND c_number IN ( SELECT c_number " & _
FROM tb_c_details " & _
WHERE e_id = " & eid & " )"
Also instead of using literals (concatenated values) in your statement you should use bind variables. This way you're more safe from SQL injections, datatype mismatches, syntax errors etc
[addition]
Concenring the latter error, you should use aliases in the select portion. Like:
SELECT distinct tb_c.c_number, tb_c.c_description ...
|
|
|
|
|
|
No problem
|
|
|
|
|
How to make auto number start from 2000 not in 1. I use MS Access 2003 database
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
