View Single Post
  #5 (permalink)  
Old July 18th, 2004, 12:55 AM
happygv happygv is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Code:
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 [productId] in (" & str & ") 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"
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
Code:
sql = "SELECT products.productid, products.Nr, products.Sonder, 
products.Price, products.Old_price, products.FilePath, 
products.FileName, products.featured, products.Author, 
products.Title, products.Subtitle FROM products WHERE 
products.Featured=1 and not_shown=0 and [productId] in (" & str & ") 
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"
But my suggestion is to immediately change those three nTEXT columns as nvarchar(needed_length)

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote