Add, Update And Delete From A Textbox (Access)

Question

0.00/5 (No votes)

See more:

Hi There.

Please could someone explain the simplist and effective method to Add, Update And Delete records to a access database without using the Bind Navigation control. As the Bind navigation does not contain any code to how it adds/updates the dataset. I have searched google and all i find is how to insert directly into the database but my application uses databound controls. Please could someone point in the right direction....as you can tell im a noobist.

I have a database with 1 table called Field_Items with the fields ID(0), Name(1), Description(2). I have a form with 3 textbox to match the fields in the table and 2 buttons for new and save action.

Thanks in Advance!

Posted 23-Oct-11 20:16pm

SIFNOk

Updated 23-Oct-11 20:22pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manoj Kishor Bhoir · Answer 1 · 2011-10-26T06:02:00

First Import the following dll's

VB

Imports System.Data
Imports System.Data.OleDb

To create connection between application and Database

SQL

Public Sub OpenConnection()
If con.State = ConnectionState.Open Then
con.Close()                                    
End If
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\DatabaseName.mdb"
Try
con.Open()                                     
Catch ex As Exception                                                   objda.SelectCommand = New OleDbCommand
objda.UpdateCommand = New OleDbCommand
objda.SelectCommand.Connection = con                       objda.UpdateCommand.Connection = con
End Sub

Adding New Data is nothing but the clearing the Textbox fields

VB

Texbox1.Clear
Texbox2.Clear

To Save data form Texbox fields to Access Daabase

VB

objda.SelectCommand.CommandText = "Insert into TableName(Field1, Field2) values(@Field1, @Field2)"
objda.SelectCommand.Parameters.AddWithValue("@StudID", txtstudid.Text)
Try
OpenConnection()
objda.SelectCommand.ExecuteNonQuery()
MessageBox.Show("Information saved Successfully", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message)
End Try

To Upadate Values present into the Database

VB

objda.UpdateCommand.CommandText = "Update TableName SET Field1='" & txtstudname.Text & "', Field2='" & txtmother.Text & "'"
Try
OpenConnection()
objda.UpdateCommand.ExecuteNonQuery()
MessageBox.Show("Information Modified Successfully", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message)
End Try

To Delete the record from Detabase

VB

objda.SelectCommand.CommandText = "DELETE * FROM TableName WHERE Field1='" & Textbox1.Text & "'"
Try
OpenConnection()
objda.SelectCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

Al Moje · Answer 2 · 2011-10-23T23:21:00

Hi,

Try this if could help. Debug to trace the flow of code...
Just separate code behind...

VB

  <%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script type="text/javascript" language="javascript">

    function DeleteData() {
        return confirm("Are you sure you want to delete the record");
    }
</script>

