DOT NET Programming :-SQL Database Runtime Dataset code

'SQL DATABASE

'Code Behind:

Imports System.Data

Imports System.Data.SqlClient

Partial Class _Default

    Inherits System.Web.UI.Page

    Dim con As SqlConnection

    Dim cmd As SqlCommand

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        con = New SqlConnection("server=LENOVO-PC\SQLEXPRESS;database=cc;integrated security=true")

        gridviewBinding()

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

        con.Open()

        Dim com As New SqlCommand("insert into tname values ('" + TextBox1.Text + "','" + TextBox2.Text + "',3000,600)", con)

        com.ExecuteReader()

        con.Close()

        gridviewBinding()

    End Sub

    Public Sub gridviewBinding()

        con.Open()

        Dim com As New SqlCommand("select * from tname", con)

        com.CommandType = CommandType.Text

        Dim da As New SqlDataAdapter(com)

        Dim ds As New DataSet()

        da.Fill(ds, "tname")

        GridView1.DataSource = ds.Tables("tname")

        GridView1.DataBind()

        con.Close()

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        con.Open()

        Dim name1 As String

        Dim com As New SqlCommand("select name from tname where no='" + TextBox2.Text + "'", con)

        Dim names As String = com.ExecuteScalar()

        Dim com2 As New SqlCommand("select salary from tname where no='" + TextBox3.Text + "'", con)

        Dim ssalary As Integer = com2.ExecuteScalar()

        Dim com3 As New SqlCommand("select bonus from tname where no='" + TextBox4.Text + "'", con)

        Dim sbonus As Integer = com3.ExecuteScalar()

        TextBox1.Text = names

        TextBox3.Text = ssalary

        TextBox4.Text = sbonus

        con.Close()

        gridviewBinding()

    End Sub

    Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click

        con.Open()

        Dim com1 As New SqlCommand("delete from  tname where no=" & Val(TextBox2.Text), con)

        com1.ExecuteNonQuery()

        con.Close()

        gridviewBinding()

    End Sub

    Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click

        con.Open()

        Dim a As Double = 100

        Dim com1 As New SqlCommand("update  tname set salary ='" + TextBox3.Text + "', bonus= '" + TextBox4.Text + "'+3000 where name='" + TextBox1.Text + "' ", con)

        com1.ExecuteNonQuery()

        con.Close()

        gridviewBinding()

    End Sub

    Protected Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click

        con.Open()

        Dim com5 As New SqlCommand("select * from tname where name like '" + TextBox1.Text + "' order by name", con)

        com5.CommandType = CommandType.Text

        Dim da As New SqlDataAdapter(com5)

        Dim ds As New DataSet()

        da.Fill(ds, "tname")

        ListView1.DataSource = ds.Tables("tame")

        ListView1.ItemPlaceholderID = True

        ListView1.DataBind()

        con.Close()

        gridviewBinding()

    End Sub

    Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting

        '        GridView1.DeleteRow(GridView1.SelectedValue(2))

        Dim intNo As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Values(0))

        con.Open()

        Dim com1 As New SqlCommand("delete from  tname where no=" & intNo, con)

        com1.ExecuteNonQuery()

        con.Close()

        gridviewBinding()

    End Sub

    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing

        GridView1.EditIndex = e.NewEditIndex

    End Sub

    Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating

        Dim userid As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Values(0))

        'Dim username As String = GridView1.DataKeys(e.RowIndex).Values("name").ToString()

        Dim txtname As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("GridView1$ctl02$ctl02"), TextBox)

        ' Dim txtDesignation As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtDesg"), TextBox)

        'con.Open()

        ' Dim cmd As New SqlCommand(("update Employee_Details set City='" + txtcity.Text & "',Designation='") + txtDesignation.Text & "' where UserId=" & userid, con)

        ' cmd.ExecuteNonQuery()

        ' con.Close()

        'lblresult.ForeColor = Color.Green

        'lblresult.Text = username & " Details Updated successfully"

        'gvDetails.EditIndex = -1

        'BindEmployeeDetails()

    End Sub

End Class

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

</head>

<body bgcolor="#ff99ff">

    <form id="form1" runat="server">

    <div style="margin-left: 200px">

   

        <asp:GridView ID="GridView1" DataKeyNames="no" runat="server">

            <Columns>

                <asp:CommandField ShowDeleteButton="True" />

                <asp:CommandField ShowEditButton="true" />

               

            </Columns>

        </asp:GridView>

        <asp:ListView ID="ListView1" runat="server">

        </asp:ListView>

   

    </div>

    <p>

        Name<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

    </p>

    <p>

        Number<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

        &nbsp;</p>

    <p>

        Salary:<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

        &nbsp;&nbsp;&nbsp;

    </p>

    <p>

        Bonus:<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

    </p>

    <p>

        &nbsp;</p>

    <p>

        <asp:Button ID="Button1" runat="server" Text="edit for given number" />

        <asp:Button ID="Button2" runat="server" Text="Insert" />

        <asp:Button ID="Button3" runat="server" Text="Delete" />

        <asp:Button ID="Button4" runat="server" Text="update" />

        <asp:Button ID="Button5" runat="server" Text="seperation" />

    </p>

    </form>

</body>

</html>