update datatable records into access database

Question

1.00/5 (1 vote)

See more:

VS2010

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.

VB

 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_j

Updated 1-Aug-12 16:39pm

Sebastian T Xavier

v3

Add a Solution

Comments

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.

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)

agillanders · Answer 1 · 2012-07-31T10:25:00

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.;-)

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

Ganesan Senthilvel · Answer 2 · 2012-08-01T09:08:00

Solution 2

If you are using SQL Server, SqlBulkCopy.WriteToServer(DataTable) is the solution. Ref: http://msdn.microsoft.com/en-us/library/ex21zs8x.aspx[^]

Posted 1-Aug-12 9:08am

Ganesan Senthilvel

Comments

joe_j 3-Aug-12 7:41am

using MS Access DB actually