cursor.getstring returns cursor index out of bounds

raphipps2002

raphipps2002

Active Member
Licensed User
Longtime User
I have a sql db called lover. I am trying to find a record in my table. There can be many same names with many phone numbers, but no duplicate phone numbers.

So I want to find in my table called "Profiles" a row where Number = x

sqlLover.Initialize(File.DirRootExternal, "/Lover/Lover.db", True)

Dim Cursor1 As Cursor

Cursor1 = sqlLover.ExecQuery2("SELECT * FROM Profiles WHERE Mobile = ?", Array As String(Number))


So, when I try this -


Cursor1.GetString2("Name")

I get java error saying index -1 out of bounds.

When I find the record i need to take the rows data and do something with it



Cursor1.Close


Thanks
 
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
You need the line I added and also getstring not getstring2
B4X: 
sqlLover.Initialize(File.DirRootExternal, "/Lover/Lover.db", True)
Dim Cursor1 As Cursor
Dim MyName as string
 Cursor1 = sqlLover.ExecQuery2("SELECT * FROM Profiles WHERE Mobile = ?", Array As String(Number))
Cursor1.Position=0   'Line added
 MyName =Cursor1.GetString("Name")  'returns the name
 
Upvote 0
mc73

mc73

Well-Known Member
Licensed User
Longtime User
First you should check if records meeting your criteria are found: if cursor.rowcount>0.
Then in order to get the very first result you should set cursor.position=0.
 
Upvote 0
raphipps2002

raphipps2002

Active Member
Licensed User
Longtime User
Yes that worked thank you. So I can now add and read a record...now I need the correct syntax for UPDATING. I have read the tutorial but the examples dont give the option to used a variable to set a value. I have tried several permutations can you help?

I tried this

