问题描述
大家好，我想实现的功能是当数据库字段state的内容为“是”的时候，把同行的字段room-no的内容赋值给一个变量。因为state为“是”的行数很多，想遍历数据库，从头到尾读，遇到state为“是”的就把同行的room_no内容输出。代码如下，但是只能把第一个为“是”的room_no的内容输出，数据库后面为“是”的都不行。不知道该怎么修改。PrivateSubForm2_Load(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesMyBase.LoadDimconnStr,selectName,selectCmdAsStringDimi,mAsIntegerFori=0To39Step1selectCmd="SELECTroom_noFROMinformationWHEREstate='是'"connStr="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=user.mdb"DimconnAsOleDbConnection,cmdAsOleDbCommand,myReaderAsOleDbDataReaderconn=NewOleDbConnection(connStr)conn.Open()cmd=NewOleDbCommand(selectCmd,conn)myReader=cmd.ExecuteReader()myReader.Read()roomnumber=myReader.Item("room_no")SelectCaseroomnumberCase"101"b101.BackColor=Color.Bluea101=TrueCase"102"b102.BackColor=Color.Bluea102=TrueCase"103"b103.BackColor=Color.Bluea103=TrueCase"104"b104.BackColor=Color.Bluea104=TrueCase"105"b105.BackColor=Color.Bluea105=TrueCase"106"b106.BackColor=Color.Bluea106=TrueCase"107"b107.BackColor=Color.Bluea107=TrueCase"108"b108.BackColor=Color.Bluea108=TrueCase"109"b109.BackColor=Color.Bluea109=TrueCase"110"b110.BackColor=Color.Bluea110=TrueCase"201"b201.BackColor=Color.Bluea201=TrueCase"202"b202.BackColor=Color.Bluea202=TrueCase"203"b203.BackColor=Color.Bluea203=TrueCase"204"b204.BackColor=Color.Bluea204=TrueCase"205"b205.BackColor=Color.Bluea205=TrueCase"206"b206.BackColor=Color.Bluea206=TrueCase"207"b207.BackColor=Color.Bluea207=TrueCase"208"b208.BackColor=Color.Bluea208=TrueCase"209"b209.BackColor=Color.Bluea209=TrueCase"210"b210.BackColor=Color.Bluea210=TrueCase"301"b301.BackColor=Color.Bluea301=TrueCase"302"b302.BackColor=Color.Bluea302=TrueCase"303"b303.BackColor=Color.Bluea303=TrueCase"304"b304.BackColor=Color.Bluea304=TrueCase"305"b305.BackColor=Color.Bluea305=TrueCase"306"b306.BackColor=Color.Bluea306=TrueCase"307"b307.BackColor=Color.Bluea307=TrueCase"308"b308.BackColor=Color.Bluea308=TrueCase"309"b309.BackColor=Color.Bluea309=TrueCase"310"b310.BackColor=Color.Bluea310=TrueCase"401"b401.BackColor=Color.Bluea401=TrueCase"402"b402.BackColor=Color.Bluea402=TrueCase"403"b403.BackColor=Color.Bluea403=TrueCase"404"b404.BackColor=Color.Bluea404=TrueCase"405"b405.BackColor=Color.Bluea405=TrueCase"406"b406.BackColor=Color.Bluea406=TrueCase"407"b407.BackColor=Color.Bluea407=TrueCase"408"b408.BackColor=Color.Bluea408=TrueCase"409"b409.BackColor=Color.Bluea409=TrueCase"410"b410.BackColor=Color.Bluea410=TrueEndSelectNextEndSub
解决方案
解决方案二：
datareader对象读取数据每次只能读取一个值，，需要循环读取才行。。whilemyReader.Read()‘你的代码endwhile
解决方案三：
你不用穷举所用的控件。。那样是出力不讨好的。。。用下面的这种方法：dimanumas你的type(a101的类型)dimbnumas你的type(b101的类型)dimxasinteger=roomnumberanum=Me.Controls.Find("a"+x.tostring,False)(0)bnum=Me.Controls.Find("b"+x.tostring,False)(0)bnum.backcolor=color.blueanum=true这样你就可以用上面的这几句代替你的繁琐的select语句。。。呵呵。。。直接定义变量然后遍历就行了。。。
解决方案四：
你不应该循环取数据的方法,而应该循环取到的数据.DimconnStr,selectName,selectCmdAsStringDimi,mAsIntegerselectCmd="SELECTroom_noFROMinformationWHEREstate='是'"connStr="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=user.mdb"DimconnAsOleDbConnection,cmdAsOleDbCommand,myReaderAsOleDbDataReaderconn=NewOleDbConnection(connStr)conn.Open()cmd=NewOleDbCommand(selectCmd,conn)myReader=cmd.ExecuteReader()roomnumber=myReader.Item("room_no")DoWhilemyReader.Read()SelectCaseroomnumberCase"101"b101.BackColor=Color.Bluea101=TrueCase"102"b102.BackColor=Color.Bluea102=TrueCase"103"b103.BackColor=Color.Bluea103=TrueCase"104"b104.BackColor=Color.Bluea104=TrueCase"105"b105.BackColor=Color.Bluea105=TrueCase"106"b106.BackColor=Color.Bluea106=TrueCase"107"b107.BackColor=Color.Bluea107=TrueCase"108"b108.BackColor=Color.Bluea108=TrueCase"109"b109.BackColor=Color.Bluea109=TrueCase"110"b110.BackColor=Color.Bluea110=TrueCase"201"b201.BackColor=Color.Bluea201=TrueCase"202"b202.BackColor=Color.Bluea202=TrueCase"203"b203.BackColor=Color.Bluea203=TrueCase"204"b204.BackColor=Color.Bluea204=TrueCase"205"b205.BackColor=Color.Bluea205=TrueCase"206"b206.BackColor=Color.Bluea206=TrueCase"207"b207.BackColor=Color.Bluea207=TrueCase"208"b208.BackColor=Color.Bluea208=TrueCase"209"b209.BackColor=Color.Bluea209=TrueCase"210"b210.BackColor=Color.Bluea210=TrueCase"301"b301.BackColor=Color.Bluea301=TrueCase"302"b302.BackColor=Color.Bluea302=TrueCase"303"b303.BackColor=Color.Bluea303=TrueCase"304"b304.BackColor=Color.Bluea304=TrueCase"305"b305.BackColor=Color.Bluea305=TrueCase"306"b306.BackColor=Color.Bluea306=TrueCase"307"b307.BackColor=Color.Bluea307=TrueCase"308"b308.BackColor=Color.Bluea308=TrueCase"309"b309.BackColor=Color.Bluea309=TrueCase"310"b310.BackColor=Color.Bluea310=TrueCase"401"b401.BackColor=Color.Bluea401=TrueCase"402"b402.BackColor=Color.Bluea402=TrueCase"403"b403.BackColor=Color.Bluea403=TrueCase"404"b404.BackColor=Color.Bluea404=TrueCase"405"b405.BackColor=Color.Bluea405=TrueCase"406"b406.BackColor=Color.Bluea406=TrueCase"407"b407.BackColor=Color.Bluea407=TrueCase"408"b408.BackColor=Color.Bluea408=TrueCase"409"b409.BackColor=Color.Bluea409=TrueCase"410"b410.BackColor=Color.Bluea410=TrueEndSelectLoopEndSub
解决方案五：
顶2楼，直接查找出state的内容为“是”的字段，再对查找出来的字段进行修改
解决方案六：
foreach,break
解决方案七：
myReader=cmd.ExecuteReader()whilemyReader.Read()roomnumber=myReader.Item("room_no")SelectCaseroomnumberCase"101"b101.BackColor=Color.Bluea101=Trueendwhile或通过数组或类属性实现赋值，添加数据到List<T>