SQl Query Problem

[mangojack]

I'm in the process of converting text records to an SQL db but have hit a snag.

The db appears to be populated correctly and various queries and non queries work as expected. Just prior to this code snippet the db was queried and all data from 1 table was displayed in a CustomListView as expected.... Cursor1 was Closed.

A List LongClick fires the following ..

Sub btnDeleteItem_Click
   
   Dim rowID As Int
   Dim recData(2) As String
    
    rowID = 5
      
   'Cursor1 = SQL1.ExecQuery("SELECT Col2, Col3 FROM Records WHERE P_id =  '" & rowID & "' ")
     Cursor1 = SQL1.ExecQuery("SELECT Col2, Col3 FROM Records WHERE P_id = 5 ")
      
    'Cursor1 = SQL1.ExecQuery("SELECT Col2, Col3 FROM Records WHERE Col2 = 'Baked Beans' ")
   
   'Cursor1.Position = 5
      
     recData(0) = Cursor1.GetString("Col2")  'ERROR OCCURS HERE  *****
     recData(1) = Cursor1.GetString("Col3")

The error ,Cursor Index out of bounds : Index 1 Requested with a size of 1.

The table has 13 fields (TEXT) Col1 being P_id of PRIMARY INTEGER KEY
All 3 queries cause the error. for the exercise P_id 5 Col2 is Baked Beans.

What am I missing ...

Cheers mj

 

[mc73]

Set cursor.position=0 before using getstring.

 

[Mahares]

Here is the code:

Dim rowID As Int  = 5      
Dim recData(2) As String    
Dim MyRecCount As Int
Cursor1 = SQL1.ExecQuery("SELECT Col2, Col3 FROM Records WHERE P_id = 5 ")  
MyRecCount =Cursor1.RowCount
If MyRecCount=0 Then
   Msgbox("No records found","")
Else
   Cursor1.Position=0
   recData(0) = Cursor1.GetString("Col2")  
   recData(1) = Cursor1.GetString("Col3")
   Msgbox(recData(0) & "  " & recData(1) ,"")
End If

 

[mangojack]

Thanks mc73 and Mahares ..

So can i clarify when you do a Query that should / will result in a single record return .ie WHERE P_id = xxx .. that record is set to / becomes the the first / only record in the db View ? There would be 1 record found which is at position 0

for a wider scoping Query , MyRecCount is number of records found by your Query and you would loop through them by way of

ie: 0 TO MyRecCount -1

Thanks guys ..

Cheers mj