Inserting into access db using VWD.

Peki · February 9th, 2005, 05:05 AM

Hello!

I just joined here and also just started using asp.net 2.0.

So my question is how can i INSERT a new value to an access database. in my case a new category to a "cats" table. as i understand first i need to create a form with a label, a textbox and a submit button. then i need to create an AccessDataSource, and last code a btn_click event. so this is where i stoped, i dont how how to do so.

Here is a sample picture and code.

Acess DB:
-Cats
  -CatID
  -CatName

http://img218.exs.cx/img218/9517/snap0481fc.jpg

<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblInsertCat" Runat="server" Text="Enter category name:"></asp:Label>
        <asp:TextBox ID="txtInsertCat" Runat="server"></asp:TextBox>
        <asp:Button ID="btnInsertCat" Runat="server" Text="Insert" OnClick="btnInsertCat_Click" />
        <br />
        <asp:AccessDataSource ID="AccessDataSource1" Runat="server" DataFile="~/Data/data.mdb"
            DeleteCommand="DELETE FROM [Cats] WHERE [CatID] = ?" InsertCommand="INSERT INTO [Cats] ([CatID], [CatName]) VALUES (?, ?)"
            SelectCommand="SELECT * FROM [Cats]" UpdateCommand="UPDATE [Cats] SET [CatName] = ? WHERE [CatID] = ?">
            <DeleteParameters>
                <asp:Parameter Type="Int32" Name="CatID"></asp:Parameter>
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Type="String" Name="CatName"></asp:Parameter>
                <asp:Parameter Type="Int32" Name="CatID"></asp:Parameter>
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Type="Int32" Name="CatID"></asp:Parameter>
                <asp:Parameter Type="String" Name="CatName"></asp:Parameter>
            </InsertParameters>
        </asp:AccessDataSource>

    </div>
    </form>
</body>

----------------------------------------

Partial Class Default_aspx

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

    End Sub
End Class

IceBreakerG · April 11th, 2005, 09:12 PM

I know this is a few months old, but if you're still interested, try this code in your btnInsertCat_Click event:

Private Sub btnInsertCat_Click(ByVal sender As Object, ByVal e As System.EventArgs)
     'Add a new category into the table
     Dim cnCat As New OleDbConnection(<Connection String Here>)
     Dim daCat As New OleDbDataAdapter
     Dim cmCat As New OleDbCommand
     Dim sSQL As String

     sSQL = "INSERT INTO INSERT INTO [Cats] ([CatID], [CatName]) VALUES (?, ?)"
     cmCat.CommandText = sSQL
     cmCat.CommandType = CommandType.Text
     cmCat.Connection = cnCat
     daCat.Command = cmCat
     cmCat.ExecuteNonQuery()
     If cnCat.State = ConnectionState.Open() Then cnCat.Close()
     cmCat.Dispose()
     daCat.Dispose()
End Sub

If any thinks there are any errors in this code, please correct it, I hope this helps.