DBDataSource with Conditions doesn't filter

[Former Member]

Hi,

I have created a custom table using SQL (instead of User Table)

I am able to retrieve the data from the table without a problem. However, Condition Object does not filter,my codes as below

oCondition = oConditions.Add

oCondition.Alias = "ItemCode"

oCondition.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

oCondition.CondVal = itemcode

Does the Condition object work on custom table?

LS

[former_member184566 · Accepted solution]

Hi,

Before your "oCondition = oConditions" you should have "oConditions = New SAPbouiCOM.Conditions" also.

Also more down you should have "oDBDatasource.Query(oConditions)". And then do a loadfromdatasource.

Your columns should also have been binded....databind.

Hope this helps

[Former Member · Accepted solution]

I am getting error below when i have added oConditions as what you have mentioned.

"Exception from HRESULT: 0xFFFFFFFF"

[former_member184566]

That is a strange error. I usualy get that when it doesn't connect to the db properly through single sign on.

What are you trying to do? i think the "loadfromdatasource" functionality only works on sap tables. Not even user defined tables will it work on. Try this code, it works on OCRD table(SAP table), it works perfect. Works with a form that has a matrix with two columns. Once you got it working then change to see if it works with your table. But from experience i dont think loadfromdatasource will work on non sap tables.

Let me know

Dim oConditions As SAPbouiCOM.Conditions

Dim oCondition As SAPbouiCOM.Condition

Dim oItem As SAPbouiCOM.Item

Dim oMatrix As SAPbouiCOM.Matrix

Dim oColumns As SAPbouiCOM.Columns

Dim oColumn As SAPbouiCOM.Column

str += "%"

oConditions = New SAPbouiCOM.Conditions

oCondition = oConditions.Add

oCondition.Alias = "CardCode"

oCondition.Operation = SAPbouiCOM.BoConditionOperation.co_GRATER_THAN

oCondition.CondVal = str

oItem = SBO_Application.Forms.Item("Choose").Items.Item("Mat")

oMatrix = oItem.Specific

oColumns = oMatrix.Columns

oColumn = oColumns.Item("BPCode")

oColumn.DataBind.SetBound(True, "OCRD", "CardCode")

oColumn = oColumns.Item("BPName")

oColumn.DataBind.SetBound(True, "OCRD", "CardName")

oDBDatasource.Query(oConditions)

oMatrix.LoadFromDataSource()

[Former Member]

I am using the loadfromdatasource on a User Table(Created outside SBO).

I didn't know that loadfromdatasource doesn't work on User Table.

Actually what i am trying to do is to create a table which can store duplicated values , which the User table doesn't allow (because of default fields, Code and Name)

What i have done is create a table in SQL instead, and insert a line in OUTB table.

is there a way to generate running number in Code(similar to Identity Seed in SQL) which i think might solve my problem

[former_member184566]

Hi, me again

Did you try the code, did it work. Even if you use a user defined table in sap i dont think it will do the "loadfromdatasource", but if it does. Let me know.

About the code and name, it's stored as text.So i usualy store a number there. But once a number is text it does not have the same ordering. So what i do is i use a query that looks at that column, converts the value to integer and then finds the max and adds one, if max is NULL then make it 1. Look below. Use recordset to run the query. Hope this helps. If you come up with a better way let me know.

select convert(varchar,isnull(max(convert(integer,Code)+1),1)) as UniqNext from [@User_Table_Name]

[former_member184566]

The value returned by the query is a unique value, bacause it's a counter. Starts at one and just goes up and up. So use "UniqNext" as your value for code and name.

[Former Member]

loadfromdatasource does work on User Define tables.

what my problem is that i manually created the fields in SQL. I did not prefix the fields with "U_" earlier, which cause the problem.

Problem fixed!

[Former Member]

How do you use "UniqNext"? Care to explain. seems very useful

[former_member184566]

Okay, i'll have to give it a try on the user defined table. Never tried, just heard from some one that it won't work.

Simple,thought you would understand. All the magic is in the query.

This will populate RS with the next unique value

Dim RS_Date As SAPbobsCOM.Recordset = Nothing

RS = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

RS.DoQuery("select convert(varchar,isnull(max(convert(integer,Code)+1),1)) as UniqNext from [@SM_ORER]")

Then when adding to the user defined table the code and name can be assigned like this. Do you know how to add to a user defined field within sbo? Im sure you do.

oUserTable.Code = RS.Fields.Item("UniqNext").Value

oUserTable.Name = RS.Fields.Item("UniqNext").Value

Is this helpful?

[Former Member]

This is very helpful!

Thanks!

[former_member184566]

Oh Ya,

i'd also suggest making the table within SBO,could solve the problems if you have done the above. why didn't you in the first place?