x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 4 replies
  • Subscribers 14 subscribers
  • Views 856 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
murni

Error when query table from custom database

Posted By

Hi all,

I have a custom database(not company database) to store information to be retrieve by all companies in a custom form. I am able to query its table using plain query in MSSQL Management Studio.

With Sage 200, I am able to make a connection to the database using Sage.ObjectStore.ConnectionData. I make persistent object class for the tables in custom database using Sage 200 ObjectStore Builder.

But not able to add or select from its table due to "Sage.ObjectStore.DatabaseException: Counter Table is missing or corrupt ---> Sage.ObjectStore.MissingTableException: Counter Table is missing or corrupt". 

Full error as below. 

Sage.ObjectStore.DatabaseException: Counter Table is missing or corrupt ---> Sage.ObjectStore.MissingTableException: Counter Table is missing or corrupt
   at Sage.ObjectStore.Counter.GetPage(Int32 pageSize)
   at Sage.ObjectStore.PrimaryKeyGenerator.get_NextPrimaryKey()
   at Sage.ObjectStore.PersistentObject.GenerateNextPrimaryKey()
   at Sage.ObjectStore.Database.GetInsertCommand(IDbCommand dbCommand, List`1 parameters, IDatabaseObject src, List`1 fields, String tableName, Field primaryKeyField)
   at Sage.ObjectStore.Database.Insert(IDatabaseObject src, List`1 fields, String tableName, Field primaryKeyField)
   --- End of inner exception stack trace ---
   at Sage.Common.Exceptions.ExceptionManager.Throw(Exception e)
   at Sage.ObjectStore.Database.Insert(IDatabaseObject src, List`1 fields, String tableName, Field primaryKeyField)
   at Sage.ObjectStore.Database.Insert(IDatabaseObject src)
   at Sage.ObjectStore.PersistentObject.AddNewPrivate()
   at Sage.ObjectStore.PersistentObject.AddNewInternal()
   at Sage.ObjectStore.PersistentObject.AddNew()
   at Sage.ObjectStore.PersistentObject.Add()

Is Sage.ObjectStore.PersistentObject can only be use if we are in the company database?

  • 0

    Interesting, i have never thought to try this,  

    To get over this issue, all you would need to do is add a table called "Counter" to your custom database. find the counter table in a sage 200 database, right click and choose "Script table as " the "Create to" then "new query window"

    remove the "Use" statement at the top, and then run the script against you custom table.

    this will resolve the missing table issue, whether or not it will make it work? i would be interested to know

  • 0

    Another possibility is to set your own primary key (if you have some way of generating them), This can be done by defining the primary key like this:

        Private _MyTableDbKey as Field
<Builder.MetaDataField(ColumnName:="MyTableID", IsPrimaryKey:=True, IsIndexed:=True, IsUnique:=True, DbType:=DbType.Int64)>
Property MyTableDbKey As Sage.Common.Data.DbKey
Get
    Return _MyTableDbKey.GetDbKey
End Get
Set
    _MyTableDbKey.Value = Value
End Set
End Property

    then overriding UpdateInternal:

    Protected Overrides Sub UpdateInternal()
	'allow primary key to be set
	If Not Me.IsNew OrElse Me.PrimaryKey.IsNull Then
		MyBase.UpdateInternal()
	Else 'add new record with specific primary key
		Using tran As New PersistentObjectTransaction(Me)
			InvokeMethod(GetProperty(Me, "Database"), "Insert", Me)
			tran.Commit()
		End Using
	End If
End Sub

    (InvokeMethod and GetProperty are simple reflection routines)

  • 0 in reply to Toby

    Hi Toby, thank you. It works. But I also would like to add: After adding the Counter table to my custom database, I have to insert 1 row with relevant data for the program to pick up the NextValue

  • 0 in reply to Geoff Turner

    Thank you Geoff. I went with Toby's answer because I not very proficient in VB.

*Community Hub is the new name for Sage City