Imports System.Data.SqlClient
Public Class view1
Inherits System.Web.UI.Page
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Button1.DataBinding
If Not IsPostBack Then
BindGridView()
End If
End Sub
Private Function GetConnectionString() As String
Return System.Configuration.ConfigurationManager.ConnectionStrings("nitink").ConnectionString
End Function
Private Sub BindGridView()
Dim Customer As New DataTable()
Dim connection As New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
Try
connection.Open()
Dim sqlStatement As String = "SELECT Top(10)* FROM Customer"
Dim cmd As New SqlCommand(sqlStatement, connection)
Dim sqlDa As New SqlDataAdapter(cmd)
sqlDa.Fill(Customer)
If Customer.Rows.Count > 0 Then
GridView1.DataSource = Customer
GridView1.DataBind()
End If
Catch ex As System.Data.SqlClient.SqlException
Dim msg As String = "Fetch Error:"
msg += ex.Message
Throw New Exception(msg)
Finally
connection.Close()
End Try
End Sub
Private Sub UpdateOrAddNewRecord(ByVal ID As String, ByVal Company As String, ByVal Name As String, ByVal Title As String, ByVal Address As String, ByVal CounTry As String, ByVal isUpdate As Boolean)
Dim connection As SqlConnection = New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
Dim sqlStatement As String = String.Empty
If Not isUpdate Then
sqlStatement = "INSERT INTO Customer" + "(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +
"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)"
Else
sqlStatement = "UPDATE Customer" + "SET CompanyName = @CompanyName,ContactName = @ContactName," + "ContactTitle = @ContactTitle,Address = @Address,Country = @Country" + "WHERE CustomerID = @CustomerID,"
End If
Try
connection.Open()
Dim cmd As SqlCommand = New SqlCommand(sqlStatement, connection)
cmd.Parameters.AddWithValue("@CustomerID", ID)
cmd.Parameters.AddWithValue("@CompanyName", Company)
cmd.Parameters.AddWithValue("@ContactName", Name)
cmd.Parameters.AddWithValue("@ContactTitle", Title)
cmd.Parameters.AddWithValue("@Address", Address)
cmd.Parameters.AddWithValue("@Country", CounTry)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
Dim msg As String = "Insert/Update Error:"
msg += ex.Message
Throw New Exception(msg)
Finally
connection.Close()
End Try
End Sub
Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, False)
BindGridView()
End Sub
Protected Sub GridView1_RowEditing(sender As Object, e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
GridView1.EditIndex = e.NewEditIndex
BindGridView()
End Sub
Protected Sub GridView1_RowCancelingEdit(sender As Object, e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
GridView1.EditIndex = -1
BindGridView()
End Sub
Protected Sub GridView1_RowUpdating(sender As Object, e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
Dim id As String = GridView1.Rows(e.RowIndex).Cells(0).Text
Dim company As String = (CType(GridView1.Rows(e.RowIndex).Cells(1).Controls(0), TextBox)).Text
Dim name As String = (CType(GridView1.Rows(e.RowIndex).Cells(2).Controls(0), TextBox)).Text
Dim title As String = (CType(GridView1.Rows(e.RowIndex).Cells(3).Controls(0), TextBox)).Text
Dim address As String = (CType(GridView1.Rows(e.RowIndex).Cells(4).Controls(0), TextBox)).Text
Dim counTry As String = (CType(GridView1.Rows(e.RowIndex).Cells(5).Controls(0), TextBox)).Text
Dim UpdateOrAddNewRecord(id, company, name, title, address, counTry, True)
GridView1.EditIndex = -1
BindGridView()
End Sub
Private Sub DeleteRecord(ByVal ID As String)
Dim connection As SqlConnection = New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
Dim sqlStatement As String = "DELETE FROM Customer WHERE CustomerID = @CustomerID"
Try
connection.Open()
Dim cmd As SqlCommand = New SqlCommand(sqlStatement, connection)
cmd.Parameters.AddWithValue("@ID", ID)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
Dim msg As String = "Deletion Error:"
msg += ex.Message
Throw New Exception(msg)
Finally
connection.Close()
End Try
End Sub
Protected Sub GridView1_RowDeleting(sender As Object, e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
Dim id As String = GridView1.Rows(e.RowIndex).Cells(0).Text
DeleteRecord(id)
BindGridView()
End Sub
End Class