• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Data of recoderset

I try to do a sql selection

Set MyRs = New ADODB.Recordset
strSQL = "SELECT recid zoneid FROM recorder WHERE recname = '" + Meter_SN + "'"
MyRs.Open strSQL, cn

How can I get the value of recid zoneid?
May I use MyRs(0) and MyRs(1) to get it?

Thanks
0
turbot_yu
Asked:
turbot_yu
  • 2
  • 2
  • 2
  • +3
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
us MyRS("zoneid").value
0
 
ShauliCommented:
Set MyRs = New ADODB.Recordset
strSQL = "SELECT recid, zoneid FROM recorder WHERE recname = '" & Meter_SN & "'"
MyRs.Open strSQL, cn, adOpenKeyset, adLockReadOnly
    With MyRs
       Do Until .EOF
           MyRecIdCombo.AddItem !recid
           MyZonIdCombo.AddItem !zoneid
           .MoveNext
       Loop
    End With
MyRs.Close
Set MyRs = Nothing

S

0
 
JR2003Commented:
Dim sTmp1 As string
Dim sTmp2 As string
While not MyRs.EOF
    sTmp1 = MySet.Fields("recid").Value & ""
    sTmp2 = MySet.Fields("zoneid").Value & ""
    MySet.MoveNext
Wend
MySet.Close
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Off-Topic)  angelIII - Congrats on picking up two new certs in the last two weeks.  -Jim
0
 
turbot_yuAuthor Commented:
I am using vb6, and recid zoneid only have one value each.

Can they be just MyRs(0) and MyRs(1)?
0
 
aianrnoensCommented:
jr2003's code is great.   But I would use a do loop instead of a while wend loop
I use a picturebox to output the data picData is the name
dim oCN as new ADODB.Connection
dim oRS as new ADODB.Recordset
dim strSQL
oCN.open "{connection string}"
strSQL = "SELECT recid, zoneid FROM recorder WHERE recname = '" & Meter_SN & "'"
oRS.Open strSQL, oCN, 3, 3
oRS.MoveFirst
do while not oRS.EOF
  picData.Print "recid" & vbtab & oRS.Fields("recid")
  picData.Print "zoneid" & vbtab & oRS.Fields("zoneid")
  picData.print vbnewline
loop
oRS.Close
oCN.Close
set oRS = Nothing
set oCN = nothing
0
 
aianrnoensCommented:
And for one field use this

dim strRecID as string
dim strZoneID as string
oRS.MoveFirst
strRecID = oRS.Fields("recid")
strZneID = oRS.Fields("zoneid")
oRS.Close

Its just much more readable with the fields property because you can see the actual name of the field instead of just an index number
0
 
turbot_yuAuthor Commented:
I need MyRs(0) and MyRs(1), since next I need another sql is ready as below:

            cmdstr = "UPDATE instance SET V1SS_T='" + MyRs(0) + "',V2SS_T='" + MyRs(1) + "',V3SS_T='" + _
            MyRs(2) + "',I1SS_T='" + MyRs(3) + "',I2SS_T='" + MyRs(4) + "',I3SS_T='" + MyRs(5) + "',I4SS_T='" + _
            MyRs(6) + "',V1T_T='" + MyRs(7) + "',V2T_T='" + MyRs(8) + "',V3T_T='" + MyRs(9) + "',I1T_T='" + _
            MyRs(10) + "',I2T_T='" + MyRs(11) + "',I3T_T='" + MyRs(12) + "',I4T_T='" + MyRs(13) + "',ES_T='" + _
            MyRs(14) + "',TRENDING1='" + MyRs(15) + "',TRENDING2='" + MyRs(16) + "',TRENDING3='" + _
            MyRs(17) + "',TRENDING4=='" + MyRs(18) + "',TRENDING5='" + MyRs(19) + "',TRENDING6='" + _
            MyRs(20) + "',TRENDING7='" + MyRs(21) + "',TRENDING8='" + MyRs(22) + "',TRENDING9='" + _
            MyRs(23) + "',TRENDING10='" + MyRs(24) + "',TRENDING11='" + MyRs(25) + "',TRENDING12='" + _
            MyRs(26) + "',HARMONIC1='" + MyRs(27) + "',HARMONIC2='" + MyRs(28) + "',HARMONIC3='" + _
            MyRs(29) + "',HARMONIC4='" + MyRs(30) + "',HARMONIC5='" + MyRs(31) + "',HARMONIC6='" + _
            MyRs(32) + "',HARMONIC7='" + MyRs(33) + "',HARMONIC8='" + MyRs(34) + "',HARMONIC9='" + _
            MyRs(35) + "',HARMONIC10='" + MyRs(36) + "',HARMONIC11='" + MyRs(37) + "',HARMONIC12='" + _
            MyRs(38) + "',HARMONIC13='" + MyRs(39) + "',HARMONIC14='" + MyRs(40) + "',HARMONIC15='" + _
            MyRs(41) + "',HARMONIC16='" + MyRs(42) + "',HARMONIC17='" + MyRs(43) + "',HARMONIC18='" + _
            MyRs(44) + "',HARMONIC19='" + MyRs(45) + "',HARMONIC20='" + MyRs(46) + "',HARMONIC21='" + _
            MyRs(47) + "',HARMONIC22='" + MyRs(48) + "',HARMONIC23='" + MyRs(46) + "' WHERR instkey=" + CStr(recid)
