By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,772 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

delete record from ms access database

P: n/a
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:Datagrid 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:DataGrid>
</form>

</body>
</html>
Nov 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Wrong newsgroup buddy, try adonet or asp

OHM
"Phi" <ph*******@hotmail.com(remove)> wrote in message
news:Xn**********************************@194.109. 133.20...
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:Datagrid 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:DataGrid>
</form>

</body>
</html>

Nov 20 '05 #2

P: n/a
Hello,

"Phi" <ph*******@hotmail.com(remove)> schrieb:
I would like to make a form to add and delete records from my
ms access database.


This is a VB.NET language group. Notice that you will have a better
chance to get an answer if you post to the ADO.NET newsgroup in future:

news://msnews.microsoft.com/microsof...amework.adonet

Web interface:

http://msdn.microsoft.com/newsgroups...amework.adonet

--
Herfried K. Wagner
MVP VB Classic, VB.NET
http://www.mvps.org/dotnet
Nov 20 '05 #3

P: n/a
phy
"Herfried K. Wagner [MVP]" <hi*******@m.activevb.de> wrote in
news:ei**************@TK2MSFTNGP12.phx.gbl:
msnews.microsoft.com/microsoft.public.dotnet.framework.adonet


Sorry, I'm trying a new mailreader...
I pushed the button too soon..

Phi
Nov 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.