Hi,<BR><BR>I have a problem with a code: I am converting an Access ASP application to SQL server database...and some of the functions which are for Access aren&#039;t available for SQL server.<BR><BR>This is the case of Instr...<BR><BR>This is my code:<BR>----------<BR>sql = "SELECT products.productid, products.Nr FROM products WHERE products.Featured=1 and not_shown=0 and (((Instr(1,&#039;" & str & "&#039;,(&#039;,&#039; & [productid] & &#039;,&#039;)))&#060;&#062;0)) group by products.productid, products.Nr"<BR>-----------<BR><BR>It says me that Instr isn&#039;t a recongnized function name (after trying to run this application for a SQL server database).<BR><BR>Bill (everyone knows Bill :)...and we don&#039;t want to kill him ;-)!) said that I have to use CHARINDEX function instead of Instr...I&#039;ve looked into Books Online of SQL server...<BR><BR>But...I am still confused...I replaced Instr with CHARINDEX and I get this error:<BR>---------<BR>Microsoft OLE DB Provider for SQL Server (0x80040E14)<BR>The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.<BR>---------<BR><BR>The "str" string - from (((Instr(1,&#039;" & str & "&#039;,(&#039;,&#039; & [productid] & &#039;,&#039;)))&#060;&#062;0)) - is taken from this code:<BR>----------<BR> if catcode="" then<BR><BR> strSQL = "SELECT productid from products where products.Featured=1 and not_shown=0 group by productid"<BR><BR> else<BR><BR> strSQL = "SELECT productid FROM subcat INNER JOIN (products INNER JOIN links ON products.productid = links.product_id) ON subcat.subcatcode = links.subcatcode_id where featured=1 and not_shown=0 and catcode="& catcode &" group by productid"<BR><BR> end if<BR><BR><BR> Set rsprod = Server.CreateObject("ADODB.Recordset")<BR> rsprod.Open strSQL,strConn,adLockOptimistic,adOpenKeySet<BR> <BR> if rsprod.eof then <BR> response.write ""<BR><BR> else<BR><BR>rsprod.MoveLast<BR> cnt = rsprod.RecordCount<BR> &#039;response.write cnt<BR> cnt1 = cnt<BR> rndMax = cnt<BR> <BR> maxnumber=4<BR> If CInt(maxnumber) &#060; cnt Then<BR> cnt1 = CInt(maxnumber)<BR> &#039;response.write cnt1<BR> End If<BR><BR> str = ","<BR> str1 = ","<BR> <BR> Do Until cnt1 = 0<BR> Randomize<BR> RndNumber = Int(Rnd * rndMax)<BR> &#039;response.write RndNumber<BR> If (InStr(1, str1, "," & RndNumber & "," ) = 0) Then<BR> &#039;If (InStr(str1, RndNumber) = 0) Then <BR> str1 = str1 & RndNumber & ","<BR> cnt1 = cnt1 - 1<BR> rsprod.MoveFirst<BR> rsprod.Move RndNumber<BR> str = str & rsprod("productid") & ","<BR> <BR> End If<BR><BR> Loop<BR><BR> rsprod.Close<BR> Set rsprod = Nothing<BR>------------<BR><BR>I know someone (I think again Bill :) ) told me this would have some impact on the performance...but I couldn&#039;t find another better way to extract 4 random records from "products" table.<BR><BR>So, how CHARINDEX can be adapted to work with my code shown first? Or if it;s a better way to extract 4 random records from a table without using CHARINDEX or Instr I am opened to hear it (examples would be helpful).<BR><BR>Thanks,<BR>Mircea