cancel
Showing results for 
Search instead for 
Did you mean: 

DI-Connection to another DB

Former Member
0 Kudos

Hellow,

Does any one know if can I Connect To my DB -Not the Menahel DB On Sdk form that I'v made?

Iwant to do Query of Data From my Db and I don't want to copy my data to User Tables On Menahel DB.

Can I do it?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Ruti

You can use any SQL you want with Recordset object.

For matrix:

1. Open UserDataSource for each column in the matrix

2. Bind column with data source

3 Read row from Recordset and set value of every UserDataSource for matrix

4. AddRow()

It works, but slower than using of DBDataSource

For text box use the same way with single UserDataSource

Former Member
0 Kudos

Hi Mark,

Do you mean that I can use the Recordset object of V.B(not the SBO Recordset)

and use this recordset to the SBO objects? how?

or you mean that with the object recordset of SBO

I can use for every external DB that I want?

Do you have an example or block of code?

can I use it only for query or also for update?

thank you very much.

Ruti

0 Kudos

You could use an ADO recordset to access the data, or you could probably use a Business One recordset object as Mark has suggested.

Either way, it sounds like your primary question really doesn't have to do with how you access the data, but rather, how you work with that data on a Business One form.

There are 2 kinds of data sources in Business One:

A DBDataSource pulls data directly from the BusinessOne database. (Note that it does not INSERT or UPDATE records, though... you need to use objects for that.) As far as I know, a DBDataSource cannot be used to access data in an external database. A DBDataSource holds tabular data; in other words, it holds data that consists of rows and columns... it is two-dimensional.

A UserDataSource is something like a form-level variable that holds a single value. It can hold whatever value you assign to it. A UserDataSource holds a single value; ... it is one-dimensional.

Because DBDataSources are not designed to pull data from a table outside of Business One, you would need to:

1. Bind a separate UserDataSource to each column in your matrix.

2. Read data from your table (in an external database) using ADO or DI-API recordset... doesn't really matter.

3. For the first record in your recordset, set the value of the UserDataSource that corresponds to each column in the matrix.

4. Call SetLineData to populate a row in the matrix with data from your UserDataSources.

5. Go to the next record in your recordset, repeat steps 3 and 4 until finished with your recordset.

6. If you need to pull data out of the matrix & write it back to your table, use the GetLineData method to populate your UserDataSources with values from a particular row in the matrix. Then update your table (using ADO for example, or an API if your external application has one). Again, you would need to repeat this process for every row in the matrix.

Former Member
0 Kudos

Thank You, James and Mark.

You write that I can use also the Recordset of SBO. How?

I know that this kind of recordset let me Sql sentences

from the DB of the company. no?

Ex.

Set RecSet = oCompany.GetBusinessObject(BoRecordset)

RecSet.DoQuery "select CardCode, CardName from CRD"

Maybe I don't realy know how to do that.

I just now have to start work with the sdk.

so I explain my needs again:

I need 2 things:

1.)I will have a meeting with customer today and I have to know if I can Query,Update,Insert Data from extenal DB

and intagrate it with the SBO form and its'object.

I understand from your answers that it possibly.

2.)The next step- I should have to do that.

If you can send me Example of Code it will help me

For my first steps with this type of binding.

thanks

Ruti

Former Member
0 Kudos

Hi Ruti

This is a simple code for 6.2

oForm = SBO_Application.Forms.Add("ZefiForm", SAPbouiCOM.BoFormTypes.ft_Fixed)

oForm.DataSources.UserDataSources.Add("UsDSource1", SAPbouiCOM.BoDataType.dt_SUM, 10)

oForm.DataSources.UserDataSources.Add("UsDSource2", SAPbouiCOM.BoDataType.dt_SUM, 10)

frmItem = oForm.Items.Add("mtxTable", SAPbouiCOM.BoFormItemTypes.it_MATRIX)

frmItem.Left = 5

frmItem.Top = 28

frmItem.Height = 120

frmItem.Width = 660

frmItem.Enabled = True

oMatrix = frmItem.Specific

oColumns = oMatrix.Columns

oColumn = oColumns.Add("Fld1", SAPbouiCOM.BoFormItemTypes.it_EDIT)

oColumn.TitleObject.Caption = "xxx"

oColumn.Width = 30

oColumn.Editable = False

oColumn.DataBind.SetBound(True, "", "UsDSource1")

oColumn = oColumns.Add("Fld2", SAPbouiCOM.BoFormItemTypes.it_EDIT)

oColumn.TitleObject.Caption = "yyy"

oColumn.Width = 50

oColumn.Editable = True

oColumn.DataBind.SetBound(True, "", "UsDSource2")

Dim DBSource As SAPbobsCOM.Recordset

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

DBSource.DoQuery("SELECT * FROM dbo.DBName.MyTable)

oMatrix.Clear()

With oForm.DataSources.UserDataSources

If DBSource.RecordCount <> 0 Then

DBSource.MoveFirst()

Do Until DBSource.EoF

.Item("UsDSource1").Value = DBSource.Fields.Item("TblFld1").Value

.Item("UsDSource2").Value = DBSource.Fields.Item("TblFld2").Value

oMatrix.AddRow()

DBSource.MoveNext()

Loop

End If

End With

Now you've got a matrix filled with data from table from any database(see DBName in SQL statement) In 6.5 you can use SetLineData to update matrix row from bounded data sources

Updating data is more difficult task. In 6.2 I store updated data in special structures with the row key and after updating is over I update source table. In 6.5 you can use GetLineDta to return data from matrix row to bounded data sources and update table using them.

Former Member
0 Kudos

Thank you Mark.

I'll have to do the things the next days.

I hope I'll sucseed.

Thank youvery much again

Ruti

Former Member
0 Kudos

Thank you Mark.

I'll have to do the things the next days.

I hope I'll sucseed.

Thank youvery much again

Ruti

Former Member
0 Kudos

You can write database specification in the SQL statement.

For example:

SELECT * FROM dbo.mydb.mytable

Former Member
0 Kudos

Thank you Mark for your answer,

I want to know how can I work with it with the SBO,

I mean,how should I integrate it with the SBO form object

and its objects like edit field, matrix and ect.

Thanks very much

Ruti

Former Member
0 Kudos

Hi Ruti

Actually, you can use any kind of recordset for reading data from database, but if you want to update data it's better to use recordset of DI and even DataBrowser. Block of code i'll send you tomorrow