MEMO FIELD

[Ranjini]

I am encountering problems when I am querying against a memo field.The memo field contains values seperated by a comma.Please do let me know how to query in ASP using ADO.

[Johnny101]

what are the problems you are encoutering? the data in the field shouldn't have any affect on the returning data or recordset structure, from ASP or VB.

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Ranjini]

I have stored the data in MS ACCESS with each word separated by comma..
eg :ABC,XYZ and have a text field in ASP page.When I enter ABC in the text box ..the ado should fetch a record which has ABC .. How should I do this ?

[Johnny101]

well, the asp text box thing should be fine - for your search for "ABC", are yuo using the Like operator? Try this when you go to search:

'the asteriks indicate wildcards for Access.
sql = "SELECT * FROM YourTable WHERE SearchField LIKE '*" & request.form("txtsearch") &"*'"




try that and see how it works then.

hope this helps,

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[Ranjini]

My memo field is a long string with commas in between each word.So even if I give a like clause in SQL statement ,it is not being recognized in the ADO

My code is as follows:-

'Open the Connection So as to retrieve the values present in the table Category.
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open Application("oConn_ConnectionString")
'objRS is the recordset object to retrieve the values.
Set objRS=Server.CreateObject("ADODB.Recordset")
'Keydesc is a memo field in the table client
sql="SELECT client.[clientID] FROM Client WHERE client.[keydesc] LIKE '*" & request.form("txtsearch") & "*'"
'Eg:sql="SELECT * FROM Client WHERE keydesc LIKE '*E*'"
Set objRS=objConn.Execute(sql)
if not objRS.BOF then
do while not objRS.EOF
Response.Write(objRS("clientID"))
objRs.MoveNext
loop
end if

[wenyen]

Hi, I am having problem with the Like operator. The SQL statement works well with normal select statement but Like statement:

This works fine:
sql = "SELECT * FROM HIDS WHERE EventDate = '01.02.1975'"

But, this doesn't work:
sql = "SELECT * FROM HIDS WHERE EventDate like '01.02.*'"

Why??

[Johnny101]

I didn't know that SQL Server would accept dates in that format - are they actually in a string field? if it's a "datetime" field, then i don't think yuo can do "LIKE" operations on dates - you'll have to split the date up and search for pieces you know instead. somethign like this maybe:

'since you know the day and month - search for those and leave the year blank
'but since the date can't be substringed in SQL Server, youll have to convert it to a varchar - like so
sql = "SELECT * FROM HIDS WHERE substring(convert(varchar, EventDate, 101),1,6) = '01/02/'"



the convert(varchar, EventDate, 101) function will always return a date in this format
MM/DD/YYYY which is why we can hard code the number of characters to read from the left (6) and compare it to.

hope this helps,

John


John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[wenyen]

Oh, no, they're actually in string field. Anyway, I've already solved my problem.

In fact, in SQLServer, the Like operator works fine with the % wildcard, but doesn't work with * wildcard. On the other hand, for older version of MS Access, it used to work with the * wildcard. But this time, when I am using MS Access 2000, the * doesn't work at all. I tried so many ways and end up got it work with the % wildcard, huh... I don't know why...

By the way, thanks for you info, coz it was another way that I was trying to adopt but couldn't get thru....

[PankajSonawane]

Hey!!
I too have a problem regarding Memo field.

There are some images stored in memo fields in Foxpro 2.5 (Old enough-8 years before) Now i have to retrive those images (as a part of my new project). Can u please help me through my problem.