[RESOLVED] MSFlex and recordsets

[Red_Dragon_X]

First off let me say thank you to si_the_geek for the very informative thread on inputing data from a recordset into an MSFlexGrid. But im having a slight issue here. I cant quite figure out how to define "objRS" so that i can pass that with the sub call.

I know that i have been trying to get these listviews to work, and now that i got them to work i switched to MSFlex LOL. When i figured out how to change row colors i was sold, screw that whole picturebox solution for a listview !!!

Anyhow, lets say i have a Datacontrol (Data1) on this same form, bound to the DB that i need to use (db1.mdb). what exactly do i have to do to define objRS to reference a table from DB1 ? when i tried to do it the same way that i built my listviews i get a mean ByRef error.

any help would be GREATLY appriciated !

[si_the_geek]

It may work like this:

Call FillFromRecordset_FlexGrid(MSFlexGrid1, Data1.Recordset, True)

Saying that tho, data controls are bad, mmmkay?
for some of the reasons, see the bit about "bound controls are bad" in the Database FAQ's.

The code was written using ADO code, whereas you are using DAO, so that might cause the problem (I intentionally didn't specify ADO in the sub, but I dont think I've tested it with DAO). There is an ADO data control too, which would be a slight improvement, but I would recommend going for ADO code instead - for an explanation/example, see the ADO Tutorial link in my signature.

[Red_Dragon_X]

thanks Si, i actually went through some posts about ado and got it to work before you posted that ... well kinda. i was about to post an issue when i say that you replied. I used your fill flex grid post to try and fill up the grid, but it didnt quite work correctly. before i made sure it worked i was messing around with it casue i really only need 4 of the 42 fields to display, but after i messed it all up i re pasted your code again to test it out. it fills the column names in the fixed row but no data. I was wondering if it had something to do with a lot of my fields not being populated. dont know why that would make a difference, but for some reason it is only posting the fixed row, and i opened the DB to make sure that there really are records in there !!

any ideas ?

[si_the_geek]

The fields not being populated wouldn't be a problem (the code deals with Null values), the only problems would be either no data in the recordset, or the records have all been read already (so the recordset is at EOF).

If you show us the code, we can help correct any problems in it.

[Red_Dragon_X]

this is the code that im using to pull the record. i think i have the strSQL statement written correctly

Set cn = New ADODB.Connection 
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= F:\db1.mdb" 
    cn.Open
strSQL = "SELECT * FROM Customers WHERE State = " & strAposAst & Text1.Text & strAstApos
    Set rs = New ADODB.Recordset 
    rs.Open strSQL, cn, adOpenKeyset
 
Call FillFromRecordset_FlexGrid(MSFlexGrid1, rs, True)

i run this same strSQL statement when i fill my listview and it finds the records so i figured it would be the same statement.

[Red_Dragon_X]

let me just clarify strAposAst and strAstApos are defined as:

Const strAstApos As String = "*'"
Const strAposAst As String = "'*"

ive said this before, but again; its just easier for me to see the variable names than to see "'*" and "*'"

[si_the_geek]

Ok, assuming that you are searching for records that include the text in Text1 (rather than being exactly equal to the text, with * on each side), there are two issues here..

The first is that to get a partial match (using wildcards), you need to use "Like" instead of "=".

The second is an annoying one, in DAO the wildcards are * and ? , but in ADO they are % and _

[Red_Dragon_X]

well ... for this search the field will be the exact name. so i dont need the wildcards at all ... but when i tried this:

strSQL = "SELECT * FROM Customers WHERE State = " & Text1.Text

and tried to run it i get an error that says:
"No value given for one or more required parameters" and it highlights this line:
rs.Open strSQL, cn, adOpenKeyset

im soo confused ! lol. just when i thought i had it all figured out with ListViews and DataControls i get this.

[si_the_geek]

If the field State is a Text field, you need the single quotes around the value, eg:

strSQL = "SELECT * FROM Customers WHERE State = '" & Text1.Text & "'"

[Red_Dragon_X]

OMG !!! that fixed it all !!! thank you soooo much. now all the data is being dropped in, now i just have to figure out how to change the code around abit to only give me the 3 or 4 fileds that i need!!

you're the man Si !!!!

[si_the_geek]

Thanks

If you only want to show a few fields, the easiest (and most efficient) way is to change the SQL a little, eg:

strSQL = "SELECT [u]Field1, Field2, Field3[/u] FROM Customers WHERE State = '" & Text1.Text & "'"

[Red_Dragon_X]

perfect ... worked like a charm!! Before i forget, does MSFlex have a fullrow select like the listview does ?

[si_the_geek]

You can set the "SelectionMode" property (as well as various others such as Highlight and FocusRect) to get the effect you want.