on 12-12-2006 11:09 AM
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
thanx for the help i'll try the code which you wrote
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
i'm working on sb1 2004 version. i didn't find the UserQuery object which you mentioned are you working on sb1 2005 version?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.