0
 
JR2003Commented:
The full syntax to access a field would be

MyRs.Fields.Item(0).Value

You should always include the '.Value'
The .Item is optional/default so you can just have
MyRs.Fields(0).Value

Or even better put it all in a 'With' block:

With MyRs.Fields
      cmdstr = "UPDATE instance SET V1SS_T='" & .Item(0).Value & "',V2SS_T='" & .Item(1).Value & "',V3SS_T='" & _
            .Item(2).Value & "',I1SS_T='" & .Item(3).Value & "',I2SS_T='" & .Item(4).Value & "',I3SS_T='" & .Item(5).Value & "',I4SS_T='" & _
            .Item(6).Value & "',V1T_T='" & .Item(7).Value & "',V2T_T='" & .Item(8).Value & "',V3T_T='" & .Item(9).Value & "',I1T_T='" & _
            .Item(10).Value & "',I2T_T='" & .Item(11).Value & "',I3T_T='" & .Item(12).Value & "',I4T_T='" & .Item(13).Value & "',ES_T='" & _
            .Item(14).Value & "',TRENDING1='" & .Item(15).Value & "',TRENDING2='" & .Item(16).Value & "',TRENDING3='" & _
            .Item(17).Value & "',TRENDING4=='" & .Item(18).Value & "',TRENDING5='" & .Item(19).Value & "',TRENDING6='" & _
            .Item(20).Value & "',TRENDING7='" & .Item(21).Value & "',TRENDING8='" & .Item(22).Value & "',TRENDING9='" & _
            .Item(23).Value & "',TRENDING10='" & .Item(24).Value & "',TRENDING11='" & .Item(25).Value & "',TRENDING12='" & _
            .Item(26).Value & "',HARMONIC1='" & .Item(27).Value & "',HARMONIC2='" & .Item(28).Value & "',HARMONIC3='" & _
            .Item(29).Value & "',HARMONIC4='" & .Item(30).Value & "',HARMONIC5='" & .Item(31).Value & "',HARMONIC6='" & _
            .Item(32).Value & "',HARMONIC7='" & .Item(33).Value & "',HARMONIC8='" & .Item(34).Value & "',HARMONIC9='" & _
            .Item(35).Value & "',HARMONIC10='" & .Item(36).Value & "',HARMONIC11='" & .Item(37).Value & "',HARMONIC12='" & _
            .Item(38).Value & "',HARMONIC13='" & .Item(39).Value & "',HARMONIC14='" & .Item(40).Value & "',HARMONIC15='" & _
            .Item(41).Value & "',HARMONIC16='" & .Item(42).Value & "',HARMONIC17='" & .Item(43).Value & "',HARMONIC18='" & _
            .Item(44).Value & "',HARMONIC19='" & .Item(45).Value & "',HARMONIC20='" & .Item(46).Value & "',HARMONIC21='" & _
            .Item(47).Value & "',HARMONIC22='" & .Item(48).Value & "',HARMONIC23='" & .Item(46).Value & "' WHERE instkey=" & CStr(recid)

End With


Also in your sql string you should use '&' for concatenation rather than '+'
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now