resultset max. zeilen auslesen

[Sacki]

Hallo zusammen,

kann mir jemand von euch sagen, wie ich unter LS die Anzahl der Zeilen in meinem resultset auslesen kann?

 Unter VB geht das mit .recordcount, habe aber unter LS leider nichts gefunden. Meine Hilfe geht bei Resultset auch nicht.

[Axel]

Hi,

was meinst du den mit resultset? Ist das ein Ergebnis einer Suche?


Axel

[Sacki]

ich meinte eigentlich die classe odbcresultset.

Importiere Daten mittels SQL und möchte die Anzahl der gefundenen Datensätze auslesen.

[Semeaphoros]

num% = odbcResultSet.NumRows


Axel: Resultset ist ein SQL-Fachausdruck und bezeichnet die Tabelle, die durch ein Query als Resultat zurückgeliefert wird.

[Axel]

[quote author=Semeaphoros link=board=7;threadid=15093;start=0#89724
Axel: Resultset ist ein SQL-Fachausdruck und bezeichnet die Tabelle, die durch ein Query als Resultat zurückgeliefert wird.

Hi,

ich weiß was ein Resultset ist. Manchmal kommt es ja vor, dass jemand, der mit "richtigen" Datenbanken   gearbeitet hat auch schon mal die Begriffe auf Notes überträgt. Deshalb die Nachfrage, was mit Resultset gemeint war.


Axel

[Sacki]

Das resultset ist das Ergebnis meiner Abfrage.

numrows hatte ich vorher schon probiert, liefert mit aber nur nen boolischen Wert -1 (true).

Hast du vielleicht ne andere Idee?

[Sacki]

Vielleicht hilf es ja, wenn ich euch mal mein script schicke.
Mir geht es eigentlich nur um das Print, dass ich weiß, wo das Ende liegt.

'Use "LibLOG"
%INCLUDE "LSCONST.LSS"

Sub Initialize
   Dim Session As New NotesSession
   Dim db As NotesDatabase  
   Set db = Session.currentdatabase  
   
   Dim Con As New ODBCConnection  
   Dim Qry As New ODBCQuery  
   Dim res As New ODBCResultSet  
   
   Dim vk_nr As String
   Dim vk_nr1 As String
   Dim hinweis As String
   Dim k_nr As String
   Dim eindat As String
   Dim ausdat As String
   Dim abgabeterm As String
   Dim stueck As String
   Dim gld As String
   Dim meter As String
   Dim kd_nr As String
   Dim kd_name As String
   Dim plz As String
   Dim adr_(6) As String
   Dim tech_dat(4) As String
   Dim sach_nr As String
   Dim ang_zeich(6) As String
   Dim mat_kst As String
   Dim fert_kst As String
   Dim ge_(13) As String
   Dim bild_nr As String
   Dim name1 As String
   Dim i As Integer
   Dim jahr(13) As String
   
   jahr(0) = "91"
   jahr(1) = "92"
   jahr(2) = "93"
   jahr(3) ="94"
   jahr(4) = "95"
   jahr(5) = "96"
   jahr(6) = "97"
   jahr(7) = "98"
   jahr( = "99"
   jahr(9) = "00"
   jahr(10) = "01"
   jahr(11) = "02"
   jahr(12) = "03"
   jahr(13) = "04"
   
   
   ' alle Dokumente vor einem neuen Import löschen
   Dim collection As NotesDocumentCollection
   Set collection = db.AllDocuments
   If collection.Count <> 2 Then
      Call collection.RemoveAll ( True )
   End If
   
   
   For i = Lbound(jahr) To Ubound(jahr)
      
        'Verbindungsaufbau zur AS/400
      RetCode% = Con.ConnectTo("dbase", "","")
      
      Set Qry.Connection = Con    '  
      Qry.SQL = "SELECT * FROM kalkul"&jahr(i)&""
      Set Res.Query = Qry
      Res.Execute
      
      columns = res.NumColumns
      rows% = res.NumRows
      RetCode% = Res.FirstRow    
      Do While RetCode% =True
         
         vk_nr = Cstr(Res.GetValue("VK_NR") )
         vk_nr1 = Cstr(Res.GetValue("VK_NR1") )
         hinweis = Cstr(Res.GetValue("HINWEIS") )
         k_nr = Cstr(Res.GetValue("K_NR") )
         eindat = Cstr(Res.GetValue("EINDAT") )
         ausdat = Cstr(Res.GetValue("AUSDAT") )
         abgabeterm = Cstr(Res.GetValue("ABGABETERM") )
         stueck = Cstr(Res.GetValue("STUECK") )
         gld = Cstr(Res.GetValue("GLD") )
         meter = Cstr(Res.GetValue("METER") )
         kd_nr = Cstr(Res.GetValue("KD_NR") )
         kd_name = Cstr(Res.GetValue("KD_NAME") )
         plz = Cstr(Res.GetValue("PLZ") )
         adr_(1) = Cstr(Res.GetValue("ADR_1") )
         adr_(2) = Cstr(Res.GetValue("ADR_2") )
         adr_(3) = Cstr(Res.GetValue("ADR_3") )
         adr_(4) = Cstr(Res.GetValue("ADR_4") )
         adr_(5) = Cstr(Res.GetValue("ADR_5") )
         adr_(6) = Cstr(Res.GetValue("ADR_6") )
         tech_dat(1) = Cstr(Res.GetValue("TECH_DAT1") )
         tech_dat(2) = Cstr(Res.GetValue("TECH_DAT2") )
         tech_dat(3) = Cstr(Res.GetValue("TECH_DAT3") )
         tech_dat(4) = Cstr(Res.GetValue("TECH_DAT4") )
         sach_nr = Cstr(Res.GetValue("SACH_NR") )
         ang_zeich(1) = Cstr(Res.GetValue("ANG_ZEICH1") )
         ang_zeich(2) = Cstr(Res.GetValue("ANG_ZEICH2") )
         ang_zeich(3) = Cstr(Res.GetValue("ANG_ZEICH3") )
         ang_zeich(4) = Cstr(Res.GetValue("ANG_ZEICH4") )
         ang_zeich(5) = Cstr(Res.GetValue("ANG_ZEICH5") )
         ang_zeich(6) = Cstr(Res.GetValue("ANG_ZEICH6") )
         mat_kst = Cstr(Res.GetValue("MAT_KST") )
         fert_kst = Cstr(Res.GetValue("FERT_KST") )
         ge_(1) = Cstr(Res.GetValue("GE_1") )
         ge_(2) = Cstr(Res.GetValue("GE_2") )
         ge_(3) = Cstr(Res.GetValue("GE_3") )
         ge_(4) = Cstr(Res.GetValue("GE_4") )
         ge_(5) = Cstr(Res.GetValue("GE_5") )
         ge_(6) = Cstr(Res.GetValue("GE_6") )
         ge_(7) = Cstr(Res.GetValue("GE_7") )
         ge_( = Cstr(Res.GetValue("GE_8") )
         ge_(9) = Cstr(Res.GetValue("GE_9") )
         ge_(10) = Cstr(Res.GetValue("GE_10") )
         ge_(11) = Cstr(Res.GetValue("GE_11") )
         ge_(12) = Cstr(Res.GetValue("GE_12") )
         ge_(13) = Cstr(Res.GetValue("GE_13") )
         bild_nr = Cstr(Res.GetValue("BILD_NR") )
         name1 = Cstr(Res.GetValue("NAME") )
         
         
         Dim Doc As New NotesDocument(db)    
      'doc.view = "alle"
         doc.form = "kalkul"
         doc.vk_nr = vk_nr
         doc.vk_nr1 = vk_nr1
         doc.hinweis = hinweis
         doc.k_nr = k_nr
         doc.eindat = eindat
         doc.ausdat = ausdat
         doc.abgabeterm = abgabeterm
         doc.stueck = stueck
         doc.gld = gld
         doc.meter = meter
         doc.kd_nr = kd_nr
         doc.kd_name = kd_name
         doc.plz = plz
         doc.adr_1 = adr_(1)
         doc.adr_2 = adr_(2)
         doc.adr_3 = adr_(3)
         doc.adr_4 = adr_(4)
         doc.adr_5 = adr_(5)
         doc.adr_6 = adr_(6)
         doc.tech_dat1 = tech_dat(1)
         doc.tech_dat2 = tech_dat(2)
         doc.tech_dat3 = tech_dat(3)
         doc.tech_dat4 = tech_dat(4)
         doc.sach_nr = sach_nr
         doc.ang_zeich1 = ang_zeich(1)
         doc.ang_zeich2 = ang_zeich(2)
         doc.ang_zeich3 = ang_zeich(3)
         doc.ang_zeich4 = ang_zeich(4)
         doc.ang_zeich5 = ang_zeich(5)
         doc.ang_zeich6 = ang_zeich(6)
         doc.mat_kst = mat_kst
         doc.fert_kst = fert_kst
         doc.ge_1 = ge_(1)
         doc.ge_2 = ge_(2)
         doc.ge_3 = ge_(3)
         doc.ge_4 = ge_(4)
         doc.ge_5 = ge_(5)
         doc.ge_6 = ge_(6)
         doc.ge_7 = ge_(7)
         doc.ge_8 = ge_(
         doc.ge_9 = ge_(9)
         doc.ge_10 = ge_(10)
         doc.ge_11 = ge_(11)
         doc.ge_12 = ge_(12)
         doc.ge_13 = ge_(13)
         doc.bild_nr = bild_nr
         doc.name = name1
         
      'Call doc.ComposeWithForms (True, False)
         Call doc.save (True, True)
         Print "datensatz",res.currentrow  , "von", rows%, "im jahr", jahr(i) , " Ende 04"
         RetCode% = Res.NextRow
      Loop
      
      i = i +1
   Next
   RetCode% = Res.Close( DB_CLOSE )
   RetCode% = Con.Disconnect      
   
End Sub

[koehlerbv]

From the knowledge base:

The Notes Designer Help database states the following for the NumRows property:

"You can determine the number of rows only after the entire result set is fetched, not immediately after a query or while fetching records. "

How then can you determine the number of rows in the result set?

Solution:
Fetch all the rows using the LastRow method and then use the NumRows property to return the number of rows in the result set.

Example:

    Dim con As New ODBCConnection
    Dim qry As New ODBCQuery
    Dim res As New ODBCResultSet
    If Con.ConnectTo("salary")Then
         Set Qry.Connection = Con
         Qry.SQL = "select * from Salary"
         Set Res.Query = Qry
         Res.Execute  
         Res.LastRow
         Rows = Res.NumRows
         Messagebox Rows
         Res.Close(DB_Close)
         con.DisConnect
    End If

Note that if you have set MaxRows property for the result set, the LastRow method moves you to the last row of the result set as (potentially) limited by the number you have set for MaxRows. For example, you may have a Select statement that would return 100 rows but have a result set of only 50 rows because MaxRows has been set to 50. LastRow moves you to the last row of this result set, row 50.

In this case, however, NumRows will not return 50, but will instead return a negative number (-2), equal to the constant, DB_ROWSLIMITED. If Result.NumRows = DB_ROWSLIMITED, this indicates that there would be more rows if MaxRows was not limiting the result set, and that the number of rows in the result is equal to the number of rows specified by the MaxRows property.

Example:

    Dim con As New ODBCConnection
    Dim qry As New ODBCQuery
    Dim res As New ODBCResultSet
    If Con.ConnectTo("salary")Then
         Set Qry.Connection = Con
         Qry.SQL = "select * from Salary"
         Set Res.Query = Qry
         Res.MaxRows = 50
         Res.Execute  
         Res.LastRow
         Rows = Res.NumRows  
         If Rows = DB_ROWSLIMITED Then
              Messagebox "The number of rows in the result set is 50, the max that has been set with the MaxRows property."
         Else
              Messagebox "The number of rows in the result set is" & Str(rows)
         End If
         Res.Close(DB_Close)
         con.DisConnect
    End If

Supporting Information:

Another alternative to obtain the row count is to use SQL count() functionality to obtain the count. For example, pass the SQL query:

SELECT COUNT(*) FROM <yourtable>

This query returns the count. The following example shows how to display that value:

Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim res As New ODBCResultSet

Call Con.ConnectTo("count test")
Set Qry.Connection = Con
Qry.SQL = "select count(*) from dual"
Set Res.Query = Qry
Res.Execute
Call Res.FirstRow
Messagebox ("The count is " & Res.GetValue(1))
Res.Close(DB_Close)
con.DisConnect
 



HTH,
Bernhard

[Sacki]

Schönen Dank Bernhard !!! Es fuktioniert jetzt.

der Eintrag res.lastrow hat bei mir gefehlt.

Gruß

Sacki