Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 11th, 2004, 10:35 AM
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
  #2 (permalink)  
Old July 11th, 2004, 11:15 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old July 16th, 2004, 11:53 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old July 16th, 2004, 12:01 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old July 18th, 2004, 12:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
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
  #6 (permalink)  
Old July 18th, 2004, 03:55 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old July 18th, 2004, 11:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Code:
If len(trim(str))=0 then
    str = rsprod("productid")
Else
    str = str & "," & rsprod("productid")
End IF
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.
Code:
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
Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #8 (permalink)  
Old July 19th, 2004, 01:47 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Oracle Instr ermutigen BOOK: SQL Functions Programmer's Reference 1 May 31st, 2009 12:04 AM
parsing a string using charindex and substring cole SQL Server 2000 3 March 20th, 2007 02:09 AM
CharIndex to split last, first and Mid name khautinh SQL Server 2000 4 June 28th, 2006 08:27 AM
help on functions instr.... kyootepuffy Classic ASP Basics 2 September 19th, 2003 10:01 AM
instr function Beulah ASP.NET 1.0 and 1.1 Basics 1 September 18th, 2003 01:54 AM



All times are GMT -4. The time now is 11:26 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.