Hello
I adapted the following code from an example at asp.net. The Page seems
to run OK and generates no errors now. Unfortunately it doesn't actually
UPDATE the Access Database it's supposed to. The page is called when a
user clicks on a link in the previous page Though- I have edited out the
Query.String expression at this stage for simplicity and assigned the
unique key variable empid=2.
Can anyone explain why it fails to update the database please?
Andrew McIntosh
andrew.mcintosh@e...
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
Sub PopulateList()
Dim empid As Integer
Dim DS As DataSet
Dim myConnection As OleDbConnection
Dim MyCommand As OleDbDataAdapter
'empid= Request.QueryString("id")
empid= 2
myConnection = New OleDbConnection
( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
Source=c:\Inetpub\wwwroot\htdocs\dsn\staff.mdb" )
myCommand = New OleDbDataAdapter("SELECT * FROM staff WHERE id = "
& empid & "" , myConnection)
DS = New DataSet()
MyCommand.Fill(DS, "Staff")
MyDataList.DataSource = DS.Tables("staff").DefaultView
MyDataList.DataBind()
End Sub
Sub Page_Load(Sender As Object, E As EventArgs)
If Not (Page.IsPostBack)
PopulateList()
End If
End Sub
Sub MyDataList_Edit(Sender As Object, E As DataListCommandEventArgs)
MyDataList.EditItemIndex = CInt(e.Item.ItemIndex)
PopulateList()
End Sub
Sub MyDataList_Update(Sender As Object, E As DataListCommandEventArgs)
Dim empid As Integer
Dim myConnection As OleDbConnection
Dim MyCommand As OleDbCommand
Dim fname, surname, job_position, rgroup, telephone, email, fax, homepage
As String
fname = CType(e.Item.FindControl("fname"), TextBox).Text
surname = CType(e.Item.FindControl("surname"), TextBox).Text
job_position = CType(e.Item.FindControl("job_position"), TextBox).Text
rgroup = CType(e.Item.FindControl("rgroup"), TextBox).Text
telephone = CType(e.Item.FindControl("telephone"), TextBox).Text
email = CType(e.Item.FindControl("email"), TextBox).Text
fax = CType(e.Item.FindControl("fax"), TextBox).Text
myConnection = New OleDbConnection
( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
Source=c:\Inetpub\wwwroot\htdocs\dsn\staff.mdb" )
myConnection.Open()
myCommand = New OleDbCommand("UPDATE staff SET fname = @fname, surname =
@surname, telephone = @telephone, rgroup= @rgroup, job_position=
@job_position, email = @email, homepage = @homepage where id = " & empid
& "", myConnection)
myCommand.Parameters.Add( New OleDbParameter( "@fname",
OleDbType.Varchar, 30 ))
myCommand.Parameters( "@fname" ).Value = fname
myCommand.Parameters.Add( New OleDbParameter( "@surname",
OleDbType.Varchar, 30 ))
myCommand.Parameters( "@surname" ).Value = surname
myCommand.Parameters.Add( New OleDbParameter( "@job_position",
OleDbType.Varchar, 30 ))
myCommand.Parameters( "@job_position" ).Value = job_position
myCommand.Parameters.Add( New OleDbParameter( "@rgroup",
OleDbType.Varchar, 30 ))
myCommand.Parameters( "@rgroup" ).Value = rgroup
myCommand.Parameters.Add( New OleDbParameter( "@telephone",
OleDbType.Varchar, 30 ))
myCommand.Parameters( "@telephone" ).Value = telephone
myCommand.Parameters.Add( New OleDbParameter( "@email",
OleDbType.Varchar, 30 ))
myCommand.Parameters( "@email" ).Value = email
myCommand.Parameters.Add( New OleDbParameter( "@fax", OleDbType.Varchar,
30 ))
myCommand.Parameters( "@fax" ).Value = fax
myCommand.Parameters.Add( New OleDbParameter( "@homepage",
OleDbType.Varchar, 30 ))
myCommand.Parameters( "@homepage" ).Value = homepage
myCommand.ExecuteNonQuery()
myConnection.Close()
'Dim EditText As HtmlInputText
'EditText = E.Item.FindControl("update")
Message.InnerHtml = "Record Updated: "
MyDataList.EditItemIndex = -1
PopulateList()
End Sub
Sub MyDataList_Cancel(Sender As Object, E As DataListCommandEventArgs)
MyDataList.EditItemIndex = -1
PopulateList()
End Sub
</script>
<body topmargin="0" leftmargin="0" marginwidth="0" marginheight="0">
<form runat="server">
<ASP:DataList id="MyDataList" RepeatColumns="2"
OnEditCommand="MyDataList_Edit" OnUpdateCommand="MyDataList_Update"
OnCancelCommand="MyDataList_Cancel" runat="server">
<ItemTemplate>
<table cellpadding=10 height="200" style="font: 10pt verdana">
<tr>
<td>
<b>Firt Name: </b>
</td>
<td><%# DataBinder.Eval(Container.DataItem, "fname") %>
</td>
</tr>
<tr>
<td>
<b>Second Name: </b>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "surname") %>
</td>
</tr>
<tr>
<td>
<b>Position: </b>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "job_position") %>
</td>
</tr>
<tr>
<td>
<b>Research Group: </b>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "rgroup") %>
</td>
</tr>
<tr>
<td>
<b>Email: </b>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "email") %>
</td>
</tr>
<tr>
<td>
<b>Telephone: </b>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "telephone") %>
</td>
</tr>
<tr>
<td>
<b>Fax: </b>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "fax") %>
</td>
</tr>
<tr>
<td>
<b>Homepage: </b>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "homepage") %>
</td>
</tr>
<tr>
<td>
<asp:linkbutton Text="Edit" CommandName="Edit"
runat="server">
</asp:linkbutton>
</td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table cellpadding=10 style="font: 10pt verdana">
<tr>
<td width=1 >
<td valign="top">
</td>
<td valign="top">
<b>First Name: </b>
<asp:TextBox id="fname" type="text" Text='<%#
DataBinder.Eval(Container.DataItem, "fname") %>' runat="server"/><br>
<b>Surname: </b>
<asp:TextBox id="surname" type="text" Text='<%#
DataBinder.Eval(Container.DataItem, "surname") %>' runat="server"/><br>
<b>Position: </b>
<asp:TextBox id="job_position" type="text" Text='<%#
DataBinder.Eval(Container.DataItem, "job_position") %>'
runat="server"/><br>
<b>Research Group: </b>
<asp:TextBox id="rgroup" type="text" Text='<%#
DataBinder.Eval(Container.DataItem, "rgroup") %>' runat="server"/><br>
<b>Email: </b>
<asp:TextBox id="email" type="text" Text='<%#
DataBinder.Eval(Container.DataItem, "email") %>' runat="server"/><br>
<b>Fax: </b>
<asp:TextBox id="fax" type="text" Text='<%# DataBinder.Eval
(Container.DataItem, "fax") %>' runat="server"/><br>
<b>Telephone: </b>
<asp:TextBox id="telephone" type="text" Text='<%#
DataBinder.Eval(Container.DataItem, "telephone") %>' runat="server"/>
<b>Homepage: </b>
<asp:TextBox id="homepage" type="text" Text='<%#
DataBinder.Eval(Container.DataItem, "homepage") %>' runat="server"/>
<p>
<asp:linkbutton Text="Update" CommandName="Update"
runat="server"></asp:linkbutton>
<asp:linkbutton Text="Cancel" CommandName="Cancel"
runat="server"></asp:linkbutton>
</td>
</tr>
</table>
</EditItemTemplate>
</ASP:DataList>
</form>
<div style="font: 10pt verdana;padding:0,15,15,15" id="Message"
runat="server"/>
</body>
</html>
View Cart
