RE: [sqlite] Operation is not allowed when the object is closed
Can you try this code: Dim sqlite As New SQLiteDb.Connection With sqlite .Open "Data Source=c:\temp\test.db" If .State = slStateOpen Then .Execute "CREATE TABLE [A3Test8CA_F] ([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER, [READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER, [NUMERIC_VALUE] REAL)", , slExecuteNoRecords .Close End If End With Set sqlite = Nothing MsgBox "Done" I tried it both with and without test.db existing, and it worked fine both times. Eric Pankoke Founder Point Of Light Software http://www.polsoftware.com/ -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Saturday, December 09, 2006 6:39 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Operation is not allowed when the object is closed This is the full Sub that sets the connection, except for leaving out a msgbox text: Sub SetSQLiteConnection(Optional bNoRecords As Boolean, _ Optional strDBFile As String, _ Optional lPageSize As Long = -1) Dim strDBPath As String If Len(strDBFile) = 0 Then strDBPath = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Path to SQLite database") If bFileExists(strDBPath) = False Then Exit Sub End If Else strDBPath = strDBFile End If If Len(strDBFile) > 0 Then If Not SQLiteConn Is Nothing Then If SQLiteConn.State = slStateOpen Then SQLiteConn.Close End If Set SQLiteConn = Nothing End If End If If SQLiteConn Is Nothing Then Set SQLiteConn = New SQLiteDb.Connection SQLiteConn.ConnectionString = "Data Source=" & strDBPath End If On Error Resume Next Do While SQLiteConn.State = slStateClosed SQLiteConn.Open Loop If bNoRecords Then SQLiteConn.Execute "PRAGMA synchronous=off;", , slExecuteNoRecords SQLiteConn.Execute "PRAGMA encoding='UTF-8';", , slExecuteNoRecords End If If lPageSize > -1 Then SQLiteConn.Execute "PRAGMA page_size=" & lPageSize & ";", , slExecuteNoRecords End If End Sub And this is the relevant code leading up to the error: SetSQLiteConnection True, strDBFile With SQLiteConn .BeginTrans If SQLiteTableExists(strDBFile, strTable) = False Then .Execute strCreateTable, 0, 128 <<< error here <<< End If strCreateTable is a valid SQL to create a table: CREATE TABLE [A3Test8CA_F] ([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER, [READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER, [NUMERIC_VALUE] REAL) RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 09 December 2006 23:27 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Operation is not allowed when the object is closed On Sat, 9 Dec 2006 22:24:59 -, you wrote: > Keep getting this error when trying to create a table > in SQLite. There is a valid connection object and it > is open and I have no idea what is causing this: > > Operation is not allowed when the object is closed. > Error number: 3704 Please show us some code. Did you check for any errors on the connection statement? >Thanks for any advice > >RBS > > > >--- -- >To unsubscribe, send email to [EMAIL PROTECTED] >--- -- -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Operation is not allowed when the object is closed
I don't really need the loop and I could just do: If SQLiteConn.State <> slStateOpen Then SQLiteConn.Open End If Will try that in a bit. Still, the connection gets closed and opened all the time with the same routine and no problem at all, except for in that shown code fragment. The error originates from the wrapper and it is says the source is the connection object. Unfortunately the help file doesn't help. RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 10 December 2006 00:44 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Operation is not allowed when the object is closed On Sat, 9 Dec 2006 23:38:47 -, you wrote: > On Error Resume Next > Do While SQLiteConn.State = slStateClosed > SQLiteConn.Open > Loop I has been quite some time since I wrote VB, but I'll give it a try. You seem to assume that SQLiteConn.Open raises an error when it fails. If it doesn't raise an VBerror, you don't detect something is wrong. SQLiteConn.State will not equal slStateClosed but the error status, so the loops ends and you stop trying to open, without having opened the database. HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Operation is not allowed when the object is closed
On Sat, 9 Dec 2006 23:38:47 -, you wrote: > On Error Resume Next > Do While SQLiteConn.State = slStateClosed > SQLiteConn.Open > Loop I has been quite some time since I wrote VB, but I'll give it a try. You seem to assume that SQLiteConn.Open raises an error when it fails. If it doesn't raise an VBerror, you don't detect something is wrong. SQLiteConn.State will not equal slStateClosed but the error status, so the loops ends and you stop trying to open, without having opened the database. HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Operation is not allowed when the object is closed
This is the full Sub that sets the connection, except for leaving out a msgbox text: Sub SetSQLiteConnection(Optional bNoRecords As Boolean, _ Optional strDBFile As String, _ Optional lPageSize As Long = -1) Dim strDBPath As String If Len(strDBFile) = 0 Then strDBPath = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Path to SQLite database") If bFileExists(strDBPath) = False Then Exit Sub End If Else strDBPath = strDBFile End If If Len(strDBFile) > 0 Then If Not SQLiteConn Is Nothing Then If SQLiteConn.State = slStateOpen Then SQLiteConn.Close End If Set SQLiteConn = Nothing End If End If If SQLiteConn Is Nothing Then Set SQLiteConn = New SQLiteDb.Connection SQLiteConn.ConnectionString = "Data Source=" & strDBPath End If On Error Resume Next Do While SQLiteConn.State = slStateClosed SQLiteConn.Open Loop If bNoRecords Then SQLiteConn.Execute "PRAGMA synchronous=off;", , slExecuteNoRecords SQLiteConn.Execute "PRAGMA encoding='UTF-8';", , slExecuteNoRecords End If If lPageSize > -1 Then SQLiteConn.Execute "PRAGMA page_size=" & lPageSize & ";", , slExecuteNoRecords End If End Sub And this is the relevant code leading up to the error: SetSQLiteConnection True, strDBFile With SQLiteConn .BeginTrans If SQLiteTableExists(strDBFile, strTable) = False Then .Execute strCreateTable, 0, 128 <<< error here <<< End If strCreateTable is a valid SQL to create a table: CREATE TABLE [A3Test8CA_F] ([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER, [READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER, [NUMERIC_VALUE] REAL) RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 09 December 2006 23:27 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Operation is not allowed when the object is closed On Sat, 9 Dec 2006 22:24:59 -, you wrote: > Keep getting this error when trying to create a table > in SQLite. There is a valid connection object and it > is open and I have no idea what is causing this: > > Operation is not allowed when the object is closed. > Error number: 3704 Please show us some code. Did you check for any errors on the connection statement? >Thanks for any advice > >RBS > > > >--- -- >To unsubscribe, send email to [EMAIL PROTECTED] >--- -- -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Operation is not allowed when the object is closed
On Sat, 9 Dec 2006 22:24:59 -, you wrote: > Keep getting this error when trying to create a table > in SQLite. There is a valid connection object and it > is open and I have no idea what is causing this: > > Operation is not allowed when the object is closed. > Error number: 3704 Please show us some code. Did you check for any errors on the connection statement? >Thanks for any advice > >RBS > > > >- >To unsubscribe, send email to [EMAIL PROTECTED] >- -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Operation is not allowed when the object is closed
This gives all the right answers, suggesting the object is set fine and the connection is open: If SQLiteConn Is Nothing Then MsgBox "nothing" End If MsgBox SQLiteConn.State = slStateOpen It still got me stumped. RBS -Original Message- From: Eric Pankoke [mailto:[EMAIL PROTECTED] Sent: 09 December 2006 22:47 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Operation is not allowed when the object is closed It sounds to me like you're using ADO? How do you know for sure that the connection is truly open? Eric Pankoke Founder Point Of Light Software http://www.polsoftware.com/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, December 09, 2006 5:36 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Operation is not allowed when the object is closed "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Keep getting this error when trying to create a table in SQLite. There is a > valid connection object and it is open and I have no idea what is causing > this: > > Operation is not allowed when the object is closed. > Error number: 3704 > Ain't no such error in SQLite. This must be coming from whatever wrapper or interface you are using. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Operation is not allowed when the object is closed
Yes, I am using the VB wrapper from TerraInformatica and I am coding in VBA and using ADO. The error doesn't show in the help and nil found with a websearch either. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 December 2006 22:36 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Operation is not allowed when the object is closed "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Keep getting this error when trying to create a table in SQLite. There is a > valid connection object and it is open and I have no idea what is causing > this: > > Operation is not allowed when the object is closed. > Error number: 3704 > Ain't no such error in SQLite. This must be coming from whatever wrapper or interface you are using. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Operation is not allowed when the object is closed
It sounds to me like you're using ADO? How do you know for sure that the connection is truly open? Eric Pankoke Founder Point Of Light Software http://www.polsoftware.com/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, December 09, 2006 5:36 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Operation is not allowed when the object is closed "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Keep getting this error when trying to create a table in SQLite. There is a > valid connection object and it is open and I have no idea what is causing > this: > > Operation is not allowed when the object is closed. > Error number: 3704 > Ain't no such error in SQLite. This must be coming from whatever wrapper or interface you are using. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Operation is not allowed when the object is closed
"RB Smissaert" <[EMAIL PROTECTED]> wrote: > Keep getting this error when trying to create a table in SQLite. There is a > valid connection object and it is open and I have no idea what is causing > this: > > Operation is not allowed when the object is closed. > Error number: 3704 > Ain't no such error in SQLite. This must be coming from whatever wrapper or interface you are using. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
