STUDENT RECORD USING ADO.NET

PROGRAM:

Imports System.Data.SqlClient

Public Class Form1

    Dim sqlconn As New SqlConnection()

    Dim sqlcomm As New SqlCommand()

    Dim sqldaat As New SqlDataAdapter()

    Dim ds As New DataSet()

    Private Sub StdBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles StdBindingNavigatorSaveItem.Click

Me.Validate()

Me.StdBindingSource.EndEdit()

Me.TableAdapterManager.UpdateAll(Me.VickyDataSet)

    End Sub

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

        'TODO: This line of code loads data into the 'VickyDataSet2.std' table. You can move, or remove it, as needed.

Me.StdTableAdapter1.Fill(Me.VickyDataSet.std)

    End Sub

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

sqlconn = New SqlConnection("Data Source=PRINCES\QLEXPRESS;InitialCatalog=vicky;Integrated Security=True")

sqlcomm = New SqlCommand("dbo.StoredProcedure1", sqlconn)

sqlcomm.CommandType = CommandType.StoredProcedure

sqlcomm.Parameters.AddWithValue("@name", TextBox1.Text)

sqlcomm.Parameters.AddWithValue("@regno", TextBox2.Text)

sqlcomm.Parameters.AddWithValue("@dept", TextBox3.Text)

sqlconn.Open()

sqlcomm.ExecuteNonQuery()

sqlconn.Close()

MessageBox.Show("inserted",”Data Inserted”)

    End Sub

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

sqlconn = New SqlConnection("Data Source=PRINCE\SQLEXPRESS;InitialCatalog=vicky;Integrated Security=True")

sqlcomm = New SqlCommand("dbo.StoredProcedure3", sqlconn)

sqlcomm.CommandType = CommandType.StoredProcedure

sqlcomm.Parameters.AddWithValue("@name", TextBox1.Text)

sqlconn.Open()

sqlcomm.ExecuteNonQuery()

sqlconn.Close()

MessageBox.Show("deleted",”Record Deleted”)

    End Sub

Private Sub update_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click

sqlconn = New SqlConnection("Data Source=PRINCE\SQLEXPRESS;InitialCatalog=vicky;Integrated Security=True")

sqlcomm = New SqlCommand("dbo.StoredProcedure5", sqlconn)

sqlcomm.CommandType = CommandType.StoredProcedure

sqlconn.Open()

sqlcomm.Parameters.AddWithValue("@name", TextBox1.Text)

sqlcomm.Parameters.AddWithValue("@regno", TextBox2.Text)

sqlcomm.Parameters.AddWithValue("@dept", TextBox3.Text)

        Try

sqlcomm.ExecuteNonQuery()

        Catch ex As SqlException

        Catch ex As Exception

        Finally

            If IsNothing(sqlcomm) = False Then

sqlcomm.Dispose()

sqlcomm = Nothing

            End If

sqlconn.Close()

        End Try

MessageBox.Show("Updated", "Record Updated")

    End Sub

End Class

   Private Sub select_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click

sqlconn = New SqlConnection("Data Source=PRINCE\SQLEXPRESS;InitialCatalog=vicky;Integrated Security=True")

sqlcomm = New SqlCommand("dbo.StoredProcedure6", sqlconn)

sqlcomm.CommandType = CommandType.StoredProcedure

sqlconn.Open()

sqldaat.SelectCommand = sqlcomm

sqldaat.Fill(ds, "dbo.StoredProcedure6")

sqlconn.Close()

    End Sub

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

        End

    End Sub

Create Database:

Create database student;

Create Table:

Create table std(name nvarchar(50), regnoint, deptnvarchar(50));

STORED PROCEDURE:

Insert:

ALTER PROCEDURE dbo.StoredProcedure1

            @name nvarchar(50),@regnoint,@deptnvarchar(50)

AS

insert into std values(@name,@regno,@dept)

            RETURN

Delete:

ALTER PROCEDURE dbo.StoredProcedure3

            @name nvarchar(50)

AS

delete from std where @name=name

            RETURN

Update:

ALTER PROCEDURE dbo.StoredProcedure5

            @name nvarchar(50),@regnoint,@deptnvarchar(50)

AS

updatestd set name=@name,regno =@regno,dept=@dept where name=@name

            RETURN

Select:

ALTER PROCEDURE dbo.StoredProcedure6

            @name nvarchar(50)

AS

select* from std where name=@name

            RETURN