Problem displaying results

[pkuti]

Hi,
I'd been using MS Access as my database in ASP until recently when I switched to MySQL. It's been working well but I have problems with this particular querry.

The code below works ok(returns records) when I use the Access database,

Set cn = Server.CreateObject("ADODB.Connection&quot
Constr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & Server.MapPath("./Data/lecturers.mdb&quot & ";Persist Security Info=False"
cn.Connectionstring = Constr
cn.Open


If Request.Form(&quot;txtSearch&quot <> &quot;&quot; Then
Searchstr = Request.Form(&quot;txtSearch&quot
Else
Searchstr = Request.QueryString(&quot;Search&quot
End If

strSQL = &quot;Select ID, Surname, Other_names, Title, telephone, Email, &quot;
strSQL = strSQL & &quot;Department, Centre From Contacts Where Surname Like &quot;
strSQL = strSQL & &quot;'&quot; & Searchstr & &quot;%'&quot;

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot

rs.open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText



...But then using MySQL as shown below my querry returns nothing, with the record counter showing -1.

Set cn = createobject(&quot;ADODB.Connection&quot
cn.open = &quot;DRIVER={MySQL ODBC 3.51 Driver};&quot;_
& &quot;SERVER=localhost;&quot;_
& &quot;DATABASE=staff_list;&quot;_
& &quot;UID=root;PWD=; OPTION=35;&quot;


If Request.Form(&quot;txtSearch&quot <> &quot;&quot; Then
Searchstr = Request.Form(&quot;txtSearch&quot
Else
Searchstr = Request.QueryString(&quot;Search&quot
End If

strSQL = &quot;Select ID, Surname, Other_names, Title, telephone, Email, &quot;
strSQL = strSQL & &quot;Department, Centre From Contacts Where Surname Like &quot;
strSQL = strSQL & &quot;'&quot; & Searchstr & &quot;%'&quot;

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot

rs.open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText




Can someone pleases tell me what's happening?

 

[onpnt]

I'm guesing the like clause % may be different but I'll have to check on it.

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[onpnt]

hmm..
try
strSQL = &quot;Select ID, Surname, Other_names, Title, telephone, Email, &quot;
strSQL = strSQL & &quot;Department, Centre From Contacts Where Surname Like &quot;
strSQL = strSQL & &quot;'%&quot; & Searchstr & &quot;%'&quot;

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[onpnt]

looks like thats it
<src>
expr LIKE pat [ESCAPE 'escape-char']
Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). With LIKE you can use the following two wildcard characters in the pattern:
Char Description
% Matches any number of characters, even zero characters
_ Matches exactly one character

mysql> SELECT 'David!' LIKE 'David_';
-> 1
mysql> SELECT 'David!' LIKE '%D%v%';
-> 1
</src>

http://www.mysql.com/doc/en/String_comparison_functions.html

http://www.juicystudio.com/tutorial/mysql/select.html

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[pkuti]

Thanks a lot onpnt
Hmmm, I tried what you suggested and this is the error message that I got:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
/staff/deptSearch.asp, line 110

And this is what I have on line 110

rs.open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText


Any ideas

 

[onpnt]

is case correct in the names of the table and fields?


____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[onpnt]

also try taking out the adCmdText and changing types to
adLockPessimistic and adOpenDynamic eg:2,2

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[pkuti]

Yeah I had a field name wrong, I've corrected it but I still have the problem I desribed in my initial posting.

 

[onpnt]

did you check the link from the other reply.
it states
ADO
When you are coding with the ADO API and MyODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer will return for the RecordCount Property a result of -1. To have the right value, you need to set this property to adUseClient, like is showing in the VB code here:

which I jsut realized I stated wrong in my last post. apologies!

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[pkuti]

Thanks mate, let me check out this link and I will let you know asap if I get some luck. Spk soon.

 

[pkuti]

Hi Onpnt,
Can you please send me the exact link that talked about the ADO API and MyODBC....?

 

[onpnt]

http://www.mysql.com/doc/en/MyODBC_clients.html

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[pkuti]

Hi onpnt,
The link you suggested has been quite useful and I've managed to get the querry to return the results. I had to set the property of CursorLocation to AdUseClient.
Sorry I'm writing back late. But hey, you've been very helpful. Thanks