Net Assignment

 

Net Assignment

https://drive.google.com/drive/folders/1MeQkRGn77-w2D7lQJhHo_HLFMLDWYV1S?usp=sharing

Join table :-

Data Tables

 

  Form Design: multipleTableDataFetch.vb

Controls’ ID: (txtRno, txtName, txtAddr, cityCombo, txtBid, txtIssueId, iDate, rDate, dg, insBtn, updBtn, delBtn, srchBtn, dispBtn, clrBtn, recordFillBtn)

 

Imports System.Data.OleDb

 

Public Class multipleTableDataFetch

Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DatabaseEg.accdb")

 

Sub display()

Dim cmd As New OleDbCommand("select student.rno, sname, address, city, issue_no,book_id,issue_date,return_date from book_issue,student where book_issue.rno=student.rno", cn)

        Dim da As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet

        da.Fill(ds)

 

        da.DataSource = ds.Tables(0)

End Sub

 

Private Sub multipleTableDataFetch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        display()

End Sub

 

Private Sub srchBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles srchBtn.Click

Dim cmd As New OleDbCommand("select student.rno, sname, address, city, issue_no, book_id,issue_date,return_date from book_issue,student where book_issue.rno=student.rno and return_date like '" & Date.Today & "'", cn)

    

   Dim da As New OleDbDataAdapter(cmd)

   Dim ds As New DataSet

   da.Fill(ds)

   dg.DataSource = ds.Tables(0)

End Sub

 

Private Sub dispBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dispBtn.Click

        display()

End Sub

 

Private Sub insBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insBtn.Click

    Dim str As String

 

str = "insert into student values('" & txtRno.Text & "', '" & txtName.Text & "', '" & txtAddr.Text & "' ,'" & cityCombo.Text & "')"

    Dim cmd As New OleDbCommand(str, cn)

    cn.Open()

    cmd.ExecuteNonQuery()

    cn.Close()

 

str = "insert into book_issue values('" & txtIssueId.Text & "', '" & txtRno.Text & "', '" & txtBid.Text & "' ,'" & iDate.Value.Date & "','" & rDate.Value.Date & "')"

   

    Dim cmd1 As New OleDbCommand(str, cn)

    cn.Open()

    cmd1.ExecuteNonQuery()

    cn.Close()

 

    MsgBox(" Record is inserted...")

   display()

End Sub

 

Private Sub delBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles delBtn.Click

    If txtRno.Text = "" Then

        MsgBox("please enter roll no.")

    Else

        Dim str As String

str = "delete from student where rno='" & txtRno.Text & "'"

        

Dim cmd As New OleDbCommand(str, cn)

         cn.Open()

         cmd.ExecuteNonQuery()

         cn.Close()

 

str = "delete from book_issue where rno='" & txtRno.Text & "'"

        

Dim cmd1 As New OleDbCommand(str, cn)

         cn.Open()

         cmd1.ExecuteNonQuery()

         cn.Close()

 

         MsgBox("Record is deleted...")

         display()

     End If

End Sub

 

Private Sub updBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles updBtn.Click

    If txtRno.Text = "" Then

        MsgBox("please enter roll no.")

    Else

        Dim str As String

str = "update student set sname='" & txtName.Text & "', address='" & txtAddr.Text & "' ,city='" & cityCombo.Text & "' where rno='" & txtRno.Text & "'"

        

   Dim cmd As New OleDbCommand(str, cn)

       cn.Open()

       cmd.ExecuteNonQuery()

       cn.Close()

 

str = "update book_issue set issue_no='" & txtIssueId.Text & "', book_id='" & txtBid.Text & "' , issue_date='" & iDate.Value.Date & "', return_date='" & rDate.Value.Date & "' where rno='" & txtRno.Text & "'"

     

      Dim cmd1 As New OleDbCommand(str, cn)

      cn.Open()

      cmd1.ExecuteNonQuery()

      cn.Close()

 

      MsgBox("Record is updated...")

      display()

   End If

End Sub

 

Private Sub clrBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clrBtn.Click

        txtRno.Clear()

        txtName.Clear()

        txtAddr.Clear()

        txtBid.Clear()

        txtIssueId.Clear()

        iDate.Value = Date.Today

        iDate.Value = Date.Today

End Sub

 

Private Sub recordFillBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles recordFillBtn.Click

  

If txtRno.Text = "" Then

        MsgBox("please enter roll no.")

    Else

Dim cmd As New OleDbCommand("select student.rno, sname, address, city, issue_no, book_id,issue_date,return_date from book_issue,student where book_issue.rno=student.rno and student.rno= '" & txtRno.Text & "'", cn)

      

     Dim da As New OleDbDataAdapter(cmd)

     Dim ds As New DataSet

     da.Fill(ds)

 

     txtRno.Text = ds.Tables(0).Rows(0)("rno")

     txtName.Text = ds.Tables(0).Rows(0)("sname")

     txtAddr.Text = ds.Tables(0).Rows(0)("address")

     cityCombo.Text = ds.Tables(0).Rows(0)("city")

     txtIssueId.Text = ds.Tables(0).Rows(0)("issue_no")

     txtBid.Text = ds.Tables(0).Rows(0)("book_id")

     iDate.Value = ds.Tables(0).Rows(0)("issue_date")

     rDate.Value = ds.Tables(0).Rows(0)("return_date")

   End If

 End Sub

 

End Class

-----------------------------------------------------------------------------------------------

Search by date ,sno , today

Operation

1.      Search between date

2.      Search total no of books issue by given student

3.      Search who issue book today

 

