Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Thu Jun 21, 2018 8:35 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 41 posts ]  Go to page Previous  1, 2, 3  Next
Author Message
 Post subject: Re: Database Not found
PostPosted: Wed Mar 13, 2013 3:29 pm 
Offline

Joined: Fri Nov 18, 2005 7:26 pm
Posts: 63
pfargo wrote:
rs = database.SQLSelect(sql)   /// This item does not exist


database. <> db.



Ok this does not mean anything to me. Does that mean I shoudl use DB. instead of database.

From a learning point of view the more specific the better, the reason I went wrong imn the first place is that I may not quite understand what I am doing wrong so any help anyone can give on exactly where I went wrong would be useful .

Thanks all I do apprecaite the help


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Wed Mar 13, 2013 3:47 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
There you go, this is working:

Dim dbFile as FolderItem
Dim db as New REALSQLdatabase
Dim sql as String
Dim rec as DatabaseRecord
Dim rs as RecordSet

// Get database folder
dbFile = GetFolderItem("people.db")
db.DatabaseFile = dbFile

if db.DatabaseFile.Exists = false then
if db.CreateDatabaseFile = false then
MsgBox "The database file was not created" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if
db.SQLExecute ("create table employees (ID integer not null primary key, Firstname text, Lastname text, email text)") // You wer missing a parenthesis
db.Commit // As suggested by Tim
if db.Error then
MsgBox "The table was not created" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if
end if

if db.Connect = false then // You are missing this part as pointed out by markwalsh
MsgBox "Could not connect to the database "
exit
end if

// For testing purposes lets create more than one record
dim i as integer
for i = 1 to 20
rec = New DatabaseRecord
//rec.IntegerColumn("id") = 1
rec.Column("Firstname") = "Mikcey" + " " + CStr(i)
rec.Column("Lastname")="Mouse"
rec.Column("email") = "email@email.com"
db.InsertRecord("employees",rec)
db.Commit
if db.error then // Check if the record was inserted
MsgBox "Error inserting the record" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if

next i

sql = "Select ID, Firstname , Lastname, email from employees"
rs = db.SQLSelect(sql) //This item does not exist as "database" as pointed out by Tegwin
if db.Error then
MsgBox "Error getting the recordset" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
end if

while not rs.EOF
ListBox1.AddRow(rs.Field("ID").StringValue)
rs.Movenext
Wend

rs.close


Now try to split this block of code into various functions that accomplish a specific task. For example: One function for testing if the database file exist and creating it if it doesn't, one function to create the table and one function to read the data from the database and display it in the listbox.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Wed Mar 13, 2013 8:47 pm 
Offline

Joined: Mon Oct 13, 2008 4:26 am
Posts: 63
Quote:


Ok this does not mean anything to me. Does that mean I shoudl use DB. instead of database.

From a learning point of view the more specific the better, the reason I went wrong imn the first place is that I may not quite understand what I am doing wrong so any help anyone can give on exactly where I went wrong would be useful .

Thanks all I do apprecaite the help


Sorry about that. I was in a rush and hoped it would be enough. I should have held off on posting. Yes you should use db which is what you declared the name to be above.


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Thu Mar 14, 2013 12:04 pm 
Offline

Joined: Fri Nov 18, 2005 7:26 pm
Posts: 63
HMARROQUINC wrote:
There you go, this is working:

Dim dbFile as FolderItem
Dim db as New REALSQLdatabase
Dim sql as String
Dim rec as DatabaseRecord
Dim rs as RecordSet

// Get database folder
dbFile = GetFolderItem("people.db")
db.DatabaseFile = dbFile

if db.DatabaseFile.Exists = false then
if db.CreateDatabaseFile = false then
MsgBox "The database file was not created" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if
db.SQLExecute ("create table employees (ID integer not null primary key, Firstname text, Lastname text, email text)") // You wer missing a parenthesis
db.Commit // As suggested by Tim
if db.Error then
MsgBox "The table was not created" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if
end if

if db.Connect = false then // You are missing this part as pointed out by markwalsh
MsgBox "Could not connect to the database "
exit
end if

// For testing purposes lets create more than one record
dim i as integer
for i = 1 to 20
rec = New DatabaseRecord
//rec.IntegerColumn("id") = 1
rec.Column("Firstname") = "Mikcey" + " " + CStr(i)
rec.Column("Lastname")="Mouse"
rec.Column("email") = "email@email.com"
db.InsertRecord("employees",rec)
db.Commit
if db.error then // Check if the record was inserted
MsgBox "Error inserting the record" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if

next i

sql = "Select ID, Firstname , Lastname, email from employees"
rs = db.SQLSelect(sql) //This item does not exist as "database" as pointed out by Tegwin
if db.Error then
MsgBox "Error getting the recordset" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
end if

