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

What is the datatype of the COLUMN with which you are using CHARINDEX?

I think you are using TEXT datatype on a column that is used in your WHERE clause or in ORDER by Clause. It is better you post the structure and datatype of the entire table and also the SELECT statement that you are using, which results in ERROR.

Cheers!

_________________________
- Vijay G
Strive for Perfection

The column "productid" is "int"...

So this "str" would be a collection of integers:
----
str = str & rsprod("productid") & ","
----

I converted "str" to a string using "cstr" function...but I keep getting this error:
-------
Microsoft OLE DB Provider for SQL Server (0x80040E07)
The data type int is invalid for the charindex function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
-------

How can I make it work?

Thanks,
Mircea

The full SQL where the error appears is:
---------
sql = "SELECT products.productid, products.Nr, products.Author, products.Title, products.Subtitle, products.Sonder, products.Price, products.Old_price, products.FilePath, products.FileName, products.featured FROM products WHERE products.Featured=1 and not_shown=0 and (((charindex(1,'" & str & "',(',' & [productid] & ',')))<>0)) group by products.productid, products.Nr, products.Author, products.Title, products.Subtitle, products.Sonder, products.Price, products.Old_price, products.FilePath, products.FileName, products.featured"
----------

As I said, the column "productid" is "int"...and the rest:
- Nr: nvarchar
- Author, Title, Subtitle: ntext
- ISBN, Sonder: nvarchar
- other fields are: but, money, tinyint

Hi
On this error:
The data type int is invalid for the charindex function. Allowed types are: char/varchar,nchar/nvarchar, and binary/varbinary.


You are doing a CSTR(STR) in ASP, not in your backend. So this doesn't in anyways get converted to string type in your SQL server.

Secondly, You are wrong with the CHARINDEX() syntax. I believe you have somehow managed to find the Access's INSTR() equivalent in SQL as Charindex, but have not changed the Parameters of this function in your Query.
Syntax : CHARINDEX(expression1, expression2 [, start_location])

As far as I have understood, I don't think you need a CharIndex() there. If I am not wrong, I think you are trying to compare if string "str" has the [PRODUCTID] within it.
So I would suggest you to use it as
This way you don't have to convert str and PRODUCTID into a CHAR/VARCHAR type and a CHARINDEX() would slow your query. But the in operator is much faster.

Also you should be very careful in choosing datatyes. SQL nTEXT/TEXT datatype is not something similar to the ACCESS TEXT datatype. This is only used when you want to store really huge data. Text datatype can store up to 2,147,483,647 characters. Something like content of an article can be of nTEXT/TEXT. So I won't prefer nTEXT/TEXT datatype for Authors, Title and SubTitle as in your case. Are these three columns going to have something more than 6000/7000 characters? IMO an Authors could be a maximum of 255 chars long, Title - 255 chars long and Subtitle - 255 chars long. nTEXT/TEXT datatypes used in queries always has performance hit on the database server. So, unless there is a real necessity to go for nTEXT/TEXT type, one shouldn't be choosing that.

Choosing SQL Server 2000 Data Types

Also, when you use nTEXT/TEXT type Columns in your SQL Query and try to construct recordset using such query, I would always suggest you to use them at last in your select statement. Else you won't be able to find them out in your recordset. I don't see any documented data supporting this, but have been doing this since long from my own experience. May be if someone can point some link that supports this it would be appreciated.

So in that way, I would write your SQL statement as
But my suggestion is to immediately change those three nTEXT columns as nvarchar(needed_length)

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

I used your first example and I get this error:
--------
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting the varchar value ',6100,5850,921,6497,' to a column of data type int.
--------


The output of the SQL looks like this:
-------
SELECT products.productid, products.Nr, products.Author, products.Title, products.Subtitle, products.Sonder, products.Price, products.Old_price, products.FilePath, products.FileName, products.featured FROM products WHERE products.Featured=1 and not_shown=0 and [productId] in (',6150,5867,1336,6575,') group by products.productid, products.Nr, products.Author, products.Title, products.Subtitle, products.Sonder, products.Price, products.Old_price, products.FilePath, products.FileName, products.featured
---------

As you see the "str" looks like (',6150,5867,1336,6575,')...this because of the string construction (the result is a string not a number).

So, first it seems I have to remove the first "," and the last...and after that converting to integer...

How do I do that?

Thanks,
Mircea

Mircea,

There seems to be some problem with the way you concatenate productid into str
str = str & rsprod("productid") & ","
You shouldn't have , at the beginning and end of that(6150,5867,1336,6575). Also don't wrap them around with single QUOTES. You got to code it in such a way that when the str is empty, just store the product ID there. If not empty then concat that with comma followed by product ID
This way you would get that as 6150,5867,1336,6575

Try this query directly on your DB and see if that produces the required result and then proceed further.
Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thank you, Vijay :)!

It works now...

I am going on with updating of my application to SQL Server from Access...I hope not to encounter any other problem...