Click here to Skip to main content
12,750,464 members (37,460 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB Visual-Studio VS2010 VB.NET , +

I have data dynamically added into a datatable, how would i insert that data into an access datasource.

The datatable would have more than 5 records, is it possible to insert all the 5 records into the access database in one go rather than using ExecuteNonQuery() 5 times.

I tried using dataadapter, but guess im doing something wrong.

Could someone guide me in the right direction please?
This is the code i was trying.

 Dim da As New OleDbDataAdapter("SELECT * FROM tbl_marq", sqlcon)
Dim dt As DataTable = New DataTable
 Dim ds As DataSet = New DataSet
 For Each c As Control In contentPage.Controls
                Dim txt1 As TextBox = TryCast(contentPage.FindControl("txt_upd" & txt_no), TextBox)
                If txt1 IsNot Nothing Then
                    If Not txt1.Text = "" Then
                         row1(txt_no) = dt.NewRow()
                         row1(txt_no)(0) = txt1.Text

                    End If
                End If
                txt1 = Nothing
                txt_no = txt_no + 1
            dt.TableName = "marq"
            da.Update(ds, "tbl_marq")

Posted 31-Jul-12 4:45am
Updated 1-Aug-12 17:39pm
Dave Kreskowiak 31-Jul-12 10:48am
Without seeing the dataadapter code you're using it's pretty much impossible to tell you where you went wrong. Add this code you your post, inside PRE tags, using the Improve Question widget.
joe_j 1-Aug-12 11:05am
what im trying to achieve is the the below
there are 5 or 6 text boxes in the aspx page.
and once text has been entered into them and the "update" button is clicked, these text should be inserted into the MS access database as separate rows.

With the code that i have currently I'm able to get the text in each textbox, but not able to update them into the database.
Dave Kreskowiak 1-Aug-12 11:22am
How does that answer the question of "Without seeing the dataadapater code you're using..."??
joe_j 1-Aug-12 11:34am
oops, its pasted above now.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

There are many solutions from down and dirty to elegantly structured. I have found that establishing good practices has paid huge dividends as projects grow. Also a lot of the grunt work is done for you by the compiler.

I highly recommend reading Tutorial 1 for your language of choice at[^] and building from there. The whole documentation around creating a robust n-teir architecture is very well put together.

Although it is somewhat targetted at major applications it actually works really well for quick and simple sites too once you understand the principles. To add a declarative dataset for a simple table in VWD (or VS) takes seconds and you get full CRUD methods that you can use to access your table. Figuring it out the first time takes a little longer but no pain, no gain as they say.;-)

My habit now is:
All write operations are done through DataSets built declaratively in the builder UI. This mnakes things much easier for both development and maintenance of the inital TableAdapters (literally drag and drop) plus enhanced queries on to those tables.

Nearly all programmatic read operations are the same. But I mainly build a BLL (business logic layer) of classes that wrap database access so page development is just a matter of instantiating the relevant objects - the data connectivity is nicely isolated to the BLL. I use the DataSource controls fairly extensvely for page building but only for READ ONLY access. Having the user do a row selection and adding more meaningful controls to handle inserting or updaing from there via the BLL.

It's a reliable design pattern and means I always know where to look for my data access code. Also any changes are automatically propogated to ALL the pages using the BLL classes rather than having to do a lot of refactoring of multiple pages just to accommodate small changes (e.g. new columns and so on).

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

If you are using SQL Server, SqlBulkCopy.WriteToServer(DataTable) is the solution. Ref:[^]
joe_j 3-Aug-12 7:41am
using MS Access DB actually

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170215.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100