while not rs.EOF
ListBox1.AddRow(rs.Field("ID").StringValue)
rs.Movenext
Wend

rs.close


Now try to split this block of code into various functions that accomplish a specific task. For example: One function for testing if the database file exist and creating it if it doesn't, one function to create the table and one function to read the data from the database and display it in the listbox.


Massive thanks for that.. This has helped a huge amount.. I now see how different my code is to yours.. I guess its all part of the learning process. Thank you very much


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 10:31 am 
Offline

Joined: Fri Nov 18, 2005 7:26 pm
Posts: 63
Ok thanks everyone I am progresssing a bit with this but hit another snag

I have added an edit button so that I can pull a record from the lIstbox and edit it.. When I do this I get a Nil Exception Error on my Recordset. I have used the if rs = Nil to let me know it is Nil and I always get the error.

So here is what i did .

I created a property on the main Window for Db as RealSQLDatabase and also rs as RecordSet

I have set the double click of the Listbox to update my Text boxes which it does ok



//Fille the Listbox with information from the dataBASE


dim sql as string

//Dim dr AS new DatabaseRecord
//dim rs as RecordSet


sql = "Select ID, Firstname, Lastname, email from people where ID = " + lstPeople.Cell(lstPeople.Listindex, 0)


if rs <> Nil Then
rs = db.SQLSelect(Sql)

rs.Edit

rs.Field("Firstname").StringValue = trim(txtFirstname.text)
rs.Field("Lastname").StringValue = trim(txtLastName.Text)
rs.Field("email").StringValue = trim(txtemail.text)

rs.update

db.commit /// Commit Changes to Database

End if

if rs = Nil then
MsgBox " You have a Nil Record Set "
End if


rs.Close

//Update the list box with the new changed fields

lstPeople.Cell(lstPeople.Listindex, 1) = trim(txtFirstName.text)
lstPeople.Cell(lstPeople.Listindex, 2) = trim(txtLastname.Text)
lstPeople.Cell(lstPeople.Listindex, 3) = trim(txtemail.text)



This is the main code I have in the Window open event

//  Check if database already exists and use it if it does

Dim dbFile as FolderItem
Dim sql as String
Dim rec as DatabaseRecord
Dim rs as RecordSet

//Instantiate the Database
db = New REALSQLDatabase

// Get database folder
dbFile = GetFolderItem("people1.db")
db.DatabaseFile = dbFile

if db.DatabaseFile.Exists = false then
if db.CreateDatabaseFile = false then
MsgBox "The database file was not created" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if
db.SQLExecute ("create table employees (ID integer not null primary key, Firstname text, Lastname text, email text, pic BLOB)")
db.Commit
if db.Error then
MsgBox "The table was not created" + EndOfLine + _
CStr(db.ErrorCode) + " - " + db.ErrorMessage
exit
end if
end if

if db.Connect = false then
MsgBox "Could not connect to the database "
exit
end if


I am not sure if this is because its out of scope or something..

Any help is appreciated


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 10:37 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
sql = "Select ID, Firstname, Lastname, email from people where ID = " +  lstPeople.Cell(lstPeople.Listindex, 0)


if rs <> Nil Then
rs = db.SQLSelect(Sql)

rs.Edit


you are CHECKING rs BEFORE you assign it

try this instead

sql = "Select ID, Firstname, Lastname, email from people where ID = " +  lstPeople.Cell(lstPeople.Listindex, 0)

rs = db.SQLSelect(Sql)
if rs <> Nil Then
rs.Edit

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 11:19 am 
Offline

Joined: Fri Nov 18, 2005 7:26 pm
Posts: 63
Dave, thanks very much for your help. I have tried what you suggested and still have the same error

This is my code below

Should I be using the Dim rs RecordSet here or is it ok to be using it as a property


sql = "Select ID, Firstname, Lastname, email from people where ID = " +  lstPeople.Cell(lstPeople.Listindex, 0)

rs = db.SQLSelect(Sql)


if rs <> Nil Then
rs.Edit

rs.Field("Firstname").StringValue = trim(txtFirstname.text)
rs.Field("Lastname").StringValue = trim(txtLastName.Text)
rs.Field("email").StringValue = trim(txtemail.text)

rs.update

db.commit /// Commit Changes to Database

End if

if rs = Nil then
MsgBox " You have a Nil Record Set "
End if


rs.Close


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 11:26 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
ALL variables/objects must be DIM'd somewhere... either local or global.... so yes RS must be DIM'd, as must SQL

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 11:32 am 
Offline

Joined: Fri Nov 18, 2005 7:26 pm
Posts: 63
Ok I have removed it from the property and Dimed it locally, but I am still getting the same results.


