The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Add records to an existing datagrid from a different sql string
Posted: 07 Jan 09 9:57 AM
|
Hi all.
I have a datagrid that I populate using the following code:
Dim strSQL
With Datagrid1
strSQL = "Select * from myTable"
.SQL.Text = strSQL
With .Columns
If (.Count > 0) Then
For i = 0 To .Count - 1
.Item(0).Delete
Next
End If
End With
.Refresh
End With
I want to be able to also add some records from a completely different sql string, for example,
strSQL1 = "Select * from myTable2"
and have that data also included in the datagrid. I just don't know what the correct code is to do that? Is it .SQL.Text.Add ? or .SQL.Text.Append?
Thank you in advance!
|
|
|
| |
|
Re: Add records to an existing datagrid from a different sql string
Posted: 07 Jan 09 3:27 PM
|
Hi Timmus. Thanks for the reply. I tried your suggestion and I got an error stating it couldn't find the table: C_Facility_Price_A
strSQL = "Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM C_FACILITY_PRICE_A WHERE ACCOUNTID ='" & application.BasicFunctions.CurrentAccountID & "' UNION Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM C_GPO_IND_PRICING WHERE ACCOUNTID ='" & application.BasicFunctions.CurrentAccountID & "'"
Should I be adding a paranthesis somewhere before or after the UNION portion? |
|
|
| |
|
Re: Add records to an existing datagrid from a different sql string
Posted: 07 Jan 09 3:33 PM
|
Try connecting to a query tool like SQL Management Studio as sysdba and test the following:
Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM C_FACILITY_PRICE_A WHERE ACCOUNTID ='fakeid'
UNION
Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM C_GPO_IND_PRICING WHERE ACCOUNTID ='fakeid'
Does it parse? Does C_Facility_Price_A actually exist and is it owned by sysdba?
Timmus |
|
|
|
Re: Add records to an existing datagrid from a different sql string
Posted: 07 Jan 09 3:47 PM
|
Ok. I typed the following in SQL Query Analyzer in SQL Server Enterprise Manager:
Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM sysdba.C_FACILITY_PRICE_A WHERE ACCOUNTID ='A6UJ9A000E2R'
UNION
Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM sysdba.C_GPO_IND_PRICING WHERE ACCOUNTID ='A6UJ9A000E2R'
And it gave me the desired results.
I then changed my script in the Architect to be the following:
strSQL = "Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM sysdba.C_FACILITY_PRICE_A WHERE ACCOUNTID ='" & application.BasicFunctions.CurrentAccountID & "'"
strSQL = strSQL & " UNION"
strSQL = strSQL & " Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM sysdba.C_GPO_IND_PRICING WHERE ACCOUNTID ='" & application.BasicFunctions.CurrentAccountID & "'"
And I got the same error stating C_Facility_Price_A does not exist etc. |
|
|
| |
| |
|
Re: Add records to an existing datagrid from a different sql string
Posted: 07 Jan 09 3:59 PM
|
But when I run each statement individually, in other words, I comment everthing after the UNION (I do not include the UNION) it shows me the results in the client, when I comment everything before the UNION it gives me the correct results.
I simply want both sets of results to be displayed, I must be using wrong syntax with the UNION? |
|
|
| |
|
Re: Add records to an existing datagrid from a different sql string
Posted: 07 Jan 09 4:14 PM
|
We are using SLX version 7.2.1
I ran the SLXProfiler and this is what I got:
---------- Client SQL ---------
Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM sysdba.C_FACILITY_PRICE_A WHERE ACCOUNTID ='A6UJ9A000E2R' UNION Select Contract, PrintMedia_ItemNumber, Item_Number, UnitsPerCase, Contract_Price_Case FROM sysdba.C_GPO_IND_PRICING WHERE ACCOUNTID ='A6UJ9A000E2R'
---------- Executed SQL ----------
Select C_FACILITY_PRICE_A.Contract, C_FACILITY_PRICE_A.PrintMedia_ItemNumber, C_FACILITY_PRICE_A.Item_Number, C_FACILITY_PRICE_A.UnitsPerCase, C_FACILITY_PRICE_A.Contract_Price_Case FROM sysdba.C_FACILITY_PRICE_A WHERE C_FACILITY_PRICE_A.ACCOUNTID ='A6UJ9A000E2R' UNION Select C_GPO_IND_PRICING.Contract, C_GPO_IND_PRICING.PrintMedia_ItemNumber, C_GPO_IND_PRICING.Item_Number, C_GPO_IND_PRICING.UnitsPerCase, C_GPO_IND_PRICING.Contract_Price_Case FROM sysdba.C_GPO_IND_PRICING WHERE C_FACILITY_PRICE_A.ACCOUNTID ='A6UJ9A000E2R'
The only thing I noticed was that the UNION part did not start in a new line, I don't know if that makes a difference? |
|
|
| |
|
Re: Add records to an existing datagrid from a different sql string
Posted: 07 Jan 09 5:00 PM
|
Ah ha - it appears the parser is auto appending the table name as a prefix in your where clause. Try aliasing your statement like this:
strSQL = "Select A.Contract, A.PrintMedia_ItemNumber, A.Item_Number, A.UnitsPerCase, A.Contract_Price_Case FROM sysdba.C_FACILITY_PRICE_A A WHERE A.ACCOUNTID ='" & application.BasicFunctions.CurrentAccountID & "'"
strSQL = strSQL & " UNION"
strSQL = strSQL & " Select B.Contract, B.PrintMedia_ItemNumber, B.Item_Number, B.UnitsPerCase, B.Contract_Price_Case FROM sysdba.C_GPO_IND_PRICING B WHERE B.ACCOUNTID ='" & application.BasicFunctions.CurrentAccountID & "'"
Timmus |
|
|
| |
| |
| |
| |
|