View Single Post
  #1 (permalink)  
Old July 11th, 2004, 10:35 AM
mircea mircea is offline
Registered User
Join Date: Jul 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default CHARINDEX instead of Instr...


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't available for SQL server.

This is the case of Instr...

This is my code:
sql = "SELECT products.productid, products.Nr FROM products WHERE products.Featured=1 and not_shown=0 and (((Instr(1,'" & str & "',(',' & [productid] & ',')))<>0)) group by products.productid, products.Nr"

It says me that Instr isn't a recongnized function name (after trying to run this application for a SQL server database).

Somebody said that I have to use CHARINDEX function instead of Instr...I've looked into Books Online of SQL server...

But...I am still confused...I replaced Instr with CHARINDEX and I get this error:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The "str" string - from (((Instr(1,'" & str & "',(',' & [productid] & ',')))<>0)) - is taken from this code:
if catcode="" then

strSQL = "SELECT productid from products where products.Featured=1 and not_shown=0 group by productid"


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"

end if

    Set rsprod = Server.CreateObject("ADODB.Recordset")
    rsprod.Open strSQL,strConn,adLockOptimistic,adOpenKeySet

    if rsprod.eof then
    response.write ""


    cnt = rsprod.RecordCount
    'response.write cnt
    cnt1 = cnt
    rndMax = cnt

    If CInt(maxnumber) < cnt Then
    cnt1 = CInt(maxnumber)
    'response.write cnt1
    End If

    str = ","
    str1 = ","

    Do Until cnt1 = 0
        RndNumber = Int(Rnd * rndMax)
  'response.write RndNumber
        If (InStr(1, str1, "," & RndNumber & "," ) = 0) Then
    'If (InStr(str1, RndNumber) = 0) Then
        str1 = str1 & RndNumber & ","
        cnt1 = cnt1 - 1
        rsprod.Move RndNumber
            str = str & rsprod("productid") & ","

    End If


    Set rsprod = Nothing

I know someone told me this would have some impact on the performance...but I couldn't find another better way to extract 4 random records from "products" table.

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).

I've read on about this examples of extracting 1 random record (for SQL server):
USE Northwind

   TOP 1 CategoryID,
   CategoryName FROM

But how can I use this for extracting 4 random records??

Reply With Quote