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

Hi,

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"

else

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 ""

  else

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

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

    str = ","
    str1 = ","

    Do Until cnt1 = 0
        Randomize
        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.MoveFirst
        rsprod.Move RndNumber
            str = str & rsprod("productid") & ","

    End If

    Loop

    rsprod.Close
    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 www.adopenstatic.com about this examples of extracting 1 random record (for SQL server):
--------
USE Northwind

SELECT
   TOP 1 CategoryID,
   CategoryName FROM
   Categories
ORDER BY
   NewID()
--------

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


Thanks,
Mircea
Reply With Quote