Unanswered: Help with an SQL SELECT statement in ASP page
I have built this SQL statement which should
create the RS I need.
strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"
objRS1.Open strSQL1, objConn
Causes this error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.
I have searched google, and found reference to the error meaning it found a
space in the first position. I tried adding LTRIM into my statement to cure
it but it made no difference, I may be barking up the wrong tree so to speak
but I cant find any other information on it.
If anyone has any ideas why this statement does not work I'd be very
grateful, the project has to be completed today, and this is the last thing
to get working now!
The length parameter in your LEFT(String, Length) function is
PatIndex('%821%', [INVENTRY MASTER].BOX_NO) - 1
PatIndex can return 0, if your string '821' isn't part of your BOX_NO, which makes the length = -1, which isn't allowed as a valid length. Your additional condition does not help, since the expression as a whole will be evaluated.
Consider to use a view to put your conditon
[INVENTRY MASTER].BOX_NO LIKE '%821%'
Make everything as simple as possible, but not simpler! - A. Einstein
DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool