How to search for multiple records...

[sun11]

Hi all,

I have a search field where I can search for one catalog using the catalog number. I enter for eg. 987654321 as catalog number and hit search it retreives me the record from the databas. But if I want to search more than one at a time.. how can I do it..

Thanks,
sun11

 

[Nifrabar]

Hi!
I would suggest to use SQL select.
-Bart

 

[sun11]

Hi Nifrabar,

I am using the SQL SELECT but I am able to retrive only one record but how to retrieve more than one record. I mean if I give five catalog numbers it should fetch the records from the database. any help would be appreciated.

Thanks,
sun11

 

[Nifrabar]

Sun11,

select * from mytable where searchfield=value1 or searchfield=value2 or searchfield=value3 into cursor crsResults

Would this help?
-Bart

 

[sun11]

Hi Nifrabar

Thanks for the reply...

I used like this "SELECT * FROM mytable WHERE MATCH(Catalog_no) AGAINST ('"+ALLTRIM(aIsbn13)+"')"...

So I was getting the record for one catalog number. what I was trying to do was in the same text box I would be entering the When I enter the catalog numbers like this in the text box and hit search it should give me the records.. I think you understand what I mean..

thanks,
sun11

 

[Olaf Doschke]

MATCH ... AGAINST is no VFP syntax.

First of all you need GUI to be able to enter several catalog numbers and then do an SQL like Nifrabar suggested to retrieve more than one record. Or you collect the catalog numbers into a cursor and JOIN that:

Create Cursor curSelection (CatalogNumber C(10))
Insert Into curSelection Values ('987654321')
Insert Into curSelection Values ('987654322')

Select * From myTable Inner Join curSelection ON myTable.CatalogNumber = curSelection.CatalogNumber

You may put this Insert-SQL into the textbox.valid event:
INSERT Into curSelection Values (This.Value)

But then you don't see what selection you made. Rather use a listbox and add to it all the catalog numbers you want at once before submitting and then creating such a join cursor.

And a totally different approach you can do is collect results from the SQL you use to retreive one record into a second table or alias/cursor and then append to it.

Bye, Olaf.

 

[sun11]

Hi Olaf,

If I want to search for 25 catalog numbers is it the same procedure...

i have a GUI in which I normally enter the catalog number. If I put all the catalog numbers in a text file suppose 30 catalog numbers. and if i give an option upload and search can i do in that way or is there any other easy way to do it because i need to search atleast 50 catalog numbers at a time.

thanks,
sun11

 

[JRB-Bldr]

"I am using the SQL SELECT but I am able to retrive only one record"

If that is the case, then the problem is in your SQL Query.
If it is correctly 'built' is should return ALL records which match your criteria.

Keep working on fixing your SQL Query command.

And unless MATCH() and AGAINST() are some UDF's in your VFP code, you should stick to using VFP command syntax.

Good Luck,
JRB-Bldr

 

[Nifrabar]

jrbbldr,
as far as i know match()... against()... is mySQL syntax.
-Bart

 

[JRB-Bldr]

If this is SQL Query syntax for some 'alien' (i.e. non-VFP) backend, then, in the event of it not working as expected, that syntax needs to be checked for accuracy directly in the backend itself without VFP being involved at all.

Use some mySQL GUI interface which will allow you to perform a SQL Queries directly into the mySQl data tables.

Once you get that syntax correct THEN you can utilize it in a SQL Passthru in VFP

nConnectionHandle = <whatever>

cPassThruCmd = <WORKING Backend SQL Query syntax>
nRet = SQLEXEC(nConnectionHandle,cPassThruCmd,'Result')

IF nRet = 1
   SELECT Result
   <do whatever>
ENDIF
=SQLDISCONNECT(nConnectionHandle)

Good Luck,
JRB-Bldr