|
Hello EveryBody,
After a long long time.
Dear Moderator Sorry for posting it here. Could not find a proper place for posting Query related post and also it was urgent.
I have a database in MSAccess.
I have a table as tbl_Menu.
This table has a parent child relation ship.
Parent Node is : MenuItemId and its
Child Node is : MenuItemPopUpHeaderID
The table structure is attached in Excel File with data.(HOW DO I ATTACH THE FILE HERE IN CODE PROJECT?)
I want a query to pick up the last child Item of each Parent Item.
I am using VS 2008 (c#) as front end.
Regards,
Sourabh.
Thanks and Regards,
|
|
|
|
|
Dot Net Jantu wrote: HOW DO I ATTACH THE FILE HERE IN CODE PROJECT?
You don't there is no facility to upload files.
This should be in the General Database forum as it is a query.
What have you tried so far?
|
|
|
|
|
Dear All,
I have manage to process some sorting and calculation in a few columns of my Dataset. I want to do a printout for the data now inside the dataset using Crystal Report Viewer. Can anyone told me how should I put my Crystal Report viewer to display this Dataset? I'm using VB.NET WINFORM 2.0 with ACCESS DATABASE.
This is my code now to process my data inside the dataset:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim i As Integer
Dim BATCH, TRNCDJ, RESONJ, REFNOJ, INVT, CUSTCODE, CUSTNAME, ADD1, ADD2, ADD3 As String
Dim DD, MM, YY, INVNO, QTY, PONO As Integer
Dim Day, Month, Year, D1, ADDRSS, INVNUM As String
Dim mydatatable As New DataTable
Dim ukCulture As CultureInfo = New CultureInfo("en-GB")
Dim myDateTime As DateTime
Try
If ComboBox1.SelectedItem <> "" Then
cn.Open()
MyDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM DATA WHERE ITEMJ='" & ComboBox1.Text & "' ORDER BY DD,MM,YY ASC", cn)
MyDataAdapter.TableMappings.Add("Table", "OrderTable")
DtSet = New System.Data.DataSet
MyDataAdapter.Fill(DtSet)
dt = DtSet.Tables("OrderTable")
DataGridView1.DataSource = DtSet.Tables(0)
cn.Close()
If Trim(TextBox1.Text) <> "" Then
STOCK_BAL = Trim(TextBox1.Text)
Else
STOCK_BAL = 0
End If
mydatatable.Columns.Add("Date", Type.GetType("System.String"))
mydatatable.Columns.Add("CUSTNAME", Type.GetType("System.String"))
mydatatable.Columns.Add("ADDRESS", Type.GetType("System.String"))
mydatatable.Columns.Add("INVOICE_NO", Type.GetType("System.String"))
mydatatable.Columns.Add("PO_NO", GetType(Integer))
mydatatable.Columns.Add("BATCH_NO", Type.GetType("System.String"))
mydatatable.Columns.Add("Qty", GetType(Integer))
mydatatable.Columns.Add("BALANCE", Type.GetType("System.String"))
For i = 0 To dt.Rows.Count - 1
'MessageBox.Show("This is " & i & " ")
BATCH = Trim(DataGridView1.Item(3, i).Value)
DD = Trim(DataGridView1.Item(4, i).Value)
MM = Trim(DataGridView1.Item(5, i).Value)
YY = Trim(DataGridView1.Item(6, i).Value)
If Len(Trim(DD)) = 1 Then
Day = "0" & CStr(DD)
Else
Day = CStr(DD)
End If
If Len(Trim(MM)) = 1 Then
Month = "0" & CStr(MM)
Else
Month = CStr(MM)
End If
If Len(Trim(YY)) = 1 Then
Year = "0" & CStr(YY)
Else
Year = CStr(YY)
End If
D1 = Day & "/" & Month & "/" & Year
If D1 <> "" Then
myDateTime = DateTime.Parse(D1, ukCulture.DateTimeFormat)
End If
QTY = Trim(DataGridView1.Item(7, i).Value)
TRNCDJ = Trim(DataGridView1.Item(8, i).Value)
RESONJ = Trim(DataGridView1.Item(9, i).Value)
REFNOJ = Trim(DataGridView1.Item(10, i).Value)
PONO = Trim(DataGridView1.Item(11, i).Value)
If Trim(DataGridView1.Item(12, i).Value) <> "" Then
INVT = Trim(DataGridView1.Item(12, i).Value)
INVNO = Trim(DataGridView1.Item(13, i).Value)
INVNUM = INVT & CStr(INVNO)
CUSTCODE = Trim(DataGridView1.Item(14, i).Value)
Else
INVT = 0
INVNO = 0
INVNUM = "0"
CUSTCODE = 0
End If
If Trim(DataGridView1.Item(16, i).Value) <> "" Then 'Process the ADDRESS
ADD1 = Trim(DataGridView1.Item(16, i).Value)
ADD2 = Trim(DataGridView1.Item(17, i).Value)
ADD3 = Trim(DataGridView1.Item(18, i).Value)
ADDRSS = ADD1 & " " & ADD2 & " " & ADD3
Else
ADD1 = "0"
ADD2 = "0"
ADDRSS = ADD1 & " " & ADD2
End If
If Trim(DataGridView1.Item(15, i).Value) <> "" Then
CUSTNAME = Trim(DataGridView1.Item(15, i).Value)
Else
CUSTNAME = "Nothing"
End If
If TRNCDJ = "P" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
INVNUM = ""
ADDRSS = ""
PONO = REFNOJ
CUSTNAME = "STOCK PURCHASE"
ElseIf RESONJ = "02" Then
'INVNUM = ""
'ADDRSS = ""
PONO = REFNOJ
CUSTNAME = "INTERNAL TRANSFER"
BATCH = ""
STOCK_BAL = STOCK_BAL + (QTY)
End If
ElseIf TRNCDJ = "R" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
End If
ElseIf TRNCDJ = "G" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
ElseIf RESONJ = "11" Then 'BITA Stock Transfer
CUSTNAME = "STOCK ADJUSTMENT"
ADDRSS = ""
INVNUM = ""
'PONO = REFNOJ
BATCH = ""
STOCK_BAL = STOCK_BAL + (QTY)
End If
ElseIf TRNCDJ = "T" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
End If
End If
Dim myrow As DataRow
myrow = mydatatable.NewRow
myrow("Date") = CStr(Day & "-" & Month & "-" & Year)
myrow("CUSTNAME") = CUSTNAME
myrow("ADDRESS") = ADDRSS
myrow("INVOICE_NO") = INVNUM
myrow("PO_NO") = PONO
myrow("BATCH_NO") = BATCH
myrow("Qty") = QTY
myrow("BALANCE") = STOCK_BAL
mydatatable.Rows.Add(myrow)
Next i
End If
DataGridView2.DataSource = mydatatable
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Currently everything runs perfectly in my mydatatable,it shows the output in a datagridview2. But I need to do printout using the crystal report,can anyone please give me some idea or sample how to do this?
Thank you for reading.
Drex
|
|
|
|
|
Let me get this right!
You get a datatable from Access
Then bind the datatable to a DGV
Then you create another datatable to format the data into, by the looks to faff about with the date and some order types
you then bind that result to a second DGV.
I believe Access supports named queries (something like a stored proc), which is where all this formatting should be done. I would recommend SQL Express as a better solution.
With CR I used to design a report that was serviced by a stored proc but I believe you can pass a dataset to a designed report.
|
|
|
|
|
Hello Mycroft,
I'm not so familiar with the stored procedure as you mentioned. Can you show me some sample,how to do this as what you told me? I need to printout the data by using crystal report. Thank you~!
|
|
|
|
|
This is what I have done in the past ...
Since you already have the data in a dataset, you need to create a schema file for Crystal so that you can design the report.
1) Place this in your code somewhere after the datatable is defined.
mydatatable.writeXMLSchema("C:\TEMP\mytable.xsd")
You will only need this once, or you will need it again if you change the structure of your datatable.
2) from your solution explorer, Add New Item, "Crystal Report"
3) Select, "Use report wizard" to create the report
4) choose "Create New Connection\Database Files" as the data source
5) Select the "c:\temp\mytable.xsd" file
6) choose the "table" to add to your list of selected tables. Click Next
7) Select the fields from the table to include in your report
8) follow the rest of the prompts through the wizard, "grouping","Summaries", "Sorted by", "Chart", "Record Selection", "Styles"
9) This is how I load the reports and attach it to a dataset:
Protected Sub displayRpt(ByVal sRptName As String, ByVal ds As DataSet, ByVal crParamFields As ParameterFields)
Dim RptPath As String
Dim rpt As New ReportDocument
' I store my reports in a folder called, "RptSource", one level down from the main website.
RptPath = Server.MapPath("~/Rptsource")
rpt.Load(RptPath + "\" + sRptName)
rpt.SetDataSource(ds)
' Use the following only if you are passing parameters to the report. In my example, I am not passing parameters
'CrystalReportViewer1.ParameterFieldInfo = crParamFields
CrystalReportViewer1.HyperlinkTarget = "_blank" ' This causes the report to pop into a new browser.
CrystalReportViewer1.DisplayGroupTree = False
CrystalReportViewer1.HasToggleGroupTreeButton = False
CrystalReportViewer1.HasDrillUpButton = False
CrystalReportViewer1.HasViewList = False
CrystalReportViewer1.HasSearchButton = False
CrystalReportViewer1.EnableDrillDown = False ' hmmm
CrystalReportViewer1.Height = 800
CrystalReportViewer1.Width = 900
CrystalReportViewer1.ReportSource = rpt
'CrystalReportViewer1.RefreshReport()
End Sub
|
|
|
|
|
|
hi mates,
i have an editable DataGridViewComboBoxColumn,
i've used the gridMeasure_CellValidating and gridMeasure_EditingControlShowing from msdn samples.
the comboboxcolumn can now be editable but the problem is everytime i add an item, i got the error here.,
Private Sub gridMeasure_CellValidating(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) _
Handles gridMeasure.CellValidating
If (TypeOf CType(sender, DataGridView).EditingControl Is DataGridViewComboBoxEditingControl) Then
Dim comboBoxColumn As DataGridViewComboBoxColumn = _
Me.gridMeasure.Columns(2)
If (e.ColumnIndex = comboBoxColumn.DisplayIndex) Then
If (Not comboBoxColumn.Items.Contains(e.FormattedValue)) Then
<code>comboBoxColumn.Items.Add(e.FormattedValue)</code> -->'Items collection cannot be modified when the DataSource property is set.
End If
End If
End If
End Sub
* I found a solution here
If you want to add an item to the bound combobox, you can add it beforehand in the SQL statement. E.g.
If I want to select a list of authors, then want to add another one then.
SELECT first_name, last_name FROM authors
UNION SELECT 'Joe', 'Bloggs'
But i don't know where to put that statement in my code. could somebody help me or give another solutions on how to add an item with a bound combobox meaning i have a datasource.
thanks in advance, 
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
Sub FillUnits()
Dim mCls As YourClass = New YourClass
Dim i As Integer
With ctlGrid
Dim cmb As New DataGridViewComboBoxCell
For i = 0 To record.Count - 1
cmb.Items.Add(mCls.List_All.Item(i).Name.ToString)
Next
ctlGrid(ColumnNo, CurrentRow) = cmb
End With
End Sub
Public Function List_All() As List(Of YourClass)
Dim _Fill As String = "select Name from Table"
Dim con As New SqlConnection(gConnectionString)
Dim cmd As New SqlCommand(_Fill, con)
Dim dList As New List(Of YourClass)
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
dList.Add(New Cls_Unit_Master(dr.Item("Name").ToString))
End While
dr.Close()
con.Close()
Return dList
End Function
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
wow. this is great!. thanks so much, i'll try this one.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
try to Put your Code in DataError Event of the Grid
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
one more thing, i used OleDb not SqlDB, can i still apply this to adodb.recordset?
and why comboboxcell? do you have a link so that i can refer. thnx.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
Yes you can use , this is my own code , sorry i have no link for this code but i will not recommend you record set approach an old approach i will recommend you to create your on objects and used Object Oriented Approach
not because i am using but you can see as Visual Studio is built everything there in VS is an object and as well as Microsoft Recommended Approach
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
hi,
do you have the entire class example? i still cant get my problem. im stuck on this. i try your code but i cant get it. would you mine if you post your code here. i mean code where
Dim mCls As YourClass = New YourClass and Cls_Unit_Master is written
thanks
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
This is the code for class
Imports System.Data
Imports System.Data.SqlClient
Public Class Cls_Unit_Master
'Public Function FillUnit(ByVal mCmbBox As ComboBox) As Boolean
'Dim _Fill_SUnit As String = "select unit_Name from Unit_Master"
'Dim con As New SqlConnection(gConnectionString)
'Dim cmd As New SqlCommand(_Fill_SUnit, con)
'con.Open()
'Dim dr As SqlDataReader = cmd.ExecuteReader()
'mCmbBox.Items.Add("")
'While dr.Read()
'mCmbBox.Items.Add(dr.Item("Unit_Name").ToString)
'End While
'dr.Close()
'con.Close()
'Return True
'End Function
Public Function List_AllUnits() As List(Of Cls_Unit_Master)
Dim _Fill_SUnit As String = "select unit_Name from Unit_Master"
Dim con As New SqlConnection(gConnectionString)
Dim cmd As New SqlCommand(_Fill_SUnit, con)
Dim dList As New List(Of Cls_Unit_Master)
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
dList.Add(New Cls_Unit_Master(dr.Item("unit_Name").ToString))
End While
dr.Close()
con.Close()
Return dList
End Function
End Class
Code on Form
Sub FillUnits()
Dim mCls As DAL.Cls_Unit_Master = New DAL.Cls_Unit_Master
Dim i As Integer
With ctlGrid --Grid Control
Dim cmb As New DataGridViewComboBoxCell
For i = 0 To mCls.List_AllUnits.Count - 1
cmb.Items.Add(mCls.List_AllUnits.Item(i).Unit_Name.ToString)
Next
ctlGridItems(6, mCurRow) = cmb
End With
End Sub
you can call this function
Call FillUnits()
on any even of your grid or where ever you wants to populate the combo box
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
i got an error here under Public Function List_AllUnits()
dList.Add(New Cls_Unit_Master(dr.Item("unit_Name").ToString))
it says, Too many arguments to 'Public Sub New()'
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
put in class
Sub New()
End Sub
Sub New(ByVal UName As String)
_Unit_Name = UName
End Sub
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
got it. thank u so much.
the very last question. do u have a piece of code of this -> ctlGridItems(6, mCurRow).
honestly, u help me a lot. I hate this f***ing datagridviewcombobox. actually, i have a project that has
4 combobox which can be editable by the user.i have this already in MS Access form but when I implement in visual studio 2005. i don't have any idea on how to do it. thank u very much once again.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
No Problem , pleasure is all mine, i could not forget my days when i used to struggle every one starts from down to up but the key is to keep doing , i believes on that Problem is the Gateway to Success/Development
ctlGridItems(6, mCurRow) = cmb
where cmb is "Dim cmb As New DataGridViewComboBoxCell"
ctlGridItems(6, mCurRow)
(column,Row)
you can call populating of combobox on
Private Sub ctlGridItems_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ctlGridItems.CellEnter
mCurRow = e.RowIndex
If e.ColumnIndex = 6 Then
call urRoutine
end if
end sub
mCurRow is an int variable,
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
hi softdev_sup
i don't have error anymore, the problem is still here
ctlGridItems(6, mCurRow) = cmb , i think so.
no data will show, even the combobobox doesn't show. its only datagridview. do i miss something.
this is what i've done.
Private Sub FillUnits()
Dim mCls As lgCls = New lgCls
Dim counter As Integer
With MyDataGrid
Dim cmb As New DataGridViewComboBoxCell
For counter = 0 To mCls.List_AllMethods.Count - 1
cmb.Items.Add(mCls.List_AllMethods.Item(counter).ToString())
Next
MyDataGrid(2, mCurRow) = cmb
End With
End Sub
Private Sub MyDataGrid_CellEnter(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles MyDataGrid.CellEnter
mCurRow = e.RowIndex
If e.ColumnIndex = 6 Then
End If
End Sub
where mCurRow is declared here
Public Class MyForm<br />
Dim mCurRow As Integer
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
=-=-=-=-=-- MyDataGrid(2, mCurRow) = cmb =-=-=-=-=--=-=-=-=-=--
SEE YOURS COLUMN NO IS 2
=-=-=-=-=-=-=-=-=If e.ColumnIndex = 6 Then-=-=-=-=-=-=-=-=-=-=-
HERE U R USING 6 IT SHOULD BE UR LOOKUP COLUMN I BELIEVE ITS 2
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
thanks for the last day reply.
by the way, can i apply this on my 2nd and 3rd combobox and so on... would it be possible to use and how? is it ok that you can share to me your entire code regarding editable datagridviewcombobox.
thanks again.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
Off course , You can use it for other columns also , this is the whole code their is nothing else ,
you have to make following changes
''in class
Sub New(ByVal URS_VARIABLE As String)
''URS_VARIABLE = URSPROPERTY
''Here
End Sub
Private Sub ctlGridItems_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ctlGridItems.CellEnter
mCurRow = e.RowIndex
If e.ColumnIndex = URS_COLUMNSNO Then
call urRoutine
end if
end sub
=-=-=-=-=-- MyDataGrid(URS_COLUMN_No, mCurRow) = cmb =-=-=-=-=--=-=-=-=-=--
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
sorry to disturb you again, i would like to ask something..
another thing is, what if the combobox is a databound. can i still edit that item inside the combobox, i almost got this dgvcombobox thing. but when i add the new item inside the combobox(column1) then click next combobox(column2), the combobox(column1) is empty string but when i click the dropdown, the added item is there. i want to show the added item on that combobox(column1).
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
i think so when you populates second combo then you are still referencing somewhere yours 1st combo thats why the value disapperars
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
