Insert Update Save Delete Using textbox in vb.net-3

Satyapriyanayak
Posted by Satyapriyanayak under VB.NET category on | Points: 40 | Views : 1150
Stored Procedure:-
Display:-
CREATE PROCEDURE display
AS
select * from student

Insert:-
CREATE PROCEDURE insert1
(@sid varchar(50),@sname varchar(50),@smarks int,@saddress varchar (50),@year varchar(50))
AS
insert student(sid,sname,smarks,saddress,year) values (@sid,@sname,@smarks,@saddress,@year)

Update:-
CREATE PROCEDURE update1
(@sid varchar(50),@sname varchar(50),@smarks int,@saddress varchar (50),@year varchar(50))
AS
update student set sname=@sname,smarks=@smarks,saddress=@saddress,year=@year where sid=@sid

Delete:-
CREATE PROCEDURE delete1
(@sid varchar(50))
AS
delete from student where sid=@sid

Imports System.Data.SqlClient

Module Module1

Public con As New SqlConnection("workstation id=""HOME-Z8CKE1NER2"";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

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

Try
com = New SqlCommand("display", 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
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

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

bm.Position = 0

End Sub

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

bm.Position += 1

End Sub

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

bm.Position -= 1

End Sub

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

bm.Position = bm.Count – 1

End Sub

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

flag = 1
txtid.Text = ""
txtname.Text = ""
txtmarks.Text = ""
txtaddress.Text = ""
txtyear.Text = ""
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

If flag = 1 Then
con.Open()
com = New SqlCommand("insert1", con)
com.CommandType = CommandType.StoredProcedure
com.Parameters.Add("@sid", txtid.Text)
com.Parameters.Add("@sname", txtname.Text)
com.Parameters.Add("@smarks", txtmarks.Text)
com.Parameters.Add("@saddress", txtaddress.Text)
com.Parameters.Add("@year", txtyear.Text)
com.ExecuteNonQuery()
con.Close()
MsgBox("Records added")
ElseIf flag = 2 Then

con.Open()
com = New SqlCommand("update1", con)
com.CommandType = CommandType.StoredProcedure
com.Parameters.Add("@sid", txtid.Text)
com.Parameters.Add("@sname", txtname.Text)
com.Parameters.Add("@smarks", Val(txtmarks.Text))
com.Parameters.Add("@saddress", txtaddress.Text)
com.Parameters.Add("@year", txtyear.Text)
com.ExecuteNonQuery()
con.Close()
MsgBox("Records Updated")
End If

End Sub

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

con.Open()
com = New SqlCommand("delete1", con)
com.CommandType = CommandType.StoredProcedure
com.Parameters.Add("@sid", txtid.Text)
com.ExecuteNonQuery()
con.Close()
MsgBox("Records Deleted")

End Sub

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

con.Open()
com = New SqlCommand("display", con)
com.CommandType = CommandType.StoredProcedure
sqlda = New SqlDataAdapter(com)
ds = New DataSet
sqlda.Fill(ds, "student")
DataGrid1.DataSource = ds
DataGrid1.DataMember = "student"
con.Close()

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

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

Comments or Responses

Login to post response