11/30/2024 3:35:28 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss usage & tips for SalesLogix controls and other 3rd party ActiveX controls. View the code of conduct for posting guidelines.
|
|
|
|
DataGrids: how to set from a RecordSet?
Posted: 21 Mar 08 10:49 AM
|
I know how to get new datagrid data using
grid.SQL.TEXT =
grid.REFRESH
But let's say I have a valid ADO record Set RS1
How do I slam that record set INTO the Grid as a Block (shades of FORTRAN) and Display it?
Set Grid.RecordSet = RS1
grid.REFRESH
?
Invokes the OLD SQL statement///SQL.TEXT string.....
|
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 23 Mar 08 12:26 AM
|
Opportunity Products builds a recordset then sets it to the grid. I'm not exactly sure how it handles a refresh but I would think it doesn't rebuild the columns then requery the data by hand. With my customization I have to select all rows, .delete them from the recordset, and call .CancelBatch so that I can just requery the data and keep the column definitions in tact. I don't do the "disconnect" trick though as I didn't really see the need to disconnect when my customization basically filters from larger datasets into smaller ones based on control data. I would be reconnecting to requery the data way too much.
The problem with my technique is I loop through every record and call SelectRecordForGrid which visibly selects the row and then calls delete. It's looks absolutely gorgeous on large recordsets  so I try to filter my data a lot until I can find a better technique. I'm open for suggestions as I believe RecordSet.Delete adAffected has to be a single record for some reason or my massive amounts of user error couldn't produce a ".Delete everything and start over, please. Enclosing pony." |
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 31 Mar 08 8:35 AM
|
This will change the existing grid rs to another one by casting it to an ado rs and manipulating that and setting it back
Sub DeleteFundfromGrid( ByVal strFundID)
Dim objRS
If strFundID <> "" Then
If viewMode = "Add" Then
If Not dgDistDetFund.Recordset Is Nothing Then
Set objRS = dgDistDetFund.Recordset.Clone
With objRS
Do While Not .Eof Or .Bof
If .fields("fundid").value = strfundid Then
.Delete
.Update
Exit Do
End If
.MoveNext
Loop
End With
set dgDistDetFund.Recordset = objRS
End If
dgDistDetFund.refresh
Else 'Edit Mode
End If
Else
MsgBox "No Fund selected to delete" , vbExclamation, "Notice"
End If 'Selection Count
End Sub
|
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 31 Mar 08 8:49 AM
|
Thanks!
Your method gives me an "Operation not allowed in this context" when I try to
SET grd.RECORDSET = RS1
1. What is the grid.SQL.TEXT set to, nothing?
2. Do you build the columns by hand?
3. What is the ActiveConnection set to, nothing?
I don't want to update any data...... |
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 31 Mar 08 10:11 AM
|
Only the height/width is specified for the grid. Nothing else. Everything is done on the fly. Columns are added by adding to the columns collection.
Active connection is nothing at that point.
Thow an exception at that point and check the rs properties.
You can also make this work with a design time dg i beleive.Just that you have to set the right properties. can you show the code pls?
Also is RS1 a clone of grid.rs?
|
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 02 Apr 08 10:05 AM
|
rs1 IS NOT a clone.....it's its own SQL derived RecordSet from a Select Statement tied into a User Defined Function..... and all of this came about because SLX Provider SQL couldn't handle 'complex' UNION selects.
SELECT A1.ACCOUNTID FROM sysdba.ACCOUNT A1 Inner Join sysdba.udf_GetRecursiveChildren('A6UJ9A000J48' ) rc on a1.accountid = rc.childid WHERE A1.TYPE IN('School - Public', 'School - Private', 'School - Catholic', 'School - Charter', 'School - Alternative', 'School - County', 'School - State' ) ORDER BY a1.ACCOUNT
This gives me the AccountID's I need for the Grid Conditions......
If I simply expanded the SQL statement out it would give me the correct RecordSet:
SELECT DISTINCT A1.ACCOUNTID, A1.ACCOUNT, ISNULL(A2.CITY,'') A2_CITY, ISNULL(A2.STATE,'') A2_STATE, ISNULL(A2.COUNTY,'') A2_COUNTY, A1.TYPE, ISNULL(A1.LOW_GRADE,'') LOW_GRADE, ISNULL(A1.HIGH_GRADE,'') HIGH_GRADE, ISNULL(A1.ENROLLMENT,0) ENROLLMENT, ISNULL(A3.ACCOUNT,'') A3_ACCOUNT, ISNULL(A2.ADDRESSID,'') A2_ADDRESSID, A1.ACCOUNT_UC, ISNULL(A4.QUANTITY,0) A4_QUANTITY, A1.PID, A1.ACCOUNTID, A1.SAP_CUSTOMER_NO FROM ACCOUNT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) LEFT OUTER JOIN ACCOUNT A3 ON (A1.PARENTID=A3.ACCOUNTID) LEFT OUTER JOIN OPPPRODUCT_SITE A4 ON (A1.AccountID=A4.ACCOUNTID) AND A4.OPPPRODUCTID = 'Q6UJ9A02IQUT' WHERE A2.CITY = 'Madison' AND A2.STATE = 'WI' AND A1.TYPE IN('School - Public', 'School - Private', 'School - Catholic', 'School - Charter', 'School - Alternative', 'School - County', 'School - State' ) ORDER BY A1.ACCOUNT_UC ASC
(So if you Inner Join the above mess to the first statement, or have a Massive A1.ACCOUNTID IN() clause you get the correct answer for the SLX Grid.)
So it might be Active Connection......or the Columns.....
|
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 02 Apr 08 2:37 PM
|
Hey RJ, if you need to use union in the select stament try using the SQLOLEDB provider instead of the SLX OLEDB provider. I use this alot when reading data to be displayed in slx grids. Also is there any columns in the grid before you set the grid's recordset property or after you set this property?
|
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 02 Apr 08 3:51 PM
|
Actually I was trying to have columns in the grid before hand to have something for the data to work with/format, etc.
Should I clear it all out and then slam the recordset into the Grid???
Where do the captions and formatting come from.....read only? et al.
We have a SQL Native Connection always available for use for the complex unions....I can get a legitimate unioned data set that is exactly what I want.....but can't seem to put it into the GRID?!
It's something simple.....
SQL.TEXT
ActiveConnection
KEYFIELD
Columns
etc. |
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 02 Apr 08 4:05 PM
|
Actually I was trying to have columns in the grid before hand to have something for the data to work with/format, etc.
Should I clear it all out and then slam the recordset into the Grid???
Where do the captions and formatting come from.....read only? et al.
We have a SQL Native Connection always available for use for the complex unions....I can get a legitimate unioned data set that is exactly what I want.....but can't seem to put it into the GRID?!
It's something simple.....
SQL.TEXT
ActiveConnection
KEYFIELD
Columns
etc. |
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 02 Apr 08 4:06 PM
|
on form open I build my query (sql with unions)
'open and create the recordset object
set rsSource = createObject("Adodb.Recordset")
set OBJConn = createobject("Adodb.Connection")
Connstr = "Provider=SQLOLEDB.1 assword = masterkeyersist Security Info=True;User ID=sysdba;Initial Catalog=" & DBName & ";Data Source=" & ServName & ""
grd_TargetSegment.ConnectionString = "Provider=SQLOLEDB.1assword = masterkeyersist Security Info=True;User
ID=sysdba;Initial Catalog=" & DBName & ";Data Source=" & ServName & ""
gridname.SQL = mySQL
ObjConn.open ConnStr
rsSource.open mySQL, ObjConn, 3, 1
'I then in this same function create my recordset object then do the following loop
'after the recordset hasbeen opened
For Each fld In rsSource.Fields
Set col = gridname.Columns.Add(0)
col.FieldName = fld.Name
col.Caption = fld.Name or a text string "LastName"
col.width = Len(fld.name) or a number value
Next
gridname.Refresh
rsSource.Close
set Objconn = nothing
Set rsSource = nothing
Then the onchange event of this same form I do the following
'this allows you to rebuild just the sql statement as none of the other properties of the datagrid need to be change (columns, width, ect)
grd_TargetSegment.sql = mySQL
Let me know how it goes.
|
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 04 Apr 08 11:52 AM
|
Ok so you are NEVER setting the Grid.Recordset = an ADORecordSet.....
you are using .SQL.TEXT to drive this.....
SalesLogix is unable to PARSE this SQL into the data grid, and yes it works in SQL Query Analyzer.....and with a native connection.
SELECT A1.ACCOUNTID, A1.ACCOUNT, A1.TYPE, A1.PID,A1.HIGH_GRADE, A1.LOW_GRADE, A1.ENROLLMENT, A2.CITY A2_CITY, A2.STATE A2_STATE, A2.POSTALCODE A2_POSTALCODE, A2.COUNTY A2_COUNTY, A1.MAINPHONE
FROM ACCOUNT A1 INNER JOIN ADDRESS A2 ON (A2.ADDRESSID = A1.ADDRESSID)
Inner Join sysdba.udf_GetRecursiveChildren('A6UJ9A000HCA' ) rc on a1.accountid = rc.childid
WHERE A1.HIERARCHY_LEVEL_CODE = 'School'
IF the above is set to a variable: gridSQL
and you do a
grd.SQL.TEXT = gridSQL
grd.REFRESH
I get a Failure to Parse SQL error......
So there is no way to set a Grid.RecordSet = to a RecordSet?
SET grid.RecordSet = rs1 DOES NOT WORK, nor does
grid.RecordSet = rs1
Nor does the above SQL statement.....
|
|
|
| |
| |
|
Re: DataGrids: how to set from a RecordSet?
Posted: 08 Apr 08 5:59 PM
|
sorry for this late reply. I do not know where your with this now..
Setting a grid thru sql and getting it a different from creating a std ado rs and then slamming that into a well formed SLX Datagrid control. I normally play with the rs obj and never use sql text. Here the problem is with the parsing of the sql. I see that your using table returning UDFs in your query .That may not be supported by the provider to begin with. I know i have used simple UDFs in a non-sync slx environement and it works! First make sure that your sql works and then we will deal withe the display later.
SQL statement is the problem.......If you explain what your trying to do , maybe there is a slx complaint method out there we can use. I have gotten away with those stupid techniques many times while doing slx customs. |
|
|
|
Re: DataGrids: how to set from a RecordSet?
Posted: 08 Apr 08 6:02 PM
|
Hi Derek , For some reason i thought slx controls will be inheriting all data connectivity from the client connection details unless we create a specific connection object with a connection string we want. Cool Info.
Ya so if that ssql works with that , it should work with grid also..
Also have you done customs like these ? In slx , there are many things that are exposed, but NOT implemented. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|