SQLLover.ExecNonQuery2("Update Profiles SET VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As Object(Nos, Nme, SundayX, MondayX, TuesdayX, WednesdayX, ThursdayX, FridayX, SaturdayX, lblTimeFrom.text, lblTimeTo.Text))

Can you help with the syntax please
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Did you try something like this, by removing the word VALUES:
B4X: 
SQLLover.ExecNonQuery2("Update Profiles SET (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As Object(Nos, Nme, SundayX, MondayX, TuesdayX, WednesdayX, ThursdayX, FridayX, SaturdayX, lblTimeFrom.text, lblTimeTo.Text))

If that does not work for you, please state the error message or try this. It will work:
REPLACE: SET (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
WITH: SET Field1=?,Field2=?,Field3=? etc.
 
Last edited:
Upvote 0
raphipps2002

raphipps2002

Active Member
Licensed User
Longtime User
still not work :(

Thank's but it still does not work....I get error

SQLlte.exception: near "(" syntax error: , while compiling: Update Profiles SET(?,?,?, etc )

So any ideas on the syntax

thanks
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
This should work for you: Make sure you replace field1 thru 11 with the names of your fields:
B4X: 
SQLLover.ExecNonQuery2("Update Profiles SET Field1=?, Field2=?, Field3=?, Field4= ?," _
& "Field5=? , Field6=?, Field7=?, Field8=?, Field9= ?, Field10= ?, Field11=? ",  _
Array As Object(Nos, Nme, SundayX, MondayX, TuesdayX, WednesdayX, ThursdayX, FridayX, SaturdayX, lblTimeFrom.text, lblTimeTo.Text))
 
Upvote 0
raphipps2002

raphipps2002

Active Member
Licensed User
Longtime User
Thanks Maharas but still no joy :(

Thanks but

I tried this

SQLLover.ExecNonQuery2("REPLACE: SET (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", WITH: SET (Mobile=Nos, Name=Nme, Sunday=SundayX, Monday=MondayX, Tuesday=TuesdayX, Wednesday=WednesdayX, Thursday=ThursdayX, Friday=FridayX, Saturday=SaturdayX, StartTime=lblTimeFrom.Text, EndTime=lblTimeTo.Text)

then


SQLLover.ExecNonQuery2("REPLACE: SET (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", WITH: SET (Mobile=Nos, Name=Nme, Sunday=SundayX, Monday=MondayX, Tuesday=TuesdayX, Wednesday=WednesdayX, Thursday=ThursdayX, Friday=FridayX, Saturday=SaturdayX, StartTime=lblTimeFrom.Text, EndTime=lblTimeTo.Text)

I can get this to work

SQLLover.ExecNonQuery("UPDATE Profiles SET Mobile ='Nos', Name = 'Nme', Sunday = 'SundayX', Monday = 'MondayX', Tuesday = 'TuesdayX', Wednesday = 'WednesdayX', Thursday = 'ThursdayX', Friday = 'FridayX', Saturday = 'SaturdayX', StartTime = 'lblTimeFrom.text', EndTime = 'lblTimeTo.Text' WHERE Mobile='07123456789' AND Name='Sam'")

But of course I have no variables here just text inside single quotes 'xxx'

Just can get this to work

Richard:

:sign0013:
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
I did not mean for you to use the word REPLACE in the code. I meant for you to replace something with something else. Anyway,
If it does not work for you, please post you complete project, one of us will be able to help. You can zip your project from IDE, Export, as zip. Here is how the code should be:
B4X: 
SQLLover.ExecNonQuery2("Update Profiles SET Mobile=?, Name =?, Sunday =?, Monday = ?," _
& "Tuesday =? , Wednesday =?, Thursday =?, Friday =?, Saturday= ?, StartTime = ?, " _
& "EndTime =?   WHERE  Mobile='07123456789' AND Name='Sam'",  _
Array As Object(Nos, Nme, SundayX, MondayX, TuesdayX, WednesdayX, ThursdayX, FridayX, _
SaturdayX, lblTimeFrom.text, lblTimeTo.Text))
 
Last edited:
Upvote 0
raphipps2002

raphipps2002

Active Member
Licensed User
Longtime User
Here us the full SUB...

Sorry I am new to Basic4android and need some hand holding....conquered a lot on my own but this is has got me stuck. Anyhow here is the relevant SUB

Seven Variables (SundayX to SaturdayX) start with 0 (int type) and dependent upon checkboxes they are set to TRUE or FALSE, but I used an integer 0 or 1 instead of boolean to avoid any further issues with boolean types.

If the profile does NOT exist a new row is inserted into the SQL db successfully. But if cursor count > 0 then it must be present already so ELSE comes into play, and then I want to update that record instead.

The creation, and inserting works fine. I have tested the tables with sql browser and they data is complete and accurate.



Sub FillProfile


Dim SundayX As Int = 0
Dim MondayX As Int = 0
Dim TuesdayX As Int = 0
Dim WednesdayX As Int = 0
Dim ThursdayX As Int = 0
Dim FridayX As Int = 0
Dim SaturdayX As Int = 0

If chkSun.Checked = True Then
SundayX = 1
End If

If chkMon.Checked = True Then
MondayX = 1

End If

If chkTues.Checked = True Then
TuesdayX = 1
End If


If chkWed.Checked = True Then
WednesdayX = 1
End If

If chkThur.Checked = True Then
ThursdayX = 1
End If

If chkFri.Checked = True Then
FridayX = 1
End If

If chkSat.Checked = True Then
SaturdayX = 1
End If


SQLLover.Initialize(File.DirRootExternal, "/Lover/Lover.db", True)

Dim Cursor1 As Cursor

Cursor1 = SQLLover.ExecQuery2("SELECT Mobile, Name, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, StartTime, EndTime FROM Profiles WHERE Mobile = ? AND Name = ?", Array As String(Nos, Nme))

'Row many rows with this criteria.....hopefully only 1

If Cursor1.RowCount = 0 Then 'No profile exists

SQLLover.ExecNonQuery2("INSERT INTO Profiles VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As Object(Nos, Nme, SundayX, MondayX, TuesdayX, WednesdayX, ThursdayX, FridayX, SaturdayX, lblTimeFrom.text, lblTimeTo.Text))

Else 'Profile already exists


Msgbox("Client Profile already exists","Profile will be updated")

***** THIS is the Syntax I am having problems with 8 *****


SQLLover.ExecNonQuery2("Update Profiles SET(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As Object(Nos, Nme, SundayX, MondayX, TuesdayX, WednesdayX, ThursdayX, FridayX, SaturdayX, lblTimeFrom.Text, lblTimeTo.Text))

End If



End Sub


** The more I read perhaps I need a WHERE statement in here somewhere?

Any way If you can get this working I can continue my project

thanks very much

RP
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
** The more I read perhaps I need a WHERE statement in here somewhere?
Click to expand...

You are not following or not reading instructions we give you. In the previous post, I put in what I think is the correct but you did not use it, you used something else. Please see my previous post code for an example:
If you want any of us to help you, you need to zip your entire project:
While your project is open on the IDE:
Click 'File', then click 'Export as zip' and zip it to a file and upload it to the forum. Also, make sure the database db file you use is also attached..
 
Upvote 0
mc73

mc73

Well-Known Member
Licensed User
Longtime User
Of course you need a 'where' otherwise all db records will be updated with the chosen values. For updating you can use the phone number as unique id (I don't like this idea, cause occasionally you may want to alter the phone number as well, so I would suggest a unique auto-increment ID field). Here's the query:
B4X: 
SQLLover.ExecNonQuery2("UPDATE Profiles SET Nme=?,SundayX=?,MondayX=?,TuesdayX=?,WednesdayX=?,ThursdayX=?,FridayX=?,SaturdayX=?,StartTime=?, EndTime=?  WHERE Mobile=?",Array As Object(Nme, SundayX, MondayX, TuesdayX, WednesdayX, ThursdayX, FridayX, SaturdayX,lblTimeFrom.Text, lblTimeTo.Text,Nos))
 
Upvote 0
raphipps2002

raphipps2002

Active Member
Licensed User
Longtime User
I did

Thanks for your help. I did try all your suggestions and permutations. This one you send me I did not see before and I apologize if you did give this one to me. I will try the last suggestions and let you know

regards
 
Upvote 0
raphipps2002

raphipps2002

Active Member
Licensed User
Longtime User
Fantastic!!!

You are a star!!! Perfect works and I now see better the sql struture and syntax.

:sign0142:
:sign0098:
:sign0148:

Huge relief as I am so close to finishing this project

regards

RP
 
Upvote 0
You must log in or register to reply here.
Top