String Query in vb

zrtv · March 17th, 2008, 08:51 AM

Hi All,
I have product table in my database in the sqlserver having the following fields.
pk_productID nvarchar(20)
uom nvarchar(20)
qty float

Data like the following:-

pk_productID uom qty
-------------------------------------
0101001 Nos 50
0101002 Nos 100
0101003 Nos 50
0101004 Nos 50

I want to retreive the product using the following criteria from vb6
FromProduct:<To productID>
ToProduct :<To productID>

what sqlquery i should use, because of these fields are in varchar datatype ..
Can anyone help me urgently please

BrianWren · March 17th, 2008, 12:37 PM

Code:

"SELECT *                              " & _
"FROM   <the product table>            " & _
"WHERE  pk_productID BETWEEN '0101001' " & _
"                        AND '0101003' "

urtrivedi · March 19th, 2008, 06:19 AM

If above doesn't works then try following.

SELECT * FROM <the product table>
WHERE pk_productID BETWEEN cast('0101001' as int)
AND cast('0101003' as int)

urt

Help yourself by helping someone.

BrianWren · March 19th, 2008, 09:42 AM

I had not encountered this before, so I did a little looking. I found in Teradata SQL: Unleash the Power by Michael Larkins and Tom Coffing (Coffing Data Warehousing Â© 2001):

Data Conversions
In order for data to be managed and used, it must have characteristics associated with it. These characteristics are called attributes that include a data type and a length. The values that a column can store are directly related to these two attributes.

There are times when the data type or length defined is not convenient for the use or output display needed. For instance, when character data is too long for display, an option might be to reduce its length. At other times, the defined numeric data type is not sufficient to store the result of a mathematical operation. Therefore, conversion to a larger numeric type may be the only way to successfully complete the request.

When one of these situations interrupt the execution of the SQL, it is necessary to use one or more of the conversion techniques....

In normal practices, there should be little need to convert from a number to a character on a regular basis. This requirement is one indicator that the table or column design is questionable. However, if a conversion must be performed, it is much safer to use the ANSI Standard CAST (Convert And Store) function when going from numeric to character instead of the older Teradata implied conversion....

[u]Conversions should be used only when absolutely necessary because they are intensive on system resources</u>. As an example, I saw an SQL statement that converted 4 columns 6 different times. There were around a million rows in the table. The SQL did a lot of processing and it took about an hour to run. By eliminating these 6 million conversions, the SQL ran in under 5 minutes. Conversions can have an impact, but sometimes you need them. [u]Use them only when absolutely necessary</u>! [Emphasis added]

This is not to say that using cast() is ruled out, only that if it can be avoided, it should be.