Anyone who has had any dealings with databases knows there are several things which are needed:
Selecting Records
Inserting New Records
Deleting Records
Updating/Editing Recordsh
This code sample shows two things – Selecting Records, and then, choosing one record to then Update/Edit. It’s based on an earlier code sample (Populating a Form’s TextBoxes from a DB Query)
What you will see added here, is a very simple subroutine called ‘doUpdate’, which makes a connection to the database and performs a very simple Update SQL statement. There are only three fields in this simple table (ID, fname and lname). The ID field is an autonumbering/Identity field in SQL Server
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.1">
<title>Choosing & Updating a Record</title>
<script language="VB" runat="server">
Dim intWdth, intID as integer
Dim strFirst, strLast, strTitle as string
Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then
BindData
End If
End Sub
Sub BindData()
Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=YourDataBase"
Dim sql as string = "Select id, fname, lname from TestInsert"
Dim conn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(sql, conn)
Dim objDR as SQLDataReader
conn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
list1.datasource=objDR
list1.DataValueField="ID"
list1.datatextfield="Lname"
list1.databind
list1.selectedindex=0
conn.Close()
End Sub
Sub EditRecord(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=YourDataBase"
Dim sql as string = "Select ID, Fname, Lname from TestInsert Where ID = " & list1.selecteditem.value
Dim conn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(sql, conn)
conn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
While objDR.Read()
intID=objDR("ID")
strFirst=objDR("Fname")
strLast=objDR("Lname")
End While
page.databind()
label1.text=""
label2.text=""
End Sub
Sub doUpdate(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=Your_Db"
Dim MySQL as string = "Update TestInsert Set Fname=@Fname, Lname=@Lname Where id=@ID"
Dim Conn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(MySQL, Conn)
cmd.Parameters.Add(New SQLParameter("@ID", lblID.text))
cmd.Parameters.Add(New SQLParameter("@Fname", frmFirstName.text))
cmd.Parameters.Add(New SQLParameter("@Lname", frmLastName.text))
Conn.Open()
cmd.ExecuteNonQuery
label1.text = "It''s Done!"
label1.text="Successfully updated -- - "
BindData
list1.SelectedIndex=List1.Items.IndexOf(List1.Items.FindByValue(lblID.text))
End Sub
</script>
</head>
<body>
<form Name="form1" runat="server">
<table border="1" width="100%">
<tr>
<td align="center"width="145" valign="Top"><b><i>Choose from List:</i></b><br>
<asp:ListBox id="list1" Width="125px" height="150px" runat="server" /><br>
<asp:button id="button1" Text="Edit Record" onclick="EditRecord" runat="server" />
</td>
<td align="Left" valign="Top">
<b>ID :</b> <asp:Label id="lblID" Text="<%# intID %>" runat="server" /><br>
<b>FirstName : </b> <asp:textbox id="frmFirstName" Text="<%# strFirst %>" runat="server" /><br>
<b>LastName : </b><asp:textbox id="frmLastName" runat="server" Text="<%# strLast %>" /><br>
<br>
<br>
<asp:button id="button2" Text="Update" onclick="doupdate" runat="server" />
<i><asp:Label ID="label1" forecolor="red" runat="server" /></i><br>
<i><asp:Label ID="label2" runat="server" /></i>
</td>
</tr>
</table>
</form>
</body>
</html>