Asp and Sql syntax 1

[redsand]

I am trying to read an sql table, store Efficiency field value based on the Cellnum value. The stored field will populate a java param field
SQL TABLE
CellNum Cell Efficiency


SqlOP = "SELECT Efficiency,Cellnum,Cell from Efficiency"
'------------OPEN UP DATABASE AND RUN QUERY
conOP.open "dsn=Dashboard","xx","xx"

'Static, Readonly, Command
rsOP.open sqlOP, conOP, 3, 1, 1

The table is very small 20 records and I need to select store the value in efficiency based on the cellnum.

If rs("Cellnum&quot = 66 Then
SmallMachop = rs("Efficiency&quot
else
If rs("Cellnum&quot = 36 Then
SmallAssop = rs("Efficiency&quot

Java param
<param name=&quot;g3value&quot; value=&quot;<%= SmallMachop%>&quot;>
<param name=&quot;g2value&quot; value=&quot;<%= SmallAssop%>&quot;>

Is this the correct syntax. Is this the even the correct way to do this? Any help would be greatly appreciated. Happy Holidays

 

[baddos]

Try this:

Select Case rs(&quot;Cellnum&quot
Case 66
SmallMachop = rs(&quot;Efficiency&quot
Case 36
SmallAssop = rs(&quot;Efficiency&quot
End Select

 

[redsand]

I am getting invalid charater Case 66 Here is what I Have

SqlOP = &quot;SELECT Efficiency,Cellnum,Cell from Efficiency&quot;

'------------OPEN UP DATABASE AND RUN QUERY

conOP.open &quot;dsn=Dashboard&quot;,&quot;x&quot;,&quot;x&quot;


'Static, Readonly, Command
rsOP.open sqlOP, conOP, 3, 1, 1

'-----------CHECK FOR NULL RECORD SET

dataOPAvail = True
if rsOP.EOF and rsOP.BOF then
dataOPAvail = False
end if

'----------Get data for ontime

if dataOPAvail Then
Select Case rs(&quot;Cellnum&quot
   Case 66
   SmallMachop = rs(&quot;Efficiency&quot
  Case 36
   SmallAssop = rs(&quot;Efficiency&quot
End Select
else
SmallMachop = 0

end if
'-----------CLOSE CONNECTION AND RECORDSET OBJECTS

rsOP.close
Set rsOP = nothing
conOP.close
Set conOP = nothing

 

[redsand]

Never Mind, I had a syntax error with the rs - rsOP.

Thanks for all of your help!!!!

 

[redsand]

I take that back, I am still getting an invalid character for case 66

 

[DeRoe]

You can try to explicitly convert the rs(&quot;Cellnum&quot to a Lng like this:

Select Case CLng(rs(&quot;Cellnum&quot)
Case 66
SmallMachop = rs(&quot;Efficiency&quot
Case 36
SmallAssop = rs(&quot;Efficiency&quot
End Select

or use strings:

Select Case CStr(rs(&quot;Cellnum&quot)
Case &quot;66&quot;
SmallMachop = rs(&quot;Efficiency&quot
Case &quot;36&quot;
SmallAssop = rs(&quot;Efficiency&quot
End Select

 

[redsand]

I tried both ways, I am still getting invalid character for Case 061. In Sql I have the field defined as nvarchar. I tried to use a different field(Cell) and changed the 061 to &quot;Small Cell&quot; to select data and received the same error.


Any ideas suggestions