Question How can i filter departmentwise report in datagrid or crystal report?

sapna21

New member
Joined
Apr 1, 2010
Messages
3
Programming Experience
Beginner
I want to filter department wise from following database (MS Access)..

S_NO EMP_NAME E_CODE DEPARTMENT COMPANY DIVISION S_DATE E_DATE TOPIC
1 ANAND 1112 HR abcd ghjk 25-Nov-09 11/25/2009 QA
2 PANKAJ 3223 QA abcd ghjk 25-Nov-09 11/25/2009 L.F.T
3 AMITABH 1231 MECHANICAL abcd ghjk 25-Nov-09 11/25/2009 L.F.T
4 GIRJA SHANKAR 5117 HR abcd ghjk 25-Nov-09 11/25/2009 L.F.T
Also i am not able to insert, update :( data through insert query but i can update through following coding:
Imports CrystalDecisions.CrystalReports.Engine

Public Class Form1
Dim inc As Integer
Dim MaxRows As Integer
Dim con As New OleDb.OleDbConnection
Dim Command As OleDb.OleDbCommand
Dim sql As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim datagrid1 As New DataGrid
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0;Data source = C:\Users\sapna.sharma\Documents\Training.mdb"
con.Open()

sql = "SELECT * FROM tblContacts"
da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "Training")
datagrid1.DataSource = ds.DefaultViewManager

con.Close()

MaxRows = ds.Tables("Training").Rows.Count
inc = -1
End Sub

Private Sub NavigateRecords()
txtEmp_Name.Text = ds.Tables("Training").Rows(inc).Item(1)
txtE_code.Text = ds.Tables("Training").Rows(inc).Item(2)
txtDepartment.Text = ds.Tables("Training").Rows(inc).Item(3)
txtCompany.Text = ds.Tables("Training").Rows(inc).Item(4)
txtDivision.Text = ds.Tables("Training").Rows(inc).Item(5)
txtS_Date.Text = ds.Tables("Training").Rows(inc).Item(6)
txtE_Date.Text = ds.Tables("Training").Rows(inc).Item(7)
txtTopic.Text = ds.Tables("Training").Rows(inc).Item(8)
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If inc <> MaxRows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MsgBox("No More Rows")
End If
End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
If inc > 0 Then
inc = inc - 1
NavigateRecords()
ElseIf inc = -1 Then
MsgBox("No Records Yet")
ElseIf inc = 0 Then
MsgBox("First Record")
End If
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("Training").Rows(inc).Item(1) = txtEmp_Name.Text
ds.Tables("Training").Rows(inc).Item(2) = txtE_code.Text
ds.Tables("Training").Rows(inc).Item(3) = txtDepartment.Text
ds.Tables("Training").Rows(inc).Item(4) = txtCompany.Text
ds.Tables("Training").Rows(inc).Item(5) = txtDivision.Text
ds.Tables("Training").Rows(inc).Item(6) = txtS_Date.Text
ds.Tables("Training").Rows(inc).Item(7) = txtE_Date.Text
ds.Tables("Training").Rows(inc).Item(8) = txtTopic.Text
da.Update(ds, "Training")

MsgBox("Data updated")

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
btnCommit.Enabled = True
btnAdd.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False

txtEmp_Name.Clear()
txtE_code.Clear()
txtDepartment.Clear()
txtCompany.Clear()
txtDivision.Clear()
txtS_Date.Clear()
txtE_Date.Clear()
txtTopic.Clear()
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click

If inc <> -1 Then

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("Training").NewRow()

dsNewRow.Item(1) = txtEmp_Name.Text
dsNewRow.Item(2) = txtE_code.Text
dsNewRow.Item(3) = txtDepartment.Text
dsNewRow.Item(4) = txtCompany.Text
dsNewRow.Item(5) = txtDivision.Text
dsNewRow.Item(6) = txtS_Date.Text
dsNewRow.Item(7) = txtE_Date.Text
dsNewRow.Item(8) = txtTopic.Text

ds.Tables("Training").Rows.Add(dsNewRow)
MsgBox("New Record added to the Database")
da.Update(ds, "Training")

MsgBox("New Record added to the Database")

btnCommit.Enabled = False
btnAdd.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True

End If
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("Training").Rows(inc).Delete()
MaxRows = MaxRows - 1

inc = 0
NavigateRecords()
da.Update(ds, "Training")

If MessageBox.Show("Do you really want to Delete this Record?", _
"Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then


MsgBox("Operation Cancelled")
Exit Sub

End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



Dim cr As New ReportDocument

cr.Load("C:\Users\sapna.sharma\Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\CrystalReport2.rpt")

CrystalReportViewer1.ReportSource = cr
CrystalReportViewer1.Refresh()

End Sub


Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load

End Sub
and also i am not able to update Access database through following code::(

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
Dim icount As Integer


con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0;Data source = C:\Users\sapna.sharma\Documents\Training.mdb"
con.Open()

sql = "UPDATE tblContacts SET COMPANY='CAPGEMINI' WHERE EMP_NAME='PANKAJ'"
da = New OleDb.OleDbDataAdapter(sql, con)
Command = New OleDb.OleDbCommand(sql, con)

da.Fill(ds, "Training")
icount = Command.ExecuteNonQuery

con.Close()
End Sub
End Class

PLZZZZZZZZZZZZZZZZZZZZZZZZZZZ help meeeeeeeeee:(
 
Back
Top