delete record from ms access database

Phi · Sep 17, 2003

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>
</asp

ataGrid>
</form>

</body>
</html>

One Handed Man [ OHM ] · Sep 17, 2003

Wrong newsgroup buddy, try adonet or asp

OHM

Phi said:
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>

<aspatagrid 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>

Herfried K. Wagner [MVP] · Sep 17, 2003

Hello,

Phi said:
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/microsoft.public.dotnet.framework.adonet

Web interface:

http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.adonet

phy · Sep 17, 2003

msnews.microsoft.com/microsoft.public.dotnet.framework.adonet

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

Phi

delete record from ms access database

Phi

One Handed Man [ OHM ]

Herfried K. Wagner [MVP]

phy