Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VS2010 VB.NET DataTable
Hi,
 
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
                         dt.Rows.Add(row1(txt_no))
 
                    End If
                End If
                txt1 = Nothing
                txt_no = txt_no + 1
            Next
            dt.TableName = "marq"
            ds.Tables.Add(dt)
            da.Update(ds, "tbl_marq")
 
 
regards
Joe
Posted 31-Jul-12 3:45am
joe_j4K
Edited 1-Aug-12 16:39pm
v3
Comments
Dave Kreskowiak at 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 at 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 at 1-Aug-12 11:22am
   
How does that answer the question of "Without seeing the dataadapater code you're using..."??
joe_j at 1-Aug-12 11:34am
   
oops, its pasted above now.
Rate this: bad
good
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 http://msdn.microsoft.com/en-us/library/aa581769[^] 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.Wink | ;-)
 
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).
 
Alistair
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If you are using SQL Server, SqlBulkCopy.WriteToServer(DataTable) is the solution. Ref: http://msdn.microsoft.com/en-us/library/ex21zs8x.aspx[^]
  Permalink  
Comments
joe_j at 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)



Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2014
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