cancel
Showing results for 
Search instead for 
Did you mean: 

How to insert data in DB with DBDataSource ?

Former Member
0 Kudos

I want to save data entered in form to my table in DataBase.

I have used both

<b>Recordset for text fields and</b>

<u>DBDataSource for matrix</u>

i dont know method how to save data form front end to back end via Recordset and DBDataSource.

if possible Plz. provide some sample code.

Regards,

Ganesh

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Ganesh,

Sorry to tell you that it is impossible to update database with DBDataSource. You should use DI API to update DB.

Please kindly notice that UI API can only allow you to get data from database, and if you want to update, you should use DI API.

And in your case, you'd better update data through specific object.

Hope this helps,

Nick

Former Member
0 Kudos

Thanks for reply,

Its right that i can`t use UI-API object to save data in DB.

But code i used to fill matrix is like this

<i>oColumn = oColumns.Item("DSCardCode")

oColumn.DataBind.SetBound(True, "OITM", "ITEMCODE")

</i>

<b>

Now i want to copy data from matrix to DB.

What should be my method ?

if i need to use recordset how should i use it ?

</b>

My problem is to catch data form matrix and punch it in DB.

Regards,

Ganesh

Former Member
0 Kudos

Hello Ganesh,

Sorry for my misunderstanding.

If you want to copy ITEM data to DB, as indicated in your code, you should use Item object in DI API. One suggestion is to get data from DBDatasource, and then set to item object to update.

Sorry to tell you that it is not allow to update system table with Recordset directly.

Thanks for your understanding.

Hope this helps,

Nick

Former Member
0 Kudos

GOD_Form = GOD_Application.Forms.Item("SBO_17")

db1 = GOD_Form.DataSources.DBDataSources.Item("@PRACTICEREQROW")

Dim rec As SAPbobsCOM.Recordset

rec = GOD_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

rec.DoQuery("select itemcode,itemname,Onhand, AvgPrice from oitm where itemcode='" & val & "'")

If pVal.Row = mat.VisualRowCount Then

db1.Offset = db1.Size - 1

db1.SetValue("U_ItemCode", db1.Offset, "")

db1.SetValue("U_IDesc", db1.Offset, rec.Fields.Item(1).Value)

db1.SetValue("U_Onhand", db1.Offset, rec.Fields.Item(2).Value)

db1.SetValue("U_Price", db1.Offset, rec.Fields.Item(3).Value)

mat.SetLineData(pVal.Row)

mat.FlushToDataSource()

End If

db1.SetValue("U_ItemCode", db1.Offset, datatable.GetValue(0, 0))

db1.SetValue("U_IDesc", db1.Offset, rec.Fields.Item(1).Value)

db1.SetValue("U_Onhand", db1.Offset, rec.Fields.Item(2).Value)

db1.SetValue("U_Price", db1.Offset, rec.Fields.Item(3).Value)

mat.SetLineData(pVal.Row)

mat.FlushToDataSource()

mat.LoadFromDataSource()

db1.setvalue this will set the value to dbdatasource

mat.flushtoDatasource this will update ur dbdatasource from matrix

Hope this code will help u

Answers (1)

Answers (1)

Former Member

Hi

Use this code to save changes in your matrix on database.

Hope I help you.

BR

Sierdna


Private oDBDataSource As SAPbouiCOM.DBDataSource

Public Sub CreateForm(...)

  '...
  oDBDataSource = oForm.DataSources.DBDataSources.Item("@USER_TABLE")
  '...
  ' bind matrix columns to database table attributes.
  '...
End Sub

Private Function MatrixAddOrUpdate(ByRef oForm As SAPbouiCOM.Form) As Boolean

  Dim b As Boolean = True
  Try
      If SBO_Company.Connected Then
	  oForm.Freeze(True)

	  Dim oMatrix As SAPbouiCOM.Matrix
	  oMatrix = oForm.Items.Item(MatrixID).Specific

	  If oMatrix Is Nothing Then Throw New Exception("ERROR: matrix object is nothing")

	  oMatrix.FlushToDataSource()

	  Dim i As Integer = 0
	  For i = 0 To oDBDataSource.Size - 1

	      Try
		  Dim sCode As String = oDBDataSource.GetValue("Code", i).Trim
		  Dim sName As String = oDBDataSource.GetValue("Name", i).Trim
		  Dim sU_Field As String = oDBDataSource.GetValue("U_Field", i).Trim
		  ' add other fields...

		  Dim oUserTable As SAPbobsCOM.UserTable
		  oUserTable = SBO_Company.UserTables.Item("USER_TABLE")
		  Dim iRet As Integer = 0
		  Try
		    ' Transaction Begin
		    SBO_Company.StartTransaction()
		    If oUserTable.GetByKey(sCode) Then

		      ' UPDATE
		      oUserTable.Name = sName
		      oUserTable.UserFields.Fields.Item("U_Field").Value = sU_Field
		      ' add other fields...

		      iRet = oUserTable.Update()

		    Else

		      ' ADD
		      oUserTable.Code = sCode
		      oUserTable.Name = sName
		      oUserTable.UserFields.Fields.Item("U_Field").Value = sU_Field
		      ' add other fields...

		      iRet = oUserTable.Add()

		    End If
		  Catch ex As Exception
		    ' Transaction Rollback
		    If SBO_Company.InTransaction Then SBO_Company.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
		    ' log exception
		  Finally
		    ' Transaction End
		    If SBO_Company.InTransaction Then SBO_Company.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
		    oUserTable = Nothing
		  End Try
	      Catch ex As Exception
		' log exception
	      End Try
	  Next

	  oDBDataSource.Query()
	  oMatrix.LoadFromDataSource()
	
	  oMatrix = Nothing

      End If
  Catch ex As Exception
    b = False
    ' log exception
  Finally
      oForm.Freeze(False)
      System.GC.Collect() 'Release the handle to the table   
  End Try

  Return b

End Function