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
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