P
Phi
Hi,
I hope somebody could help me with this problem.
I would like to make a form to add and delete records from my ms access
database. I've found most of the codes from the internet and adjusted
some part of it. Anyhow, inserting records to the database seems to work
fine, but deleting the records is not working as I want to and I just
can't see what's causing the problem.
Please, feel free to respond. Any comments/suggestion is welcome.
Thanks in advance for you help.
Kind regards,
Phi
code
========
<%@ Import Namespace="System.Data" %>
<%@ Page Language = "vb" debug="true"%>
<%@ Import Namespace = "System.Data.OleDb" %>
<script language="vb" runat="server">
Private Sub Page_Load(sender As Object, e As EventArgs)
If Not Page.IsPostBack Then
viewstate("sortField") = "test_id"
viewstate("sortDirection") = "DESC"
testBindGrid()
End If
End Sub
sub onupdate(obj as object, e as eventargs)
dim StrSQl as string = "Insert into tbltest (test, test2 ) values
('" & tbx_test.text & "', '" & tbx_test2.text & "')"
ExecuteStatement(StrSQL)
testBindGrid()
End Sub
sub Delete_Record_tblTest(obj as object, e as DataGridCommandEventargs)
dim strSQL as string = "delete from tbltest where test_id = " &
e.Item.ItemIndex
lbl_delete_strSQL.text = strsql
end sub
function ExecuteStatement(StrSQL)
Dim strConn As String = "Provider=Microsoft.Jet.Oledb.4.0;" & "Data
Source=" & Server.MapPath("/data/test.mdb") & ";"
Dim MyConn As New OleDBConnection(strConn)
dim objCmd as new OleDbCommand(StrSQL, MyConn)
objCmd.Connection.Open()
objCmd.ExeCuteNonQuery()
''close the connection to get the affected record in the table
objCmd.Connection.Close()
end function
Private Sub testBindGrid()
Dim strConn As string = "Provider=Microsoft.Jet.Oledb.4.0;" & "Data
Source=" & Server.MapPath("/data/test.mdb") & ";"
Dim MySQL As string = "SELECT tbltest.test_id, tbltest.test,
tbltest.test2 FROM tbltest ORDER BY " _
& viewstate("sortField").ToString() & " " & viewstate
("sortDirection").ToString()
Dim MyConn As New OleDBConnection(strConn)
Dim ds As DataSet = New DataSet()
Dim Cmd As New OleDBDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"test")
testDataGrid.DataSource = ds
testDataGrid.Databind()
End Sub
Private Sub testPageGrid(ByVal source As Object, ByVal e As
DataGridPageChangedEventArgs)
testDataGrid.CurrentPageIndex = e.NewPageIndex
testBindGrid()
End Sub
Private Sub testSortGrid(ByVal source As Object, ByVal e As
DataGridSortCommandEventArgs)
If e.SortExpression.ToString() = viewstate("sortField").ToString() Then
Select Case viewstate("sortDirection").ToString()
Case "ASC"
viewstate("sortDirection") = "DESC"
Case "DESC"
viewstate("sortDirection") = "ASC"
End Select
Else
viewstate("sortField") = e.SortExpression
viewstate("sortDirection") = "DESC"
End If
testBindGrid()
End Sub
</script>
<html>
<body>
<form runat="server" method="post">
Insert Records in to the table Test<br><br>
<table>
<tr>
<td>test:</td>
<td><asp:textbox id="tbx_test"
runat="server"/></td>
</tr>
<tr>
<td>test2:</td>
<td><asp:textbox id="tbx_test2"
runat="server"/><br></td>
</tr>
<tr>
<td>
</td>
<td>
<asp:button id="button1" OnClick=
"onupdate" text="Add record" runat="server"/><br>
<asp:label id="lbl_delete_strSQL"
runat="server"/>
</td>
</tr>
</table>
<br><br><br>
<asp
atagrid runat="server"
Id="testDataGrid"
Width="130"
GridLines="Both"
AutoGenerateColumns="false"
BorderColor="silver"
CellPadding="4"
CellSpacing="0"
AllowPaging="true"
OnPageIndexChanged="testPageGrid"
AllowSorting="true"
OnSortCommand="testSortGrid"
BorderWidth="1"
PageSize="20"
Primarykey="test_id"
OnDeleteCommand="Delete_Record_tblTest">
<headerstyle
Forecolor="#FFFFFF"
Font-Name="Tahoma"
Font-Size="100%"
Font-Bold="true"
BackColor="#FF0000">
</headerstyle>
<itemstyle
Font-Name="Verdana"
Font-Size="70%"
VerticalAlign="top"
BackColor="#FFFFFF">
</itemstyle>
<alternatingitemstyle
BackColor="#F4F4F4">
</alternatingitemstyle>
<pagerstyle
Position="bottom"
Mode="NumericPages"
HorizontalAlign="Center"
ForeColor="#FFFFFF"
BackColor="#FF0000">
</pagerstyle>
<columns>
<asp:boundColumn
DataField="test_id"
SortExpression="test_id"
HeaderText="ID"
ItemStyle-Wrap="false">
</asp:boundColumn>
<asp:boundColumn
DataField="test"
SortExpression="test"
HeaderText="Test"
ItemStyle-Wrap="false">
</asp:boundColumn>
<asp:boundColumn
DataField="test2"
SortExpression="test2"
HeaderText="Test2"
ItemStyle-Wrap="false">
</asp:boundColumn>
<asp:ButtonColumn
HeaderText=""
Text="Delete"
CommandName="delete"/>
</columns>
</aspataGrid>
</form>
</body>
</html>
I hope somebody could help me with this problem.
I would like to make a form to add and delete records from my ms access
database. I've found most of the codes from the internet and adjusted
some part of it. Anyhow, inserting records to the database seems to work
fine, but deleting the records is not working as I want to and I just
can't see what's causing the problem.
Please, feel free to respond. Any comments/suggestion is welcome.
Thanks in advance for you help.
Kind regards,
Phi
code
========
<%@ Import Namespace="System.Data" %>
<%@ Page Language = "vb" debug="true"%>
<%@ Import Namespace = "System.Data.OleDb" %>
<script language="vb" runat="server">
Private Sub Page_Load(sender As Object, e As EventArgs)
If Not Page.IsPostBack Then
viewstate("sortField") = "test_id"
viewstate("sortDirection") = "DESC"
testBindGrid()
End If
End Sub
sub onupdate(obj as object, e as eventargs)
dim StrSQl as string = "Insert into tbltest (test, test2 ) values
('" & tbx_test.text & "', '" & tbx_test2.text & "')"
ExecuteStatement(StrSQL)
testBindGrid()
End Sub
sub Delete_Record_tblTest(obj as object, e as DataGridCommandEventargs)
dim strSQL as string = "delete from tbltest where test_id = " &
e.Item.ItemIndex
lbl_delete_strSQL.text = strsql
end sub
function ExecuteStatement(StrSQL)
Dim strConn As String = "Provider=Microsoft.Jet.Oledb.4.0;" & "Data
Source=" & Server.MapPath("/data/test.mdb") & ";"
Dim MyConn As New OleDBConnection(strConn)
dim objCmd as new OleDbCommand(StrSQL, MyConn)
objCmd.Connection.Open()
objCmd.ExeCuteNonQuery()
''close the connection to get the affected record in the table
objCmd.Connection.Close()
end function
Private Sub testBindGrid()
Dim strConn As string = "Provider=Microsoft.Jet.Oledb.4.0;" & "Data
Source=" & Server.MapPath("/data/test.mdb") & ";"
Dim MySQL As string = "SELECT tbltest.test_id, tbltest.test,
tbltest.test2 FROM tbltest ORDER BY " _
& viewstate("sortField").ToString() & " " & viewstate
("sortDirection").ToString()
Dim MyConn As New OleDBConnection(strConn)
Dim ds As DataSet = New DataSet()
Dim Cmd As New OleDBDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"test")
testDataGrid.DataSource = ds
testDataGrid.Databind()
End Sub
Private Sub testPageGrid(ByVal source As Object, ByVal e As
DataGridPageChangedEventArgs)
testDataGrid.CurrentPageIndex = e.NewPageIndex
testBindGrid()
End Sub
Private Sub testSortGrid(ByVal source As Object, ByVal e As
DataGridSortCommandEventArgs)
If e.SortExpression.ToString() = viewstate("sortField").ToString() Then
Select Case viewstate("sortDirection").ToString()
Case "ASC"
viewstate("sortDirection") = "DESC"
Case "DESC"
viewstate("sortDirection") = "ASC"
End Select
Else
viewstate("sortField") = e.SortExpression
viewstate("sortDirection") = "DESC"
End If
testBindGrid()
End Sub
</script>
<html>
<body>
<form runat="server" method="post">
Insert Records in to the table Test<br><br>
<table>
<tr>
<td>test:</td>
<td><asp:textbox id="tbx_test"
runat="server"/></td>
</tr>
<tr>
<td>test2:</td>
<td><asp:textbox id="tbx_test2"
runat="server"/><br></td>
</tr>
<tr>
<td>
</td>
<td>
<asp:button id="button1" OnClick=
"onupdate" text="Add record" runat="server"/><br>
<asp:label id="lbl_delete_strSQL"
runat="server"/>
</td>
</tr>
</table>
<br><br><br>
<asp
atagrid runat="server"Id="testDataGrid"
Width="130"
GridLines="Both"
AutoGenerateColumns="false"
BorderColor="silver"
CellPadding="4"
CellSpacing="0"
AllowPaging="true"
OnPageIndexChanged="testPageGrid"
AllowSorting="true"
OnSortCommand="testSortGrid"
BorderWidth="1"
PageSize="20"
Primarykey="test_id"
OnDeleteCommand="Delete_Record_tblTest">
<headerstyle
Forecolor="#FFFFFF"
Font-Name="Tahoma"
Font-Size="100%"
Font-Bold="true"
BackColor="#FF0000">
</headerstyle>
<itemstyle
Font-Name="Verdana"
Font-Size="70%"
VerticalAlign="top"
BackColor="#FFFFFF">
</itemstyle>
<alternatingitemstyle
BackColor="#F4F4F4">
</alternatingitemstyle>
<pagerstyle
Position="bottom"
Mode="NumericPages"
HorizontalAlign="Center"
ForeColor="#FFFFFF"
BackColor="#FF0000">
</pagerstyle>
<columns>
<asp:boundColumn
DataField="test_id"
SortExpression="test_id"
HeaderText="ID"
ItemStyle-Wrap="false">
</asp:boundColumn>
<asp:boundColumn
DataField="test"
SortExpression="test"
HeaderText="Test"
ItemStyle-Wrap="false">
</asp:boundColumn>
<asp:boundColumn
DataField="test2"
SortExpression="test2"
HeaderText="Test2"
ItemStyle-Wrap="false">
</asp:boundColumn>
<asp:ButtonColumn
HeaderText=""
Text="Delete"
CommandName="delete"/>
</columns>
</asp
ataGrid></form>
</body>
</html>