<script runat="server">
    Public recordPointer As Int32 = 0
    Public Class Attachments
        Private _id As Int32
        Private _pageName As String
        
        Public Property Id() As String
            Get
                Return _id
            End Get
            Set(ByVal value As String)
                _id = value
            End Set
        End Property
        Public Property PageName() As String
            Get
                Return _pageName
            End Get
            Set(ByVal value As String)
                _pageName = value
            End Set
        End Property
        
        Public Sub Attachments()
        End Sub
        
    End Class
    
    Protected Sub TaskGridView_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs)

    End Sub

    Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.addNew.Enabled = False
        Me.addNew.Visible = False
        Me.submit.Enabled = True
        Me.submit.Visible = True      
    End Sub
    
  

    Protected Sub GridView1_SelectedIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSelectEventArgs)
        BindData(recordPointer)
    End Sub

    Protected Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        recordPointer = 0
        BindData(recordPointer)
    End Sub
    
    Protected Sub BindData(ByVal seclectedRecord As Int32)
        Dim lstAttchments As List(Of Attachments) = New List(Of Attachments)
        Dim strSQL2 As String = "SELECT ID, pagename FROM tblPages"
        Dim dbsource As String = "acm2000.mdb"
        Dim iMapPath As String = Server.MapPath(".").ToString() & "\app_data\"
        Dim iConn2 As New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & iMapPath & dbsource)
        Dim iCmd2 As New OleDbCommand(strSQL2, iConn2)
        iConn2.Open()
        Using (iCmd2)
            iCmd2.CommandType = CommandType.Text
            Dim dr As OleDbDataReader = iCmd2.ExecuteReader()
            Using (dr)
                While (dr.Read())
                    Dim temp As Attachments = New Attachments()
                    temp.Id = dr("id")
                    temp.PageName = dr("pageName")
                    lstAttchments.Add(temp)
                End While
            End Using
        End Using
        
        If lstAttchments.Count > 0 And recordPointer = 0 Then
            
        End If

        Dim lst As Object = lstAttchments
        GridView1.DataSource = lstAttchments
        GridView1.DataBind()
        iConn2.Close()
        iCmd2.Dispose()
    End Sub

    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs)

    End Sub
    
    Public Sub GridView1_LnkDelete(ByVal Sender As Object, ByVal e As EventArgs)
               
        Dim LnkDelete As LinkButton = DirectCast(Sender, LinkButton)
        Dim row As GridViewRow = DirectCast(LnkDelete.NamingContainer, GridViewRow)
        Dim strId As String = row.Cells(2).Text
        Dim strPageName As String = row.Cells.Item(3).Text    
        'todo Delete code here...
    End Sub
    
    Public Sub GridView1_LnkEdit(ByVal Sender As Object, ByVal e As EventArgs)
       
        Dim LnkEdit As LinkButton = DirectCast(Sender, LinkButton)
        Dim row As GridViewRow = DirectCast(LnkEdit.NamingContainer, GridViewRow)
        Dim strId As String = row.Cells(2).Text
        Dim strPageName As String = row.Cells.Item(3).Text
        'todo Edit code here...
    End Sub
    
      
  

    Protected Sub addNew_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        'todo Your add code here...
    End Sub

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)

    End Sub

   
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body style="font-family: Arial, Helvetica, sans-serif;">
    <form id="Form1" runat="server" onload="Form1_Load">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    
    <asp:Button ID="addNew" runat="server" Text="Add New" OnClick="addNew_Click" />
    <asp:Button ID="submit" runat="server" Text="Save page contents" OnClick="submit_Click"
        Visible="false" />
    <br />
    <table width="100%">
        <tr>
            <td>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
                    OnRowUpdated="TaskGridView_RowUpdated" OnSelectedIndexChanging="GridView1_SelectedIndexChanging"
                    OnPageIndexChanging="GridView1_PageIndexChanging">
                    <columns>
                        <asp:TemplateField HeaderText="Delete" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                            <itemtemplate>
                                <asp:LinkButton ID="lnkDelete" runat="server" CommandArgument='<%#Eval("ID") %>'
                                    OnCommand="GridView1_LnkDelete" OnClientClick="return DeleteData();"> Delete
                                
                            </itemtemplate>
                        
                        <asp:TemplateField HeaderText="Edit" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                            <itemtemplate>
                                <asp:LinkButton ID="lnkEdit" runat="server" CommandArgument='<%#Eval("ID") %>' OnCommand="GridView1_LnkEdit"> Edit
                                
                            </itemtemplate>
                        
                        <asp:BoundField DataField="ID" HeaderText="ID No." InsertVisible="False" ReadOnly="True"
                            SortExpression="ID" />
                        <asp:BoundField DataField="pagename" HeaderText="Page Name" SortExpression="pagename" />
                    </columns>
                
                <asp:HiddenField ID="hfId" runat="server" />
                <asp:HiddenField ID="hfPageName" runat="server" />
                <br />
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

Do not forget to vote if could help...

Regrds,
Al Moje

Add, Update And Delete From A Textbox (Access)

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0