This is what I have in the Edit button

//Fille the Listbox with information from the dataBASE

Dim rs as RecordSet
Dim sql as String


sql = "Select ID, Firstname, Lastname, email from people where ID = " + lstPeople.Cell(lstPeople.Listindex, 0)


rs = db.SQLSelect(Sql)


if rs <> Nil Then
rs.Edit

rs.Field("Firstname").StringValue = trim(txtFirstname.text)
rs.Field("Lastname").StringValue = trim(txtLastName.Text)
rs.Field("email").StringValue = trim(txtemail.text)

rs.update

db.commit /// Commit Changes to Database
rs.close

End if

if rs = Nil then
MsgBox " You have a Nil Record Set "
End if


I still get the Nil .. in my messagebox and then the NilObjectException Error


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 11:57 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
Personally I prefer using pure SQL to do this type of operations

DIM SQL as string
SQL= _
"UPDATE people "+_
" SET firstname='"+trim(txtfirstname.text)+"'," + _
" lastname ='"+trim(txtlastname.text)+"',"+_
" email="+trim(txtemail.textf+"'"+_
" WHERE ID="+lstPeople.Cell(lstPeople.Listindex, 0)

DB.SQLExecute SQL
If DB.Error Then
DB_Error(rollback,SQL)
Return False
Else
DB.Commit
Return True
End If



I use similar code to for INSERT records..... usually only use RECORDSET to READ data

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Last edited by DaveS on Sat Mar 16, 2013 12:01 pm, edited 4 times in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 11:59 am 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
You are never assigning anything to the global variables or properties. The db and rs you are working with in your opening code are local, you dim them inside the procedure and they go out of scope when the procedure ends.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 12:35 pm 
Offline

Joined: Fri Nov 18, 2005 7:26 pm
Posts: 63
HMARROQUINC wrote:
You are never assigning anything to the global variables or properties. The db and rs you are working with in your opening code are local, you dim them inside the procedure and they go out of scope when the procedure ends.
#

Ok so I need to dim the database as well inside that procedure ? How can I make it I dont need to Dim it every time ?


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 12:46 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
dim the database (DB) in a MODULE this will make it PUBLIC and accessible to ALL other parts of your program

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 12:53 pm 
Offline

Joined: Fri Nov 18, 2005 7:26 pm
Posts: 63
Tegwin wrote:
HMARROQUINC wrote:
You are never assigning anything to the global variables or properties. The db and rs you are working with in your opening code are local, you dim them inside the procedure and they go out of scope when the procedure ends.
#

Ok so I need to dim the database as well inside that procedure ? How can I make it I dont need to Dim it every time ?



Ok Not sure I understand what I am doing wrong .. this is really frustrating . I have now declared the database as well and still get the same error .

//Fille the Listbox with information from the dataBASE



Dim sql as String
Dim rs as RecordSet
Dim db as REALSQLDatabase

Dim dbFile as FolderItem


// Get database folder
dbFile = GetFolderItem("people1.db")
db.DatabaseFile = dbFile

if db.Connect = False Then
MsgBox "Unable to Connect to the Database"

End if

sql = "Select ID, Firstname, Lastname, email from people where ID = " + lstPeople.Cell(lstPeople.Listindex, 0)

rs = db.SQLSelect(Sql)


if rs <> Nil Then
rs.Edit

rs.Field("Firstname").StringValue = trim(txtFirstname.text)
rs.Field("Lastname").StringValue = trim(txtLastName.Text)
rs.Field("email").StringValue = trim(txtemail.text)
rs.update
db.commit /// Commit Changes to Database
rs.close


End if

if rs = Nil then
MsgBox " You have a Nil Record Set "
End if


lstPeople.Cell(lstPeople.Listindex, 1) = trim(txtFirstName.text)
lstPeople.Cell(lstPeople.Listindex, 2) = trim(txtLastname.Text)
lstPeople.Cell(lstPeople.Listindex, 3) = trim(txtemail.text)


Please can someone point me in the right direction, no matter what I am trying I get the same errors. It has no problems adding the data but cannot edit it


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Not found
PostPosted: Sat Mar 16, 2013 12:58 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
are you SURE this is generating a valid SQL statement?

sql = "Select ID, Firstname, Lastname, email from people where ID = " + lstPeople.Cell(lstPeople.Listindex, 0)

are you SURE there is a record to BE editted?

Have you tried using the UPDATE SQL method?


AND DO NOT DIM THE DATABASE INSIDE A LOCAL PROCEDURE..... you do not want to connect and disconnect on every single operation.

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 41 posts ]  Go to page Previous  1, 2, 3  Next

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group