//Ado Project Data Insert Update Save Delete Using Sql Statement//

 

Imports System.Data.SqlClient

 

Module Module1

Public con As New SqlConnection("workstation id=""HOME-6TIW5FOKEY"";packet size=4096;user id=sa;initial catalog=pint" & _

    "u;persist security info=False")

    Public com As SqlCommand

    Public sqlda As SqlDataAdapter

    Public ds As DataSet

    Public str As String

    Public dt As DataTable

    Public dr As DataRow

    Public dc As DataColumn

    Public bm As BindingManagerBase

    Public flag As Byte

    Public objcom As SqlCommandBuilder

 

End Module

 

Imports System.Data.SqlClient

 

Public Class Form1

    Inherits System.Windows.Forms.Form

 

    Dim bm As BindingManagerBase

 

Private Sub btnload1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnload1.Click

 

        str = "select * from student"

        com = New SqlCommand(str, con)

        sqlda = New SqlDataAdapter(com)

        ds = New DataSet

        sqlda.Fill(ds, "student")

        'dt = ds.Tables("student")

        txtid.DataBindings.Add("Text", ds, "student.sid")

        txtname.DataBindings.Add("Text", ds, "student.sname")

        txtmarks.DataBindings.Add("Text", ds, "student.smarks")

        txtaddress.DataBindings.Add("Text", ds, "student.saddress")

        txtyear.DataBindings.Add("Text", ds, "student.year")

        bm = Me.BindingContext(ds, "student")

        bm.Position = 0

  Label6.Text = ((Me.BindingContext(ds, "student").Position + 1).ToString + " of ") & Me.BindingContext(ds,  "student").Count.ToString

 

    End Sub

 

 

Private Sub btnfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfirst.Click

 

        bm.Position = 0

Label6.Text = ((Me.BindingContext(ds, "student").Position + 1).ToString + " of ") & Me.BindingContext(ds, "student").Count.ToString

 

    End Sub

 

    Private Sub btnnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnext.Click

 

        bm.Position += 1

  Label6.Text = ((Me.BindingContext(ds, "student").Position + 1).ToString + " of ") & Me.BindingContext(ds, "student").Count.ToString

 

    End Sub

 

    Private Sub btnprevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprevious.Click

 

        bm.Position -= 1

  Label6.Text = ((Me.BindingContext(ds, "student").Position + 1).ToString + " of ") & Me.BindingContext(ds, "student").Count.ToString

 

    End Sub

 

    Private Sub btnlast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlast.Click

 

        bm.Position = bm.Count - 1

  Label6.Text = ((Me.BindingContext(ds, "student").Position + 1).ToString + " of ") & Me.BindingContext(ds, "student").Count.ToString

 

    End Sub

 

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

 

        txtid.Text = ""

        txtname.Text = ""

        txtaddress.Text = ""

        txtmarks.Text = ""

        txtyear.Text = ""

        flag = 1

        txtid.Focus()

 

    End Sub

 

    Private Sub btnmodify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmodify.Click

 

        flag = 2

 

    End Sub

 

    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

 

        Try

            If flag = 1 Then

 

                con.Open()

str = "insert into student values('" & txtid.Text.Trim & "','" & txtname.Text.Trim & "'," & txtmarks.Text.Trim & ",'" & txtaddress.Text.Trim & "','" & txtyear.Text.Trim & "')"

                com = New SqlCommand(str, con)

                com.ExecuteNonQuery()

                con.Close()

                MsgBox("Records Successfuly Inserted")

                txtid.Clear()

                txtname.Clear()

                txtmarks.Clear()

                txtaddress.Clear()

                txtyear.Clear()

 

            ElseIf flag = 2 Then

 

                con.Open()

str = "update student set sname='" & txtname.Text.Trim & "',smarks=" & txtmarks.Text.Trim & ",saddress='" & txtaddress.Text.Trim & "',year='" & txtyear.Text.Trim & "' where sid='" & txtid.Text.Trim & "'"

                com = New SqlCommand(str, con)

                com.ExecuteNonQuery()

                con.Close()

                MsgBox("Records Successfuly Updated")

            End If

 

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

 

    End Sub

 

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click

 

        Try

str = "delete from student where sid='" & txtid.Text.Trim & "'"

            com = New SqlCommand(str, con)

            con.Open()

            com.ExecuteNonQuery()

            MsgBox("Records Successfuly Deleted")

            con.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

 

    End Sub

 

 

 

 

    Private Sub btnload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnload.Click

 

        Try

            con.Open()

            str = "select * from student"

            com = New SqlCommand(str, con)

            sqlda = New SqlDataAdapter(com)

            ds = New DataSet

            sqlda.Fill(ds, "student")

            DataGrid1.DataSource = ds

            DataGrid1.DataMember = "student"

            con.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

 

    Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click

 

         Application.Exit()

 

    End Sub

 

    Private Sub btnmaxmarks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnmaxmarks.Click

 

        Try

            con.Open()

            str = "select max(smarks)from student"

            com = New SqlCommand(str, con)

            txtmaxmarks.Text = com.ExecuteScalar

            con.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

     End Sub

 

    Private Sub btnminmarks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnminmarks.Click

 

        Try

            con.Open()

            str = "select min(smarks)from student"

            com = New SqlCommand(str, con)

            txtminmarks.Text = com.ExecuteScalar

            con.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

End Class