[RESOLVED] SQL query search to return table row

[helpjaypls]

Is it possible to do an sql search like the following:-

Select name from table where name = “James”

And then instead of returning that record returning the row in the table that that record is on?

If someone could let me know how to do this i would really appreciate it.

I want to do this because i have a flexigrid that shows the data from a table but none of the data in the table corresponds to the row it sits in. So i need to find where the searched data is in the table so i can then highlight that record on the flexigrid.

[FunkyDexter]

I'm not sure you can do that with SQL and I would strongly advise against it anyway. It makes the assumption that the data in the table is in the same order as it is in the flex greid and that it hasn't been filtered in any way. That's the sort of assumption that your likely to regret later.

Why don't you just loop through the rows in the flex grid checking each in turn. It's likely to be a bit slower depending on how many rows you're loading but if you're loading enough for it to take a very long time then you're probably loading too many.

[helpjaypls]

Thanks for your reply

It makes the assumption that the data in the table is in the same order as it is in the flex greid and that it hasn't been filtered in any way.

I can make that assumption because i made it that way and the reason i am looking into doing it this way is because i didn't want to filter it.

Although if you are right about SQL not being able to do that then i may have to just return the search results in the flexigrid instead.

Thanks for the loop through the flexigrid idea tho i will look into that.

[FunkyDexter]

I can make that assumption because i made it that way and the reason i am looking into doing it this way is because i didn't want to filter it.

Sorry to harp on but be careful here. There are all sorts of issues which might not be immediately obvious. For example, if your system is used by more than one user at the same time, one user may open the flex grid populating it from the table, then another user deletes a record - the positions of the records will no longer match.

If you're 100% sure you've thought of every possible issue then asking the database for the record position will almost certainly give you better performance (assuming you can find a SQL statement that will do it) but checking against the data in the grid is much safer because then you KNOW that the data is in the state you think it is.

[helpjaypls]

Its a good point FunkyDexter, although in the programs final use its very unlikely it could potentially happen and therefore i will scrap that idea and just filter the flexigrid to show the search results.

Thanks again

[FunkyDexter]

Sorry if I rained on your parade a bit.

I did have another thought though, how are you populating the datagrid in the first place? If you're doing it from a record set you could use the recordset's .find method followed by the .absolutePosition property. Make sure it's a dettached (client side) record set though because otherwise you'll still be vulnerable to the problems I outlined.

[helpjaypls]

Wouldn’t have asked a question if i didn't want a answer,

The FLEXIGRID is populated by a nice bit of code provided by si_the_geek in his signature, ado connection and SQL query.

So don't think that’s a viable option for me