COMPUTER PROGRAMMING AND LOGICS: September 2015

Imports System.Data

Imports System.Data.SqlClient

Partial Class _Default Inherits System.Web.UI.Page

Dim con As SqlConnection

Dim cmd As SqlCommand

//CONNECTION

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

//INSERTION

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

//SELECTION

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

con.Open()

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

//DELETE OPERATION

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

//UPDATE OPERATION

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

<%@ 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">

<title>Untitled Page</title>

</head>

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

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

 </p>

<p>

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

</p>

<p>

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

</p>

<p>

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

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

<title>Untitled Page</title>

</head>

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

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

 </p>

<p>

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

</p>

<p>

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

</p>

<p>

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

COMPUTER PROGRAMMING AND LOGICS

Friday, 11 September 2015

DOT NET Programming : -SqlDatabase Manipulation with insert, delete , update

DOT NET Programming :-SQL Database Runtime Dataset code