Page 1 of 1

CSV Import Error (Bookmark is invalid)

PostPosted: Wed Sep 02, 2015 6:34 am
by stu
Hello there,

I'm getting a nasty error after executing a CSV import, this is the code I'm using:

Code: Select all
dbpOpenDatabase "Test" "Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=[#34][#34];Initial Catalog=TestDB;Data Source=LOCAL\SQLEXPRESS;Initial File Name=[#34][#34];Server SPN=[#34][#34]"
dbpOpenTable "Test" "tblTempImport" ""
FileOpenBox "" "Any File|*.CSV" "D:\FE" "[SelectedFile]" ""
dbpExecSQL "Test" "DELETE FROM tblTempImport;" ""
dbpImportFromCSV "Test" "tblTempImport" "[SelectedFile]" "Delimiter=,;ContainsFieldNames=No;MatchFieldNames=No"


tblTempImport is a 20 column all VARCHAR(255) table that actually gets the data 10 out of 10, so the import is working.

But soon after the import is finished I get the following error message exactly 21 times (and one more time soon as I close the application):

Image

As an additional detail I tried on a different MSSQL Express (2008) instance and It worked with no problems the first time, after the first time every single time its it generated the same error even after restarting the application.

A work around (which I would rather ditch for a solution) is to go by:


Code: Select all
dbpOpenDatabase "Test" "Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=[#34][#34];Initial Catalog=TestDB;Data Source=LOCAL\SQLEXPRESS;Initial File Name=[#34][#34];Server SPN=[#34][#34]"
dbpOpenTable "Test" "tblTempImport" ""
FileOpenBox "" "Any File|*.CSV" "D:\FE" "[SelectedFile]" ""
dbpExecSQL "Test" "DELETE FROM tblTempImport;" ""
dbpShowErrors "No"
dbpImportFromCSV "Test" "tblTempImport" "[SelectedFile]" "Delimiter=,;ContainsFieldNames=No;MatchFieldNames=No"
dbpCloseTable "Test" "tblTempImport"
dbpOpenTable "Test" "tblTempImport" ""
dbpShowErrors "Yes"

Re: CSV Import Error (Bookmark is invalid)

PostPosted: Wed Sep 02, 2015 8:39 am
by Neosoft Support
Try adding a unique "primary key" field to your table. Many databases rely on the primary key for many tasks. You may be able to solve this problem by making one of your fields a primary key. This is a quirk of client-server databases. The easiest way to do this is to use an AutoInc type field which will always be unique.

Re: CSV Import Error (Bookmark is invalid)

PostPosted: Wed Sep 02, 2015 8:52 am
by stu
Neosoft Support wrote:Try adding a unique "primary key" field to your table. Many databases rely on the primary key for many tasks. You may be able to solve this problem by making one of your fields a primary key. This is a quirk of client-server databases. The easiest way to do this is to use an AutoInc type field which will always be unique.


Table does have PK.

Re: CSV Import Error (Bookmark is invalid)

PostPosted: Thu Sep 03, 2015 11:11 am
by Neosoft Support
That error doesn't happen with other database engines, so I'm not sure exactly what to suggest. It could be that the bookmark error is caused by the index being out of sync with the table's contents after the deletion. Here are some suggestions:

You could try using DBPro's dbpDeleteAll action instead of doing it through SQL (which happens outside of DBPro's influence):

dbpDeleteAll "Test" "tblTempImport"

You could try using truncate instead of delete to empty the table:

dbpExecSQL "Test" "TRUNCATE TABLE tblTempImport;" ""

You could also try adding select after deleting:

dbpExecSQL "Test" "DELETE FROM tblTempImport;SELECT * FROM tblTempImport" ""