CHARINDEX instead of Instr...again

Results 1 to 2 of 2

Thread: CHARINDEX instead of Instr...again

  1. #1
    Join Date
    Dec 1969

    Default CHARINDEX instead of Instr...again

    Hi, <BR><BR>Sorry for re-posting but I didn&#039;t get any answer to this (it was Sunday when I posted it)...<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

  2. #2
    Join Date
    Dec 1969

    Default RE: CHARINDEX instead of Instr...again

    Yeah you&#039;re going to have some problems with that!<BR><BR>Can I ask why you have a text datatype I would think that a varchar would be more appropiate!<BR><BR>(If you change the type to varchar then the code with work!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts