|
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 ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
How to use 2 datasource on 1 datagrid
Posted: 01 May 08 9:20 PM
|
Hi!
I am student in Indonesia
I want to ask about datagrid, Can I use 2 datasource for 1 datagrid
I am using Visualbasic and access as database
Database(independent) structure Like This :
a)Table 1
tanggal | id_transaksi | msisdn_target | status | jumlah
------------------------------------------------------------------------------
2008-04-01 | 1118810618 | 85652224948 | complete | 10000
b)Table 2
point_of_charging | msisdn | type
---------------------------------------------------------
Balikpapan | 856522abcd | Sat-C
and my code are :
Option Explicit
Dim koneksi As New ADODB.Connection
Dim sql, sql2, sql3, data_count As String
Dim rs, rs2, rs3, rsteks As ADODB.Recordset
Private Sub Form_Load()
Set koneksi = New ADODB.Connection
koneksi.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=True;User ID=; Password=;" & _
"Data Source=" & App.Path & "\independent.mdb"
koneksi.Open
koneksi.CursorLocation = adUseClient
'/////////////////////////////////////////////////////////////////////////////
sql = "select count(*) as count_dt " & _
"from source_msisdn as a, data_independent_in as b " & _
"where left(a.msisdn,6) like left(b.msisdn_target, 6)"
Set rs = koneksi.Execute(sql, , adCmdText)
Me.Text1.Text = rs("count_dt")
Dim strsql As String
Set rsteks = New ADODB.Recordset
strsql = "select a.point_of_charging, a.msisdn, " & _
"b.msisdn_target, b.jumlah " & _
"from source_msisdn as a, data_independent_in as b " & _
"where left(a.msisdn,6) like left(b.msisdn_target,6)"
rsteks.Open strsql, koneksi, adOpenDynamic, adLockOptimistic
Set Me.DataGrid1.DataSource = rsteks
Me.DataGrid1.Refresh
Set rs2 = New ADODB.Recordset
sql2 = "select a.point_of_charging, count(*) as dtq, sum(b.jumlah) as qty " & _
"from source_msisdn as a, data_independent_in as b " & _
"where left(a.msisdn,6) like left(b.msisdn_target,6) " & _
"group by a.point_of_charging " & _
"order by a.point_of_charging"
rs2.Open sql2, koneksi, adOpenDynamic, adLockOptimistic
Set rs3 = New ADODB.Recordset
sql3 = "select sum(jumlah) as qty_all " & _
"from data_independent_in"
rs3.Open sql3, koneksi, adOpenDynamic, adLockOptimistic
Me.Text2.Text = rs3("qty_all")
Set Me.DataGrid2.DataSource = rs2
Me.DataGrid2.Columns(0).Caption = "Cabang"
Me.DataGrid2.Columns(1).Caption = "Transaksi"
Me.DataGrid2.Columns(2).Caption = "Jumlah(Rp)"
Me.DataGrid2.Columns(0).DataField = rs2("point_of_charging")
Me.DataGrid2.Columns(1).DataField = rs2("dtq")
Me.DataGrid2.Columns(2).DataField = rs2("qty")
Me.DataGrid2.Columns.Add (3)
Me.DataGrid2.Columns(3).Caption = "Percentage(%)"
Me.DataGrid2.Refresh
'/////////////////////////////////////////////////////////////////////////////
End Sub
My question is, How if I want to get the value of percentage?
the formula like tHis : rs2("qty")/rs3("qty_all")*100
and I want to put the value of percentage On columns(3), which I was add it
I dont know How to use 2 datasource for one datagrid
aLL many Thanks
 : |
|
|
| |
|
Re: How to use 2 datasource on 1 datagrid
Posted: 02 May 08 10:10 AM
|
| You can't have two datasources for one grid. Your only hope is to either come up with a query that has an aggregate of the data you want to display in the grid or build a recordset in code and fill it with the desired data and then bind it to the grid. |
|
|
| |
|
Re: How to use 2 datasource on 1 datagrid
Posted: 06 May 08 7:14 PM
|
Originally posted by cloud
maybe do you have any suggestion or answer for my problem? |
|
Uh, that *is* the answer to your problem. You have to either
1) Create a new recordset and load it with the data from the two sources
2) Combine the data in the same SQL statement using aggregates of unions the then bind that to the grid
Those are two answers to accomplish what you need. |
|
|
| |
|
Re: How to use 2 datasource on 1 datagrid
Posted: 06 May 08 8:40 PM
|
2) Combine the data in the same SQL statement using aggregates of unions the then bind that to the grid
Hmm,, I know about union in sql syntax, but I don't know about "bind",,
can u explain about bind data to the grid? or another Link 4 this threat?
|
|
|
| |
| |
|
Re: How to use 2 datasource on 1 datagrid
Posted: 11 May 08 8:22 PM
|
okay,
I had done the result of problem,
I make the another recodset, which this recordset will create table in database,,
I am using
sql2 = "(select a.point_of_charging, count(*) as transaksi, " & _
"sum(b.jumlah) as jumlah " & _
"into data_result_cabang " & _
"from source_msisdn as a, data_independent_in as b " & _
"where left(a.msisdn,6) like left(b.msisdn_target,6) " & _
"group by a.point_of_charging " & _
"order by a.point_of_charging)"
Set rs3 = koneksi.Execute(sql2, , adCmdText)
after that / table "data_result_cabang" created, I will create the column or add the column "percentage",
sql like this :
sql_add = "alter table data_result_cabang add percentage number after"
Set rs_add = koneksi.Execute(sql_add, , adCmdText)
and then I will do selection of data from table "data_result_cabang" with some condition, like this :
sql_list = "select point_of_charging " & _
"from data_result_cabang"
Set rs_list = koneksi.Execute(sql_list, , adCmdText)
rs_list.MoveFirst
While Not rs_list.EOF
'///////////////////////////////////////////////////////////////////////
Dim sql_count_all As String
Dim rs_count_all As ADODB.Recordset
sql_count_all = "select sum(jumlah) as qty_all " & _
"from data_result_cabang"
Set rs_count_all = koneksi.Execute(sql_count_all, , adCmdText)
Dim pof, sql_jml_pof As String
Dim rs_jml_pof As ADODB.Recordset
pof = rs_list("point_of_charging")
sql_jml_pof = "select jumlah " & _
"from data_result_cabang " & _
"where point_of_charging='" & pof & "'"
Set rs_jml_pof = koneksi.Execute(sql_jml_pof, , adCmdText)
Dim percentage_pof, percentage_pof2 As String
Dim sql_update As String
Dim rs_update As ADODB.Recordset
percentage_pof = rs_jml_pof("jumlah") / rs_count_all("qty_all") * 100
percentage_pof2 = FormatNumber(percentage_pof, 0)
sql_update = "update data_result_cabang " & _
"set percentage='" & percentage_pof2 & "' " & _
"where point_of_charging='" & pof & "'"
Set rs_update = koneksi.Execute(sql_update, , adCmdText)
rs_list.MoveNext
Wend
and after that I will get the percentage which I put the data on the datagrid,,
and this my code :
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Option Explicit
Dim koneksi As New ADODB.Connection
Dim sql_drop, sql_add, sql_list, sql, sql2, sql3, data_count As String
Dim rs_drop, rs_add, rs_list, rs, rs2, rs3, rsteks As ADODB.Recordset
Private Sub Form_Load()
Skin1.LoadSkin App.Path & "/skin/winaqua.skn"
Skin1.ApplySkin Me.hWnd
Set koneksi = New ADODB.Connection
koneksi.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=True;User ID= assword=;" & _
"Data Source=" & App.Path & "\database\independent.mdb"
koneksi.Open
koneksi.CursorLocation = adUseClient
'/////////////////////////////////////////////////////////////////////////////
sql_drop = "drop table data_result_cabang"
Set rs_drop = koneksi.Execute(sql_drop, , adCmdText)
sql = "select count(*) as count_dt " & _
"from source_msisdn as a, data_independent_in as b " & _
"where left(a.msisdn,6) like left(b.msisdn_target, 6)"
Set rs = koneksi.Execute(sql, , adCmdText)
sql2 = "(select a.point_of_charging, count(*) as transaksi, " & _
"sum(b.jumlah) as jumlah " & _
"into data_result_cabang " & _
"from source_msisdn as a, data_independent_in as b " & _
"where left(a.msisdn,6) like left(b.msisdn_target,6) " & _
"group by a.point_of_charging " & _
"order by a.point_of_charging)"
Set rs3 = koneksi.Execute(sql2, , adCmdText)
sql_add = "alter table data_result_cabang add percentage number after"
Set rs_add = koneksi.Execute(sql_add, , adCmdText)
sql_list = "select point_of_charging " & _
"from data_result_cabang"
Set rs_list = koneksi.Execute(sql_list, , adCmdText)
rs_list.MoveFirst
While Not rs_list.EOF
'///////////////////////////////////////////////////////////////////////
Dim sql_count_all As String
Dim rs_count_all As ADODB.Recordset
sql_count_all = "select sum(jumlah) as qty_all " & _
"from data_result_cabang"
Set rs_count_all = koneksi.Execute(sql_count_all, , adCmdText)
Dim pof, sql_jml_pof As String
Dim rs_jml_pof As ADODB.Recordset
pof = rs_list("point_of_charging")
sql_jml_pof = "select jumlah " & _
"from data_result_cabang " & _
"where point_of_charging='" & pof & "'"
Set rs_jml_pof = koneksi.Execute(sql_jml_pof, , adCmdText)
Dim percentage_pof, percentage_pof2 As String
Dim sql_update As String
Dim rs_update As ADODB.Recordset
percentage_pof = rs_jml_pof("jumlah") / rs_count_all("qty_all") * 100
percentage_pof2 = FormatNumber(percentage_pof, 0)
sql_update = "update data_result_cabang " & _
"set percentage='" & percentage_pof2 & "' " & _
"where point_of_charging='" & pof & "'"
Set rs_update = koneksi.Execute(sql_update, , adCmdText)
rs_list.MoveNext
Wend
Set rs2 = New ADODB.Recordset
sql2 = "select * from data_result_cabang " & _
"order by point_of_charging"
rs2.Open sql2, koneksi, adOpenDynamic, adLockOptimistic
Set Me.DataGrid1.DataSource = rs2
Me.DataGrid1.Columns(0).Caption = "Cabang"
Me.DataGrid1.Columns(1).Caption = "Transaksi"
Me.DataGrid1.Columns(2).Caption = "Jumlah(Rp)"
Me.DataGrid1.Columns(3).Caption = "Percentage(%)"
Me.DataGrid1.Columns(0).DataField = rs2("point_of_charging")
Me.DataGrid1.Columns(1).DataField = rs2("transaksi")
Me.DataGrid1.Columns(2).DataField = rs2("jumlah")
Me.DataGrid1.Columns(3).DataField = rs2("percentage")
Me.DataGrid1.AllowAddNew = False
Me.DataGrid1.AllowDelete = False
Me.DataGrid1.AllowUpdate = False
Me.DataGrid1.Refresh
Dim sql_date_first, sql_date_last, date_first, date_last, date_all As String
Dim rs_date_first, rs_date_last As ADODB.Recordset
sql_date_first = "select tanggal " & _
"from data_independent_in " & _
"order by tanggal asc"
Set rs_date_first = koneksi.Execute(sql_date_first, , adCmdText)
date_first = rs_date_first("tanggal")
sql_date_last = "select tanggal " & _
"from data_independent_in " & _
"order by tanggal desc"
Set rs_date_last = koneksi.Execute(sql_date_last, , adCmdText)
date_last = rs_date_last("tanggal")
date_all = date_first & Chr(32) & "S/D" & Chr(32) & date_last
Me.SkinLabel_date.Caption = date_all
End Sub
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
so aLL many tHanks for Bob (RJ)Ledger, Ryan Farley, RJ Samp
 |
|
|
|
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!
|
|
|
|
|