im creating a page which can update, insert, edit the contain of a microsoft access db. the update funtion got some problem n i cant solve it. i check the example from webside, but all i found is using sql db.
can any1 help me to take a look for it? pls copy n try run it, here is all the codes... thanks.
<%@ Page Language="
VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
dim Conn as new OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0; " &"Data Source=c:\Inetpub\wwwroot\db1.mdb")
sub Page_Load(Sender as Object, e as EventArgs)
if Not Page.IsPostBack then
FillDataGrid()
end if
end sub
sub Submit(Sender as Object, e as EventArgs)
dim i, j as integer
dim params(4) as string
dim strText as string
dim blnGo as boolean= true
j=0
for i = 0 to AddPanel.Controls.Count-1
if AddPanel.controls(i).GetType Is GetType(TextBox) then
strText=Ctype(AddPanel.Controls(i), TextBox).Text
if strText<>"" then
params(j)=strText
else
blnGo= false
lblMessage.Text=lblMessage.Text & "u forgot to enter a value for" & AddPanel.Controls(i).ID & lblMessage.Style("ForeColor")= "Red"
end if
j=j+1
end if
next
if not blnGo then
exit sub
end if
dim strsql as string= "INSERT INTO tbUser" & "(firstname, lastname, Address,"&" phone) VALUE ("& "'"& params(0) &"', " &"'"& params(1) &"', " &"'"& params(2) &"', " &"'"& params(3) &"')"
ExecuteStatement(strsql)
FillDataGrid()
end sub
' ----------------- edit, delete, update, cancel, and page changed methods-----------
sub dgData_Edit (Sender as object,e as DataGridCommandEventArgs)
FillDataGrid(e.Item.ItemIndex)
end sub
sub dgData_Delete(Sender as object, e as DataGridCommandEventArgs)
dim strsql as string="DELETE FROM tbUser" & "WHERE userid="& e.Item.ItemIndex + 1
ExecuteStatement(strsql)
FillDataGrid()
end sub
sub dgData_Cancel(Sender as object, e as DataGridCommandEventArgs)
FillDataGrid(-1)
end sub
' sub dgData_Update(Sender as object, e as DataGridCommandEventArgs)
' if UpdateStore (e as DataGridCommandEventArgs) then
' FillDataGrid(-1)
' end if
' end sub
sub dgData_PageIndexChanged(Sender as Object, e as DataGridPageChangedEventArgs)
dgData.DataBind()
end sub
'----------------------------------------------------------------
function UpdateStore (e as DataGridCommandEventArgs)as boolean
dim i,j as integer
dim params(4) as string
dim strText as string
dim blnGo as boolean= false
j=0
for i =1 to e.Item.Cells.Count-3
strText=Ctype(e.Item.Cells(i).Controls(0), TextBox).Text
if strText <> "" then
params(j)=strText
j=j+1
' else
' blnGo=false
' lblMessage.Text=lblMessage.Text & "u forgot to enter value!!<p>"
end if
next
if not blnGo then
return false
exit function
end if
dim strsql as string="UPDATE tbUser SET " & "firstname='" & params(0) & "'," &"lastname='" & params(1) & "'," &"Address='" & params(2) & "'," &"phone='" & params(3) & "'" & "WHERE userid="& Ctype(e.Item.Cells(0).Controls(1), Label).text
ExecuteStatement(strsql)
return blnGo
end function
sub FillDataGrid(Optional EditIndex as integer=-1)
dim objCmd as new OleDbCommand("select * from tbUser", Conn)
dim objReader as OleDbDataReader
try
objCmd.Connection.Open()
objReader= objCmd.ExecuteReader()
catch ex as Exception
lblmessage.Text=" error retrieving from database."
end try
dgData.DataSource=objReader
if not EditIndex.Equals(Nothing) then
dgData.EditItemIndex = EditIndex
end if
dgData.DataBind()
objReader.Close
objCmd.Connection.Close()
end sub
function ExecuteStatement(strsql)
dim objCmd as new OleDbCommand(strsql,Conn)
try
objCmd.Connection.Open()
objCmd.ExecuteNonQuery()
catch ex as Exception
lblMessage.Text= "error updating the database."
end try
objCmd.Connection.Close()
end function
</script>
<html>
<head>
</head>
<body>
<asp:Label id="lblMessage" runat="server"></asp:Label>
<form runat="server">
<asp:DataGrid id="dgData" runat="server" OnPageIndexChanged="dgData_PageIndexChanged" OnCancelCommand="dgData_Cancel" OnEditCommand="dgData_Edit" OnDeleteCommand="dgData_Delete" BorderColor="Black" GridLines="Vertical" cellpadding="4" width="100%" AutoGenerateColumns="false"> <%-- OnUpdateCommand="dgData_Update" --%>
<Columns>
<asp:TemplateColumn HeaderText="ID">
<ItemTemplate>
<asp:Label id="Name" runat="server" Text= '<%# Container.DataItem("userid")%>' />
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="firstname" HeaderText="firstname"></asp:BoundColumn>
<asp:BoundColumn DataField="lastname" HeaderText="lastname "></asp:BoundColumn>
<asp:BoundColumn DataField="Address" HeaderText="Address"></asp:BoundColumn>
<asp:BoundColumn DataField="phone" HeaderText="phone"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" HeaderText="Edit" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:ButtonColumn Text="Delete" CommandName="delete"></asp:ButtonColumn>
</Columns>
</asp:DataGrid>
<p>
<asp:Panel id="AddPanel" runat="server">
<table>
<tbody>
<tr>
<td valign="top" width="100">
first n last name:
</td>
<td valign="top" width="300">
<asp:TextBox id="tbFName" runat="server"></asp:TextBox>
<asp:TextBox id="tbLName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td valign="top">
Address:</td>
<td valign="top">
<asp:TextBox id="tbAddress" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td valign="top">
phone:</td>
<td valign="top">
<asp:TextBox id="tbPhone" runat="server" size="11"></asp:TextBox>
<p></p>
</td>
</tr>
<tr>
<td valign="top" align="right" colspan="2">
<asp:Button id="btSubmit" onclick="Submit" runat="server" text="Add"></asp:Button>
</td>
</tr>
</tbody>
</table>
</asp:Panel>
</p>
</form>
</body>
</html>
edit, update method
