I attempting to pull all record from a column in a SQL table. I'm pulling in the first record but thats it. There are 152 record in the column that I need to pull in and put in a column in excel. Below is my code. Any ideas whta I'm doing wrong.
Please Login or Register to view this content.
Try this.
Please Login or Register to view this content.
If posting code please use code tags, see here.
That's my output. The line before that is my input.
There's nothing wrong with your input, the SQL statement is fine.
This isn't going to return all the records.
GetRows will return all the records in an array.Please Login or Register to view this content.
The alternative is to loop through the recordset.
Normaly my code would look something like SQLLaborOper = recS(0). which would referance item 1 and recS(1) would referance item 2. Anyway im only geting 1 item back from my statement I know that there are over 150. So I'm not sure whats wrong.
When you use recS(0) how many records/values does it return?
It'll be the value from the first field of the current record.
Yes GetRows works very well for putting the datat into the cells. You are one step ahead of me. My problem is getting all the data from the database. Im only getting one record back. I should be getting 150.
ADO's GetRows doesn't put data in cells, it returns records from a recordset.
http://msdn.microsoft.com/en-gb/libr...(v=vs.85).aspx
If you wanted the entire recordset in cells you would use CopyFromRecordset.
Last edited by Norie; 11-19-2012 at 04:21 PM.
I understand that. this statement is what is getting the data from the database. Correct ?Conn is the connection and recS is the record set. If you go into VBE and expand recS while debugging you can see all the data and it atributes I only see 1 record not the 150 that I need. so at this pointPlease Login or Register to view this content.isnt my problem since I do not have all the data from the database.Please Login or Register to view this content.
When you expand recS you will not see all the data, you'll see properties of the recordset.
One of these properties is the Fields collection which holds the data.
The data in the Fields collection when you first open the recordset will be for the first record.
Last edited by Norie; 11-19-2012 at 04:37 PM.
So your saying all the data is there and I'm just not seeing it?
I didn't say that.
To get all the data you can use various methods GetRows being one of them, but you can also loop through each record or use CopyFromRecordset to put the data on a worksheet.
What is it exactly you are trying to do?
Where are you using this function?
the function is being used on another sheet and will be refernaced in a dropdown. based on the selection from the dropdown some calculations are made.
Do you mean this is the used to populate the dropdown?
THat is correct.
Can you post the code that is using the function to populate the dropdown?
PS Have you tried GetRows yet?
No code its just an excel dropdown. I tried it when you first posted and all I got whs the first record. Sorry about this I'm not trying to be a pain. I just am one
How exactly did you check the no of records that were returned?
Also, how can you use this function on it's own to populate a drodown?
dropdowns utilise a rage of cells that are populated. As long as they are populated a dropdown wont care how. There was a record returned. The first one thats it there should have been 150 there was only 1.
How are you putting values in the cells?
Like I said a few posts ago you should use CopyFromRecordset.
GetRows put's the records in an array, you can't transfer that directly to cells without some work.Please Login or Register to view this content.
The dropdown is created by going to Data > Data Validation > and selecting List.
That did not work.
What did not work, how did it not work and what exactly did you try?
OK I figured out what the problem was itsthis looks at rows I need columns. Any one know what ADODB looks at columns?Please Login or Register to view this content.
That's not right.
A table in a database is made up of records and fields which can be equated to rows and columns.
With your query you are returning the value of one column (field) for all the rows (records) from the table.
OK I must be tired. I'm giving up for the day.
I found the code I needed to make it work thanks for all the helpPlease Login or Register to view this content.
Just like I suggested back at the start - loop through the recordset.
Anyway you've got it working and that's the important thing.
I just didnt know how to go about it. Thanks for the help.
Sorry, I could have sworn I posted a small example, but I checked and I can't see it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks