Imports System.Data
Imports System.Data.Sql
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.Security
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls.WebParts
Partial Class _Default
Inherits System.Web.UI.Page
Public cn As New SqlConnection
Public cmd As New SqlCommand
Public da As SqlDataAdapter
Public dr As SqlDataReader
Public ds As DataSet = New DataSet
Public sql As String = Nothing
\'Public ConString As String = ("Data Source=ANONYMOUS-PC;Initial Catalog=dbSQL;Integrated Security=True") \'this is connected to the server
Public ConString As String = ("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\dbSQL.mdf;Integrated Security=True;User Instance=True")
Public Sub MyCn()
If cn.State = Data.ConnectionState.Open Then cn.Close()
cn.ConnectionString = ConString
cn.Open()
End Sub
Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
MyCn()
GridView1.DataBind()
End Sub
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
If txtFname.Text = "" Or txtMname.Text = "" Or txtLname.Text = "" Or txtAge.Text = "" Or txtBdate.Text = "" Or txtBplace.Text = "" Or txtGender.Text = "" Or txtContact.Text = "" Or txtAddress.Text = "" Then
Label11.Text = "Insuficient Data!!"
Else
AddInfo()
ClearTextBox(Me)
End If
End Sub
Public Sub ClearTextBox(ByVal root As Control)
For Each ctrl As Control In root.Controls
ClearTextBox(ctrl)
If TypeOf ctrl Is TextBox Then
CType(ctrl, TextBox).Text = String.Empty
lblID.Text = ""
GridView1.DataBind()
End If
Next ctrl
End Sub
Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClear.Click
ClearTextBox(Me)
Label11.Text = ""
End Sub
Private Function AddInfo()
Try
Dim xSQL As New System.Text.StringBuilder
xSQL.AppendLine("INSERT INTO tblInfo")
xSQL.AppendLine("(Fname,Mname,Lname,Age,BDay,BPlace,Gender,ContactNum,Address)")
xSQL.AppendLine("VALUES")
xSQL.AppendLine("(@fnam,@mnam,@lnam,@ag,@bda,@bpla,@gen,@cont,@add)")
Using cn As New SqlConnection(ConString)
cn.Open()
Dim cmd As New SqlCommand(xSQL.ToString, cn)
cmd.Parameters.AddWithValue("@fnam", txtFname.Text)
cmd.Parameters.AddWithValue("@mnam", txtMname.Text)
cmd.Parameters.AddWithValue("@lnam", txtLname.Text)
cmd.Parameters.AddWithValue("@ag", txtAge.Text)
cmd.Parameters.AddWithValue("@bda", txtBdate.Text)
cmd.Parameters.AddWithValue("@bpla", txtBplace.Text)
cmd.Parameters.AddWithValue("@gen", txtGender.Text)
cmd.Parameters.AddWithValue("@cont", txtContact.Text)
cmd.Parameters.AddWithValue("@add", txtAddress.Text)
cmd.ExecuteNonQuery()
GridView1.DataBind()
Label11.Text = "Added Info Success!"
End Using
Return True
Catch ex As Exception
Label11.Text = "ERROR" & ex.Message.ToString
Return False
End Try
End Function
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
lblID.Text = GridView1.SelectedRow.Cells(1).Text
txtFname.Text = GridView1.SelectedRow.Cells(2).Text
txtMname.Text = GridView1.SelectedRow.Cells(3).Text
txtLname.Text = GridView1.SelectedRow.Cells(4).Text
txtAge.Text = GridView1.SelectedRow.Cells(5).Text
txtBdate.Text = GridView1.SelectedRow.Cells(6).Text
txtBplace.Text = GridView1.SelectedRow.Cells(7).Text
txtGender.Text = GridView1.SelectedRow.Cells(8).Text
txtContact.Text = GridView1.SelectedRow.Cells(9).Text
txtAddress.Text = GridView1.SelectedRow.Cells(10).Text
End Sub
Protected Sub btnEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEdit.Click
EditData()
ClearTextBox(Me)
End Sub
Private Function EditData()
Try
Dim xSQL As New System.Text.StringBuilder
xSQL.AppendLine("UPDATE tblInfo SET")
xSQL.AppendLine("Fname=@fnam, Mname=@mnam, Lname=@lnam, Age=@ag, BDay=@bda, BPlace=@bpla, Gender=@gen, ContactNum=@cont, Address=@add")
xSQL.AppendLine("WHERE ID = " & lblID.Text.Trim)
Using cn As New SqlConnection(ConString)
cn.Open()
Dim cmd As New SqlCommand(xSQL.ToString, cn)
cmd.Parameters.AddWithValue("@fnam", txtFname.Text)
cmd.Parameters.AddWithValue("@mnam", txtMname.Text)
cmd.Parameters.AddWithValue("@lnam", txtLname.Text)
cmd.Parameters.AddWithValue("@ag", txtAge.Text)
cmd.Parameters.AddWithValue("@bda", txtBdate.Text)
cmd.Parameters.AddWithValue("@bpla", txtBplace.Text)
cmd.Parameters.AddWithValue("@gen", txtGender.Text)
cmd.Parameters.AddWithValue("@cont", txtContact.Text)
cmd.Parameters.AddWithValue("@add", txtAddress.Text)
cmd.ExecuteNonQuery()
GridView1.DataBind()
Label11.Text = "Edited Info Success!"
End Using
Return True
Catch ex As Exception
Label11.Text = ex.Message.ToString
Return False
End Try
End Function
Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If lblID.Text = Nothing Then
Label11.Text = "ID not Selected!"
Else
DeleteItem()
ClearTextBox(Me)
End If
End Sub
Private Function DeleteItem()
Try
Dim xSQL As New System.Text.StringBuilder
xSQL.AppendLine("DELETE FROM tblInfo")
xSQL.AppendLine("WHERE ID = " & lblID.Text.Trim)
Using cn As New SqlConnection(ConString)
cn.Open()
Dim cmd As New SqlCommand(xSQL.ToString, cn)
cmd.ExecuteNonQuery()
cmd.Dispose()
GridView1.DataBind()
Label11.Text = "Data Deleted Successfully!"
End Using
Return True
Catch ex As Exception
Label11.Text = ex.Message.ToString
Return False
End Try
End Function
End Class