I have this little side project as I try to finish and now there is an interesting problem that almost not exist!
Why isn’t the first post possible to edit? I hope the images are clear enough! Anyideas of what might be the issue or where to look forthe solution!?
My guess, since we can’t see the enclosing code, is that you are expecting the first row in the listbox to be row 1 but listboxes start from row 0. What is the variable intURLID being initialized at? Is it a loop counter?
Good Luck!
While unrelated to your issue, you are using str to convert the recordset fields that have already been converted to strings with .stringvalue.
As Dale said, you should also show the code above, esp. where you fill your Listox.
I guess there might be a wrong IF clause or you’re not zero indexing.
But I see some other serious errors in your code:
You’re checking your DB for errors but only throwing a MsgBox in case of errors. You should at least stop any further processing of rs or any Fields.
You should check if rs <> Nil before processing it.
You should check your SQL Strings for unexpected characters like ’ , " or % to prevent SQL Code Injection.
In your 2nd query you are adding a Variant to a String. This might lead to another unexpected results.
Thank you for taking the time to look at my code!
My code is far from school-book perfect, but for me, it’s really not so important. If it’s working, I’m happy.
SQL injection is not an issue at this point or in this case.
Every user has full access to all data and if someone want to play with it… then, why not!
I’ve edited all images and also added the ID-code image to show, it’s really not that bad after all! However, something is missing.
I hope it’s more clear.
instead of
txtURL.text = Str(rs.Field("strName").StringValue)split that up into its parts and break on the special case. In the debugger are rs, dbf, s and s2 what you think they should be?
dim dbf As DatabaseField = rs.Field("strName")
dim s As String = dbf.StringValue
dim s2 As String = Str(s)
if intURLID = 1 then break
txtURL.text = s2
Hi Jakob,
When I run into these kind-of errors, there is a chance that the code works and there is the possibility of a logical error. In your third screen grab, could I ask you to modify the program with the following code and see if this gives the data that seems correct to you?
...previous code in screengrab 3
txtURL.Enabled = TRUE
txtURL.text = CStr(rs.Field("strName").IntegerValue)
lblKeyword.text = "Edit URL: " + Cstr(rs.field("strName").IntegerValue)
lblURL.text = "Insert Keyword to URL: " + Cstr(rs.field("strName").IntegerValue)
'Put message box's here to make sure the 'strname' is the number that is expected
..more code from screen grab 3
...previous code in screengrab 4
while not rs.eof
lstURL.AddRow(Cstr(rs.field("strName").IntegerValue) + " " + rs.Field("dteCreated").StringValue + ", ID = " + Cstr(rs.Field("intID").IntegerValue)
'Put a messagebox here to see if the variables 'strName', 'dteCreated', and 'intID' are expected values
lstURlRowTag....etc...more code from screen grab 4
The assumptions are to double check the values to make sure that they are the logical values, and not necessarily the ones look like they are correct. Another part to check is to make sure all zeros are actually zeros and not the letter o.
Its easy to get messed up when sometimes the numbers are zero-based, and some are one-based.
Not sure if this will help, and it may help us know what variables are giving correct information. 
Oh wait, I see now thats not the problem. All 3 control texts are set from the same code yet txtURL isn’t being filled.
My guess is something on the textfield got accidentally changed thats hiding text. Like its change event is running code that ends up clearing itself, or maybe the TextColor is white:)
Try changing the 3 pointed to lines to this
dim s As String = Str(rs.Field("strName").StringValue)
txtURL.text = ">> " + s
lblKeyword.text = "Edit URL: " + s
lblURL.text = "InsertKeyword to URL: " + s
if txtURL still has no text then its probably triggering some other code.
Thank you for your input! It’s valuable!
I really haven’t done very much fiddeling and testing, since I’m running out of ideas…
Some comments:
Examine the value of “oldurl.com” in the debugger. What is the length? What are the byte values? (The debugger will show you the sting in hex.) There must be something odd in the data for that value.
Hi Jakob,
Just taking a wild guess here and it may have something to do with earlier code in the programme, why does the newURL1 and newURL2 have a date and timestamp, where the oldurl.com has only a datestamp?
I have no idea if this changes anything, like I said, just taking a guess.
Eugene,
DIM d as Date
dr.Column(“dteCreated”) = d.ShortDate
dr.Column(“dteCreated”) = d
Is there any formatting specified in the definition of the txtURL field that may be causing the particular format of that line to fail? Try adding an additional line in the Name of URL listbox, one that has the .com on the URL. Maybe the problem has something to do with having that URL format since the only one failing is the one with the .com extension. If both fail, then that tells us something (I’m not sure what, exactly, but…)
I have to admit, this is a pretty perplexing problem.
Please change your line MsgBox tmpTxt to MsgBox EncodeHex(tmpTxt) and post the result.
Again, without the code it’s hard to guess, every .Action or .Change Property could do anything behind we can’t see.
What about a downloadable link of an Sample?
Mmm just another thought:
What kind of database you are using and is your Cursor client-side or server-side?
I remember on SQLServer I’ve seen similar glitches and avoided them in using temp. variables for every Field and doing all calculations or transformations only with the variables and not with the rs.Fields itself.
tmpName = rs.field("strname").StringValue
MsgBox = tmpName
txtURL.text = tmpName
lblKeyword.text = "Edit URL: " + tmpName
lblURL.text = "InsertKeyword to URL: " + tmpName
tmpName = ""Check it out, try not to call rs.Fields twice…
Thank you all for the great attempt to try to solve this issue.
My research so far is quite limited. However, I found this problem also in other project… But also some working projects.
I think I can live with this error, since I now find out there is all URL’s added in the code, while in the debugging status.
So, it’s for my eyes only, since the users never will work in the compiled version of the software.
Phew… witch reminded me, I haven’t compiled yet to try this scenario… but I think all is fine!
EDIT :: I’ve now tried to compile it and I notice, the software is really ugly, but at least, this particular part seems to work as expected. Just hope it’s not a false alarm!!
[code]
’ ## CLEAR KEYWORDS
lstKeyword.DeleteAllRows
IF me.listindex <> -1 THEN
'MsgBox me.RowTag(me.ListIndex)
intURLID = me.RowTag(me.ListIndex)
' ## SELECT THE CURRENT URL
Dim dbDatabase As SQLiteDatabase
Dim TargetFile As FolderItem
dbDatabase = New SQLiteDatabase
Dim blnResult as boolean
TargetFile = GetFolderItem("database.rsd")
dbDatabase.DatabaseFile = TargetFile
blnResult = dbDatabase.Connect
if dbDatabase.Error then
msgbox dbDatabase.ErrorMessage
end
IF blnResult = true then
dim sql as string
'sql = "SELECT intID, strName FROM [KATURL] WHERE intID = " + str(me.RowTag(me.ListIndex))
sql = "SELECT intID, strName FROM [KATURL] WHERE intID = " + intURLID
dim rs as RecordSet = dbDatabase.SQLSelect(sql)
if dbDatabase.Error then
msgbox dbDatabase.ErrorMessage
end
'txtURL.Enabled = TRUE
DIM tmpTxt as String = str(rs.Field("strName").StringValue)
MsgBox EncodeHex(tmpTxt)
txtURL.text = tmpTxt
lblKeyword.text = "Edit URL: " + tmpTxt
lblURL.text = "Insert Keyword to URL: " + tmpTxt
btnInsertURL.enabled = true
btnInsertURL.Caption = "Edit"
' ## CHECK IF ANY KEYWORDS TO THIS URL
sql = "SELECT intID, strName FROM [KATKeyword] WHERE intURLID = " + me.RowTag(me.ListIndex)
rs = dbDatabase.SQLSelect(sql)
if not rs.eof then
btnDeleteURL.Enabled = false
loadKeyword
else
btnDeleteURL.Enabled = true
end if
END IF
ELSE
txtURL.Enabled = TRUE
txtURL.text = “”
txtKeyword.Enabled = FALSE
txtKeyword.text = “”
btnInsertURL.Caption = “Insert”
btnInsertURL.Enabled = FALSE
btnDeleteKeyword.Enabled = false
btnInsertKeyword.caption = “Insert”
btnInsertKeyword.Enabled = false
lblURL.text = “New URL”
lblKeyword.text = “”
END IF[/code]
The only way I can make this happen is by inserting a null character (chrb(0)) at the beginning of the string. This displays on the label but not the textfield. This may not show in the message box so can you change that line again to msgbox EncodeHex("(" + tmptext + “)”) and posting the result here.
You could also try replacing
DIM tmpTxt as String = str(rs.Field(“strName”).StringValue)
with
DIM tmpTxt as String = ReplaceAll(rs.Field(“strName”).StringValue, chrb(0), “”)
Dear colleagues!
Excuse me for the slow conversation. Other things in life knock on my shoulder and ask for attention…!
However, I still would like to continue the unsolved mystery!
I tried Waynes suggestion above, but, of-course, no luck.
The point is, the error occurs when I INSERT the data in the development mode. But only the URL’s! Not the Keywords!
[code]
Dim db As SQLiteDatabase
Dim TargetFile As FolderItem
Dim blnResult as boolean
db = New SQLiteDatabase
TargetFile = GetFolderItem(“database.rsd”)
'main.strDBPath = CStr(TargetFile)
db.DatabaseFile = TargetFile
IF TargetFile <> Nil THEN
//menu.strDatabasePath =str(TargetFile.absolutepath)
blnResult = db.CreateDatabaseFile
if db.Error then
msgbox db.ErrorMessage
end
IF blnResult = true then
// KEYWORD DOMINATOR
[…]
// KEYWORD ACCESS TOOL
db.SQLExecute ("Create Table [KATURL] ( intID Integer PRIMARY KEY AUTOINCREMENT, strName varchar, dteCreated timeStamp)")
db.SQLExecute ("Create Table [KATKeyword] ( intID Integer PRIMARY KEY AUTOINCREMENT, strName varchar, dteCreated timeStamp, intURLID Integer)")
// KAT :: BATCH + LOG
[...]
// DOMINATOR :: BATCH + LOG
[...]
// Dominator Settings
[...]
// ## KAT.SETTINGS
[...]
db.Commit
IF DebugBuild THEN
dim dr as new DatabaseRecord
dim d as New date
DIM intLastID As Integer
// KEYWORD ACCESS TOOL
dr.Column("strName") = "www.1111firstURL.com"
dr.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATURL",dr )
db.Commit
intLastID = db.lastRowID
dr.Column("strName") = "vadstena konstrunda"
dr.Column("intURLID") = str(intLastID)
dr.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATKeyword",dr )
dr.Column("strName") = "konstrundan vadstena"
dr.Column("intURLID") = str(intLastID)
dr.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATKeyword",dr )
dr.Column("strName") = "konstnrer i vadstena"
dr.Column("intURLID") = str(intLastID)
dr.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATKeyword",dr )
dr.Column("strName") = "konstrunda vadstena 2013"
dr.Column("intURLID") = str(intLastID)
dr.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATKeyword",dr )
db.Commit
DIM dr4 as New DatabaseRecord
dr4.Column("strName") = "www.secondURL.com"
dr4.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATURL",dr4 )
db.Commit
intLastID = db.lastRowID
dr4.Column("strName") = "ryska ambassaden"
dr4.Column("intURLID") = str(intLastID)
dr4.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATKeyword",dr4 )
dr4.Column("strName") = "visum, ryssland"
dr4.Column("intURLID") = str(intLastID)
dr4.Column("dteCreated") = d.ShortDate
db.InsertRecord( "KATKeyword",dr4 )
db.Commit
'db.Commit
if db.error then
msgbox str(db.ErrorCode) + " -- " + db.ErrorMessage
return
end
'db.Commit
// DOMINATOR
dim dr2 as new DatabaseRecord
[...]
if db.error then
msgbox str(db.ErrorCode) + " -- " + db.ErrorMessage
return
end
db.Commit
END IF
' ## SETTINGS
DIM dr3 as NEW DatabaseRecord
[...]
db.Close
'MsgBox "Database created"
if db.Error then
msgbox db.ErrorMessage
end
ELSE
MsgBox "Database fail"
END IF
ELSE
// dbDatabase EXISTS, NO NEED TO CREATE NEW DATABASE.
'blnResult = dbDatabase.Connect
END IF[/code]
Oh dear! Why can’t I edit my own posts??? Grrrr…
These images should be on the above post. The URL’s are affected with this error but the connecting Keywords are working fine, as expected! It’s really difficult to tell what is wrong, since some parts are actually working as expected.
When I edit the URL’s (that part of the post) the error remain…!
But if I create a NEW URL in the User Interface, then there is no problem to EDIT that post!
The best thing you could do is post your software somewhere everyone can grab a copy to help figure this out