Hi,

im new to asp, just trying to learn as i go-i have run into a little trouble with a select case statement. Basically the code takes the users session id which corresponds to his id in the members table then uses this info to get the username. then the code gets all the vegetables which that user has in the veg_root table. Basically i want to go through each veg and work out if its a root,brassica or other type of veg through a select statement, however nothing is output. It does output the veg name if comment out all the case statements etc- heres the code, can anyone help?!!

Dim strUsername, strSQL, strLogUser,strVeg,objRS,varRotation,VarVeg

strSQL = "SELECT username FROM members WHERE id= " & Cint(session("userid"))&";"
              set objRS=objConn.execute(strSQL)
              strUsername=lcase(objRS.Fields("username"))
              objRS.close

strSQL="SELECT*FROM veg_root WHERE username='" & strUsername & "';"

objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText objRS.MoveFirst


for i=1 to objRS.recordcount

Select Case objRS.Fields("veg_name")
        Case "Artichokes","Beetroot","Carrots","Parsnips","Pota to"
  Response.Write "*"
  Response.Write objRS.Fields("veg_name")
  Response.Write " "
  Response.Write "-crop rotation group: Root"
  Response.Write "<br>"
Case "Brocolli","BrusselSprouts","Cabbage","Cauliflower ","Kale","Swede"
    Response.Write "*"
    Response.Write objRS.Fields("veg_name")
    Response.Write " "
    Response.Write "-crop rotation group: Brassica"
    Response.Write "<br>"
Case "BroadBeans","Celery","Leek","Onions","Peas","Spin ich","Sweetcorn","Tomotoes"
    Response.Write "*"
    Response.Write objRS.Fields("veg_name")
    Response.Write " "
    Response.Write "-crop rotation group: Other"
    Response.Write "<br>"
end select

objRS.MoveNext
next
objRS.close
set objRS = Nothing

I did a little refactoring of your code to simplify things a bit. Perhaps these changes will fix the problem. One thing to do when you have to test strings is to force it to a case so you won't have to worry about the case of the source data.

Dim strUsername, strSQL, strLogUser,strVeg,objRS,varRotation,VarVeg
Dim sRotationGroup

strSQL="SELECT veg_name FROM veg_root vr JOIN members m ON vr.username=m.username WHERE m.id=" & Cint(session("userid")) & ";"

objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
If Not (objRS.BOF And objRS.EOF) Then
    While Not objRS.EOF
        Select Case LCase(objRS.Fields("veg_name"))
            Case "artichokes","beetroot","carrots","parsnips","pota to"
                sRotationGroup = "Root"
            Case "brocolli","brusselsprouts","cabbage","cauliflower ","kale","swede"
                    sRotationGroup = "Brassica"
            Case "broadbeans","celery","leek","onions","peas","spin ich","sweetcorn","tomotoes"
                sRotationGroup = "Other"
        End Select
        Response.Write "*"
        Response.Write objRS.Fields("veg_name")
        Response.Write " "
        Response.Write "-crop rotation group: "
        Response.Write sRotationGroup
        Response.Write "<br>"
        objRS.MoveNext
    End While
    objRS.close
End If
Set objRS = Nothing

Peter
-------------------------
Work smarter, not harder

Thanks for the reply, i will tryout your suggestions

Cheers

Ronny