Save multiple records in one connection in access in c# win. form

Question

3.67/5 (2 votes)

See more:

.NET3.5

, +

How to save multiple records in one connection by using access database in c#. Like saving 3 records by making only one connection.

For saving 1 record at one connection

string Data = "Insert Into Tbl(Field1,Field2)values(0,1)";

Thanks in advance

Posted 26-Mar-14 23:40pm

agent_kruger

Updated 27-Mar-14 22:04pm

v4

Add a Solution

Comments

Tejas Vaishnav 27-Mar-14 7:34am

what have your tried so far to save one record... please use improve question and add you code blocks..

ZurdoDev 27-Mar-14 10:48am

Call INSERT INTO 3 times, or UPDATE 3 times. Where are you stuck?

agent_kruger 27-Mar-14 15:59pm

no sir, on one connection i want to save as many record as i want indirectly it will save the time to make connections again and again.

ZurdoDev 27-Mar-14 16:04pm

Yes. You can do as many insert statements as you want in a single connection. Otherwise, what's your plan?

agent_kruger 28-Mar-14 4:00am

sir, my databse is ACCESS can i do it now?

ZurdoDev 28-Mar-14 7:19am

I believe so. If it does not allow batching like that you can always open the connection and then do a loop and submit multiple insert statements and then close the connection.

[no name] 27-Mar-14 16:09pm

First you need to figure the difference between a connection and query. http://stackoverflow.com/questions/62504/is-there-any-way-to-create-multiple-insert-statements-in-a-ms-access-query

agent_kruger 28-Mar-14 4:04am

Sir, i know the difference between query and connection just wrote it the wrong way (updated it)

3 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)

ZurdoDev · Answer 1 · 2014-03-28T01:22:00

Solution 2

I don't think Access supports batch statements, meaning INSERT ... followed by another INSERT ... So, all you need to do is open your connection and then submit the sql statements one after the other or depending on what you have you can do them in a loop. Then when you are done you close the connection.

Posted 28-Mar-14 1:22am

ZurdoDev

Comments

agent_kruger 28-Mar-14 8:47am

no sir, it wont make a BIG difference as the request is send again and again to the database.

ZurdoDev 28-Mar-14 8:49am

You're right. The request is sent again and again but the connection is left open. That's the best you can do. Access does not support batch statements.

Especially if you are talking about only 3 statements I doubt you would even be able to notice the difference anyway.

agent_kruger 28-Mar-14 8:52am

ok sir.

agent_kruger 28-Mar-14 8:49am

and sir, i also know that unlike sql it cannot execute many statement in a single go but thought that i would get some alternate for this here

Mr.VJ · Answer 2 · 2014-03-28T21:19:00

see here sample example which i used in my project works well.

change your column values whichever you want

C#

cmdd.CommandText = "INSERT into addcontact([addusername],[addimage],[addimagename],[addname],[addbirthday],[addgender],[addrelation],[addemail],[addnumber1],[addnumber2],[addnumber3],[addnumber4],[addaddress1]) VALUES(@addusername,@addimage,@addimagename,@addname,@addbirthday,@addgender,@addrelation,@addemail,@addnumber1,@addnumber2,@addnumber3,@addnumber4,@addaddress1)";

cmdd.Connection = conn;

            cmdd.Parameters.AddWithValue("@addusername", loginusername);
            cmdd.Parameters.AddWithValue("@addimage", imgByteArr);
            cmdd.Parameters.AddWithValue("@addimagename", te);
            cmdd.Parameters.AddWithValue("@addname", txtnamecontact.Text);
            cmdd.Parameters.AddWithValue("@addbirthday", datt);
            cmdd.Parameters.AddWithValue("@addgender", rad);
            cmdd.Parameters.AddWithValue("@addrelation", comboBox1.Text);
            cmdd.Parameters.AddWithValue("@addemail", txtemail.Text);
            cmdd.Parameters.AddWithValue("@addnumber1", txtnum1.Text);
            cmdd.Parameters.AddWithValue("@addnumber2", txtnum2.Text);
            cmdd.Parameters.AddWithValue("@addnumber3", txtnum3.Text);
            cmdd.Parameters.AddWithValue("@addnumber4", txtnum4.Text);
            cmdd.Parameters.AddWithValue("@addaddress1", txtadd1.Text);
            conn.Open();
            cmdd.ExecuteNonQuery();
            conn.Close();            
            System.Windows.Forms.MessageBox.Show("inserted sucessfully", "Success");

OriginalGriff · Answer 3 · 2014-03-27T12:00:00

Solution 1

There are a couple of ways to do this: the simplest is:

INSERT INTO MyTable (ColumnOne, ColumnTwo)
VALUES (1,2), (3,4), (5,6)

Posted 27-Mar-14 12:00pm

OriginalGriff

Comments

agent_kruger 28-Mar-14 4:03am

sir, my database is MS ACCESS not SQL

Save multiple records in one connection in access in c# win. form

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us