Imports System.Data.OleDb

Public Class libr

    Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\vb.net\employee.accdb")

    Private Sub libr_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim cmd As New OleDbCommand("select * from stud ", cnn)

        Dim da As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

    End Sub

 

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

        Dim cmd As New OleDbCommand("select * from BOOK where iss_date between " & DateTimePicker1.Value.Date.ToOADate() & " and " & DateTimePicker2.Value.Date.ToOADate() & "", cnn)

 

        Dim da As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

    End Sub

 

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

        Dim cmd As New OleDbCommand("select stud.Sname,book.Bname,book.iss_date from stud,BOOK where stud.sno=" & TextBox1.Text & " and stud.sno=book.sno", cnn)

        Dim da As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

        Dim cmd1 As New OleDbCommand("select count(*) from stud,BOOK where stud.sno=" & TextBox1.Text & " and stud.sno=book.sno", cnn)

        cnn.Open()

        Label4.Text = "total No of books are" & cmd1.ExecuteScalar

    End Sub

 

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click

        Dim cmd As New OleDbCommand("select * from BOOK where iss_date = " & Date.Now().Date.ToOADate() & "", cnn)

        Dim da As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)

    End Sub

End Class

 

------------------------------------------------------------------------------

database wise demo

Data table:

 

Form Design: DoctorDet.vb

Controls’ ID (txtDid, txtName, optM, optF, dt, spec, txtChrg, insBtn, updBtn, delBtn, dg)

 

 

Imports System.Data.OleDb

Public Class DoctorDet

 

Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DatabaseEg.accdb")

 

Private Sub DoctorDet_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        displayData()

End Sub

 

Sub displayData()

        Dim cmd As New OleDbCommand("select * from doctor", cn)

   Dim da As New OleDbDataAdapter(cmd)

   Dim ds As New DataSet

   da.Fill(ds)

 

   da.DataSource = ds.Tables(0)

 End Sub

 

Private Sub insBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insBtn.Click

        Dim str As String

        Dim gen As String

        If optM.Checked = True Then

            gen = "Male"

        Else

            gen = "Female"

        End If

 

        str = "insert into doctor values(" & txtDid.Text & ", '" & txtName.Text & "', '" & gen & "', '" & dt.Value & "','" & spec.SelectedItem & "', " & txtChrg.Text & ")"

 

        Dim cmd As New OleDbCommand(str, cn)

        cn.Open()

        cmd.ExecuteNonQuery()

        cn.Close()

        MsgBox("Record is inserted...")

        displayData()

 

    End Sub

Private Sub updBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles updBtn.Click

        Dim str As String

        Dim gen As String

        If optM.Checked = True Then

            gen = "Male"

        Else

            gen = "Female"

        End If

 

str = "update doctor set dname= '" & txtName.Text & "', gender='" & gen & "', dob= '" & dt.Value & "',spec='" & spec.SelectedItem & "', charges=" & txtChrg.Text & " where did= " & txtDid.Text & ""

 

        Dim cmd As New OleDbCommand(str, cn)

        cn.Open()

        cmd.ExecuteNonQuery()

        cn.Close()

        MsgBox("Record is updated...")

        displayData()

End Sub

 

Private Sub delBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles delBtn.Click

       

Dim str As String

str = "delete from doctor where did= " & txtDid.Text & ""

       

Dim cmd As New OleDbCommand(str, cn)

    cn.Open()

    cmd.ExecuteNonQuery()

    cn.Close()

    MsgBox("Record is deleted...")

    displayData()

End Sub

End Class

------------------------------------------------

DataView.vb [Design]

TextBox and Button Control Ids txtId txtName txtCity txtPh   insBtn updBtn delBtn clrBtn   dg

 

DataView.vb

 

Imports System.Data.OleDb

 

Public Class DataView

   

Dim cn As New OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DatabaseEg.accdb")

 

Private Sub DataView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        showData()

End Sub

 

Private Sub insBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insBtn.Click

 

        Dim str As String

        str = "insert into Cust values(" & txtId.Text & ", '" & txtName.Text & "', '" & txtCity.Text & "', " & txtPh.Text & ")"

        Dim cmd As New OleDbCommand(str, cn)

        cn.Open()

        cmd.ExecuteNonQuery()

        cn.Close()

 

        MsgBox("Record is iserted...")

        showData()

    End Sub

   

Sub showData()

     Dim cmd As New OleDbCommand("select * from Cust", cn)

     Dim da As New OleDbDataAdapter(cmd)

     Dim ds As New DataSet

     da.Fill(ds)

     dg.DataSource = ds.Tables(0)

End Sub

 

Private Sub updBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles updBtn.Click

        Dim str As String

        str = "update Cust set cname='" & txtName.Text & "', city='" & txtCity.Text & "',  phno=" & txtPh.Text & " where cid=" & txtId.Text & ""

        Dim cmd As New OleDbCommand(str, cn)

        cn.Open()

        cmd.ExecuteNonQuery()

        cn.Close()

 

        MsgBox("Record is updated...")

        showData()

    End Sub

 

Private Sub delBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles delBtn.Click

        Dim str As String

        str = "delete from Cust where cid=" & txtId.Text & ""

        Dim cmd As New OleDbCommand(str, cn)

        cn.Open()

        cmd.ExecuteNonQuery()

        cn.Close()

 

        MsgBox("Record is deleted...")

        showData()

    End Sub

 

Private Sub clrBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clrBtn.Click

        txtId.Clear()

        txtName.Clear()

        txtCity.Clear()

        txtPh.Clear()

    End Sub

End Class