cancel
Showing results for 
Search instead for 
Did you mean: 

How to create a query and run it through code?

Former Member
0 Kudos

Hi all

I like to know if there is a way to create a query the same as the one created with the query manager and run it through code to recieve a form with the results within a table

appreciate the help

Yoav

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

thanx for the help i'll try the code which you wrote

Former Member
0 Kudos

i'm working on sb1 2004 version. i didn't find the UserQuery object which you mentioned are you working on sb1 2005 version?

Former Member
0 Kudos

Hi Yechiel,

The userquery is an object available for 2005 version. In 2004 you can't create the query via DIAPI but you can execute it with UIAPI.

Regards,

Vanesa

Former Member
0 Kudos

In 2004 you can use UIAPI to create Queries.

This piece is a bit extreme, but it has served me quite well:

			If strQueryCategoryName <> "" And strQueryName <> "" Then

				' IF Query Category doesn't exist THEN add Query Category
				strSQL = "SELECT * FROM OQCN WHERE CatName = 'CATEGORY_NAME'"
				strSQL = Replace(strSQL, "CATEGORY_NAME", strQueryCategoryName)
				rsRecSet.DoQuery(strSQL)
				If rsRecSet.RecordCount < 1 Then
					With qcQueryCategory
						.Name = strQueryCategoryName
						.Add()
					End With
				End If

				' IF Query doesn't exist THEN add Query 
				' TODO: JOIN UQCN
				strSQL = "SELECT * FROM OUQR WHERE QName = 'QUERY_NAME'"
				strSQL = Replace(strSQL, "QUERY_NAME", strQueryName)
				rsRecSet.DoQuery(strSQL)
				If rsRecSet.RecordCount < 1 Then

					''''' Query Generator
					''''frm = GetThisForm("4102", "115")
					''''frm.Items.Item("8").Specific.String = "NOW I WILL RAISE AN ERROR INTENTIONALLY!"
					''''frm.Items.Item("1").Click()
					''''SBOApplication.StatusBar.SetText(System.Windows.Forms.Application.ProductName & "   : Error cleared.", SAPbouiCOM_BoMessageTime_bmt_Short, SAPbouiCOM_BoStatusBarMessageType_smt_Success)
					''''frm.Close()

					'Opens and get by the Query Generator
					' Query(Generator)
					frm = GetThisForm(B1MENU_QUERY_GENERATOR, B1FORM_QUERY_GENERATOR)
					frm.Left = 2000

					frm.Items.Item("8").Specific.String = "''"

					SwitchWindowTo(gstrSBOApplication_Desktop_Title, "")
					'DELE, jos seuraava OK: Cursor.Position = New System.Drawing.Point(frmMDI.Left - 1, Cursor.Position.Y)
					Cursor.Position = New System.Drawing.Point(1, 1)
					SBOApplication.StatusBar.SetText(System.Windows.Forms.Application.ProductName & ": A click on 'OK' is needed in the case that the 'System Message' window does not get closed by itself in 30 seconds.", SAPbouiCOM_BoMessageTime_bmt_Long, SAPbouiCOM_BoStatusBarMessageType_smt_Warning)
					frm.Select()
					System.Threading.Thread.Sleep(500)
					SendKeys.Send("{LEFT 100}{ESC}")					 'Fill the keyboard buffer with harmless keystrokes + ESC to clear the 'System Message' window
					frm.Items.Item("1").Click()					  ' Execute

					SBOApplication.StatusBar.SetText(System.Windows.Forms.Application.ProductName & ": Thanks", SAPbouiCOM_BoMessageTime_bmt_Short, SAPbouiCOM_BoStatusBarMessageType_smt_Warning)

					Call SBOFormCenter(frm)
					frm.Close()

					'Queries
					frm = SBOApplication.Forms.GetFormByTypeAndCount("110", 1)
					frm.Items.Item("15").Click()					  ' Edit button
					frm.Items.Item("3").Click()					  ' Goto Edit field
					frm.Items.Item("3").Specific.String = "  "
					frm.Items.Item("3").Specific.String = strQuerySQL					  ' SQL

					' SBO bug: sometimes we get "SELECT SELECT"
					strQuerySQL = frm.Items.Item("3").Specific.String
					If InStr("SELECT SELECT", strQuerySQL) Then
						strQuerySQL = strQuerySQL.Replace("SELECT SELECT", "SELECT")
						frm.Items.Item("3").Specific.String = strQuerySQL						 ' SQL 2nd attempt
					End If

					frm.Items.Item("3").Specific.String.ToString.Replace("SELECT SELECT", "SELECT")

					frm.Items.Item("5").Click()					  ' Save

					'Save Query
					frm2 = SBOApplication.Forms.GetFormByTypeAndCount("957", 1)
					frm2.Items.Item("8").Specific.string = strQueryName
					frm2.Items.Item("20").Specific.string = strQueryCategoryName
					frm2.Items.Item("1").Click()

					frm.Items.Item("2").Click()					  'Close Queries form

				End If

HTH

Juha

Former Member
0 Kudos

Yes, you can create queries using UIAPI or DIAPI.

And yes, you can launch the User Query with UIAPI.

HTH

Juha

Creating User Queries with DIAPI is something like this:

		Dim lRetVal As Long
		Dim sboQryCategory As SAPbobsCOM.QueryCategories
		sboQryCategory = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oQueryCategories)

		Dim strSQL As String
		Dim lngQryCategoryCode As Long

		strSQL = "SELECT CategoryId FROM OQCN WHERE CatName = '_NAME_' "
		strSQL = Replace(strSQL, "_NAME_", "YOUR_CATEGORY")
		lngQryCategoryCode = SBOGetSingleValueWithSQL(strSQL)

		If lngQryCategoryCode < 1 Then
			With sboQryCategory
				.Name = "YOUR_CATEGORY"
				lRetVal = .Add()
				If lRetVal <> 0 Then MsgBox("Could not add Query Category: " & .Name & "  " & SBOGetError())
			End With
		End If
		strSQL = "SELECT CategoryId FROM OQCN WHERE CatName = '_NAME_' "
		strSQL = Replace(strSQL, "_NAME_", "YOUR_CATEGORY")
		lngQryCategoryCode = SBOGetSingleValueWithSQL(strSQL)

		Dim sboUserQuery As SAPbobsCOM.UserQueries
		sboUserQuery = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserQueries)

		With sboUserQuery
			.QueryCategory = lngQryCategoryCode
		End With

		With sboUserQuery
			.QueryDescription = "Query: Business Partners"
			.Query = "SELECT * FROM OCRD"
			lRetVal = .Add()
			If lRetVal <> 0 Then MsgBox("Could not add Query : " & .QueryDescription & "  " & SBOGetError())
		End With

And here you'll find a description on how to kick off User Queries:

Message was edited by:

Juha Lassila

Message was edited by:

Juha Lassila