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="Syst em.Data" %>
<%@ Page Language = "vb" debug="true"%>
<%@ Import Namespace = "System.Data.Ol eDb" %>
<script language="vb" runat="server">
Private Sub Page_Load(sende r As Object, e As EventArgs)
If Not Page.IsPostBack Then
viewstate("sort Field") = "test_id"
viewstate("sort Direction") = "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 & "')"
ExecuteStatemen t(StrSQL)
testBindGrid()
End Sub
sub Delete_Record_t blTest(obj as object, e as DataGridCommand Eventargs)
dim strSQL as string = "delete from tbltest where test_id = " &
e.Item.ItemInde x
lbl_delete_strS QL.text = strsql
end sub
function ExecuteStatemen t(StrSQL)
Dim strConn As String = "Provider=Micro soft.Jet.Oledb. 4.0;" & "Data
Source=" & Server.MapPath( "/data/test.mdb") & ";"
Dim MyConn As New OleDBConnection (strConn)
dim objCmd as new OleDbCommand(St rSQL, MyConn)
objCmd.Connecti on.Open()
objCmd.ExeCuteN onQuery()
''close the connection to get the affected record in the table
objCmd.Connecti on.Close()
end function
Private Sub testBindGrid()
Dim strConn As string = "Provider=Micro soft.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("sort Field").ToStrin g() & " " & viewstate
("sortDirection ").ToString ()
Dim MyConn As New OleDBConnection (strConn)
Dim ds As DataSet = New DataSet()
Dim Cmd As New OleDBDataAdapte r(MySQL,MyConn)
Cmd.Fill(ds,"te st")
testDataGrid.Da taSource = ds
testDataGrid.Da tabind()
End Sub
Private Sub testPageGrid(By Val source As Object, ByVal e As
DataGridPageCha ngedEventArgs)
testDataGrid.Cu rrentPageIndex = e.NewPageIndex
testBindGrid()
End Sub
Private Sub testSortGrid(By Val source As Object, ByVal e As
DataGridSortCom mandEventArgs)
If e.SortExpressio n.ToString() = viewstate("sort Field").ToStrin g() Then
Select Case viewstate("sort Direction").ToS tring()
Case "ASC"
viewstate("sort Direction") = "DESC"
Case "DESC"
viewstate("sort Direction") = "ASC"
End Select
Else
viewstate("sort Field") = e.SortExpressio n
viewstate("sort Direction") = "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:textbo x id="tbx_test"
runat="server"/></td>
</tr>
<tr>
<td>test2:</td>
<td><asp:textbo x 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="testDataGri d"
Width="130"
GridLines="Both "
AutoGenerateCol umns="false"
BorderColor="si lver"
CellPadding="4"
CellSpacing="0"
AllowPaging="tr ue"
OnPageIndexChan ged="testPageGr id"
AllowSorting="t rue"
OnSortCommand=" testSortGrid"
BorderWidth="1"
PageSize="20"
Primarykey="tes t_id"
OnDeleteCommand ="Delete_Record _tblTest">
<headerstyle
Forecolor="#FFF FFF"
Font-Name="Tahoma"
Font-Size="100%"
Font-Bold="true"
BackColor="#FF0 000">
</headerstyle>
<itemstyle
Font-Name="Verdana"
Font-Size="70%"
VerticalAlign=" top"
BackColor="#FFF FFF">
</itemstyle>
<alternatingite mstyle
BackColor="#F4F 4F4">
</alternatingitem style>
<pagerstyle
Position="botto m"
Mode="NumericPa ges"
HorizontalAlign ="Center"
ForeColor="#FFF FFF"
BackColor="#FF0 000">
</pagerstyle>
<columns>
<asp:boundColum n
DataField="test _id"
SortExpression= "test_id"
HeaderText="ID"
ItemStyle-Wrap="false">
</asp:boundColumn >
<asp:boundColum n
DataField="test "
SortExpression= "test"
HeaderText="Tes t"
ItemStyle-Wrap="false">
</asp:boundColumn >
<asp:boundColum n
DataField="test 2"
SortExpression= "test2"
HeaderText="Tes t2"
ItemStyle-Wrap="false">
</asp:boundColumn >
<asp:ButtonColu mn
HeaderText=""
Text="Delete"
CommandName="de lete"/>
</columns>
</asp:DataGrid>
</form>
</body>
</html>
