6 replies to this topic

[dkingston]

I have written a CA in VBScript to dynamically populate a ComboBox.  I can't insert new records in the database, because Session.Database.DatabaseState = 0.  How does one go about making the database writeable?

[Ian Blake]

Yes the database is read only.  However you can insert temporary records.

[dkingston]

I had arrived at that conclusion, but have been unsuccessful in actually implementing it.  

I have a ComboBox whose property is set to 'Location', and I'm trying to add items to the ComboBox at run-time.  I can read the values out of the ComboBox, I just can't add any.  Here's a sample of my code:

Dim oDatabase
Dim oView
Dim oRecord
Dim sSQL

Set oDatabase = Session.Database
sSQL = "SELECT * FROM ComboBox WHERE Property = 'Location'"

Set oView = oDatabase.OpenView(sSQL)
oView.Execute
Set oRecord = oView.Fetch
MsgBox oRecord.StringData(4)

sSQL = "INSERT INTO ComboBox (Property, Order, Value, Text) VALUES ('Location', 1123, '5', 'TestText' TEMPORARY)"

On Error Resume Next
Set oView = oDatabase.OpenView(sSQL)
If Err.Number <> 0 Then
 MsgBox "Error " & Err.Number & " : " & Err.Description
 On Error Goto 0
End If

The first MsgBox does display the text of the existing item in the ComboBox (which was entered at design-time), but the second call to the OpenView method of the Database object throws an error.  The description of the trapped error is "OpenView,sql", which leads me to believe the SQL is improperly formatted, but I can't seem to find anything wrong with it.

[Joe Fegan]

Your INSERT INTO statement has TEMPORARY inside the closing parenthesis instead of after it.

[dkingston]

Can't believe i missed that.  Either way, this doesn't work either:

sSQL = "INSERT INTO ComboBox (Property, Order, Value, Text) VALUES ('Location', 1123, '5', 'TestText') TEMPORARY"

I even tried making a parameterized query out of it, and that doesn't work either. I always get the 'OpenView,slq' error.

sSQL = "INSERT INTO ComboBox (Order, Value, Text, Property) VALUES (?, ?, ?, 'Location') TEMPORARY"

Is there some fundamental error that I'm making?  Or is this code theoretically sound?

(Edited by dkingston at 9:13 am on Sep. 24, 2001)

[dkingston]

Got it. From MSDN, Windows Installer, SQL Syntax:

Quote

To escape table names and column names that clash with SQL keywords, enclose the name between two grave accent marks `` (ASCII value 96).

The offending word, in this case, is Order.  From now on, I'll wrap all table and field names in grave accents just to reduce the chance of this tripping me up again.  The final functioning SQL is this:

sSQL = "INSERT INTO `ComboBox` (`Property`, `Order`, `Value`, `Text`) VALUES ('Location', 1123, '5', 'TestText') TEMPORARY"