C# insert

Question

hi,

i m the beginner

i want to create one programe in C# with ms-access database

in fornt end there is one text box one commmad button and label.

in database there are 3 columms empno, name, empcard id (primary key for empid)

if insert 100 in text it should add 100 emp record in databas(empno, name, empcard id).

before insert eg. 1, abc1, xyz1

after inserting 100 in text box eg. 100, abc100, xyz100

plz help me step by step

Answer 1

I would show you code snippet with comments step by step:

     int maxint;
        string begin1 = " Rocky";
        string begin2 = "Yue";

        private void btnInsert_Click(object sender, EventArgs e)
        {
             using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\v-zyue\Documents\test.accdb;Persist Security Info=False;")/* Connection string, you can get it from  http://connectionstrings.com */)
            {
                con.Open();//open the data base.
                
                maxint = int.Parse(this.textBox1.Text);//get the number of the textBox as the max insert number
                for (int i = 1; i <= maxint; i++)
                {
                    OleDbCommand cmd = new OleDbCommand(@"insert into empTable values(@ID,@name, @empno)", con);//command strings with three parameters.
                    OleDbParameter[] plist = new OleDbParameter[3];//add three parameters, we must pay attention at the order in the database.
                    plist[0] = new OleDbParameter("@ID", i);// assign value to ID.
                    plist[1] = new OleDbParameter("@name", begin1 + i);//assign value to name
                    plist[2] = new OleDbParameter("empno", begin2 + i);//assign value to empno
                    cmd.Parameters.AddRange(plist);// add the parameters to the command.
                    cmd.ExecuteNonQuery();//execute the command. 
                }

                con.Close();//close the data base.
            }
        }

And reference:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery.aspx

http://msdn.microsoft.com/en-us/library/h43ks021(v=vs.71).aspx

Hope it helps.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 2

Just curious... do you already have rows inserted in the database? You onl want to change the number after abc and infront of xyz?

I didnt really understand the whole concept of yours? I can see you want to insert 100 rows, but where come abc and xyz then from?

---

Mitja

Answer 3

The database is empty. Because the ID is primary key, it cannot be duplicate. If the data base is not empty, we need handle hte ID number. So for simple example, I just create a new database with 3 columns, which contain a ID as PK.

I change the abc as Rocky and xyz as Yue. :)

So what's your idea about this issue? Could you share it with us?

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 4

I have just CREATED row .(empno, name, empcard id (primary key for empid)

In that there no record.

Now if i entered (e.g 100) in textbox (C#).

so if should the record in access (1,2,3.......100 in empno columm) and it also contain 100 emp name and empcard (eg. abc1,abc2,abc3.....abc100 emp name) and empcardid (eg. xyz1,xyz2.....xyz100)

Answer 5

You can use my code snippet with changing the command string and adding a new parameter to achieve your goal.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 6

IT is giving ERROR cmd.ExecuteNonQuery();Invalid operational was Unhandled

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
            myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = " ABC";
        string begin2 = "XYZ";




        private void btn_strings_Click(object sender, EventArgs e)
        {


            // using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb;Persist Security Info=False;")) // Connection string, you can get it from  http://connectionstrings.com */


            //  con.Open();   //open the data base.


            maxint = int.Parse(this.EmployeeID.Text);//get the number of the textBox as the max insert number
            for (int i = 1; i <= maxint; i++)
            {
                OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@ID,@name, @empno), con"); //command strings with three parameters.




                myCon.Close();


                string firstName;
                string messageText;
                messageText = "Adding Record # ";
                firstName = EmployeeID.Text;
                TextMessage.Text = messageText + firstName;
                Close();


                OleDbParameter[] plist = new OleDbParameter[3];//add three parameters, we must pay attention at the order in the database.

                plist[0] = new OleDbParameter("EmployeeID", i);// assign value to ID.
                plist[1] = new OleDbParameter("EmployeeName", begin1 + i);//assign value to name
                plist[2] = new OleDbParameter("CardNumber", begin2 + i);//assign value to empno




                cmd.Parameters.AddRange(plist);// add the parameters to the command.
                cmd.ExecuteNonQuery();






                // cmd.ExecuteNonQuery(); //execute the command. 
              //  myCon.Close();


            }


            //       // myCon.Close();  //close the data base.


        }










        // string A;
        // string X;


        //  X = "Your name is: ";


        //  A = txtbox.Text;


        //  TextMessage1.Text = X + A;
        //   
















        // ODBC -- Exclusive Use


    }
}

              

Answer 7

The command string should be like this:

  OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeID,@EmployeeName, @CardNumber), con"); 

And the cmd parameters add values should be the followings:

plist[0] = new OleDbParameter("@EmployeeID", i);// assign value to ID.
                 plist[1] = new OleDbParameter("@EmployeeName", begin1 + i);//assign value to name
                 plist[2] = new OleDbParameter("@CardNumber", begin2 + i);//assign value to empno
 

PS: We must keep the same order and strings when we  assign the values to the parameters.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 8

Now it's giving ExecuteNonQuery: Connection property has not been initialized. Error

Answer 9

I think you need put this line of code before you execute the command.

 con.Open();   //open the data base.
 

In C#, if we work with a database, the general steps are the below:

1. Initialize the connection with a connection string

2. Open the database

3. Create the instance of the command

4. Execute the command

5. Close the database.

Hope it helps.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 10

Thanks for Your Hep.

I made simple program which insert the textbox value in database. With Same Database adding (Emp Name and Card ID) i applied above code but it is not getting:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
            myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }




        private void btn_strings_Click(object sender, EventArgs e)
        {
            OleDbCommand cmd = new OleDbCommand();
            myCon.Open();
            cmd.Connection = myCon;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO entry ([EmployeeID]) Values ( '" + EmployeeID.Text + "')";




            cmd.ExecuteNonQuery();


            myCon.Close();


            string firstName;
            string messageText;
            messageText = "Adding Record # ";
            firstName = EmployeeID.Text;
            TextMessage.Text = messageText + firstName;
             Close();




        }








        // string A;
        // string X;


        //  X = "Your name is: ";


        //  A = txtbox.Text;


        //  TextMessage1.Text = X + A;
        //   
















        // ODBC -- Exclusive Use
    }
}

Answer 11

I think it may be the datatable has a PK column, which is not null value. So please make sure the not null column has a value when you do the insert job.

For me, the above code you showed works well on my machine. The datatable design is with a auto number PK and a number column.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 12

 

Thanks Again. EmpoyeeID Having Primary key and it's autonumber.

Still it is giving ERROR cmd.ExecuteNonQuery();Invalid operational was Unhandled

               

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
     //   private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
        //   myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = " Rocky";
        string begin2 = "Yue";




        private void btn_strings_Click(object sender, EventArgs e)
        {


            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb;Persist Security Info=False;")) // Connection string, you can get it from  http://connectionstrings.com */
                con.Open();
          //  myCon.Open();  //open the data base.


            maxint = int.Parse(this.EmployeeID.Text);//get the number of the textBox as the max insert number
            for (int i = 1; i <= maxint; i++)
            {
              // OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@ID,@name, @empno), con"); //command strings with three parameters.
            OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeID,@EmployeeName, @CardNumber), con"); 


                //cmd.ExecuteNonQuery();
                //myCon.Close();
                OleDbParameter[] plist = new OleDbParameter[3];//add three parameters, we must pay attention at the order in the database.


              //  plist[0] = new OleDbParameter("EmployeeID", i);// assign value to ID.
              //  plist[1] = new OleDbParameter("EmployeeName", begin1 + i);//assign value to name
              //  plist[2] = new OleDbParameter("CardNumber", begin2 + i);//assign value to empno


                plist[0] = new OleDbParameter("@EmployeeID", i);// assign value to ID.
                plist[1] = new OleDbParameter("@EmployeeName", begin1 + i);//assign value to name
                plist[2] = new OleDbParameter("@CardNumber", begin2 + i);//assign value to empno


                cmd.Parameters.AddRange(plist);// add the parameters to the command.


cmd.ExecuteNonQuery(); //execute the command. Here it is showing

 ERROR cmd.ExecuteNonQuery();Invalid operational was Unhandled
            //    myCon.Close();


            }


            //       // myCon.Close();  //close the data base.


        }










        // string A;
        // string X;


        //  X = "Your name is: ";


        //  A = txtbox.Text;


        //  TextMessage1.Text = X + A;
        //   
















        // ODBC -- Exclusive Use


    }
}

Answer 13

If the EmpoyeeID  is PK with auto number, we can ignore it when we inset values to the table.

           OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeID,@EmployeeName, @CardNumber), con"); 
                                OleDbParameter[] plist = new OleDbParameter[2];//add three parameters, we must pay attention at the order in the database.
                              plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);//assign value to name
                 plist[1] = new OleDbParameter("@CardNumber", begin2 + i);//assign value to empno
 

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 14

IT's still giving same error in the cmd.ExecuteNonQuery();

Code after Changing:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
       private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
        //   myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = " Rocky";
        string begin2 = "Yue";




        private void btn_strings_Click(object sender, EventArgs e)
        {


            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb;Persist Security Info=False;")) // Connection string, you can get it from  http://connectionstrings.com */
                con.Open();
          //  myCon.Open();  //open the data base.


            maxint = int.Parse(this.EmployeeID.Text);//get the number of the textBox as the max insert number
            for (int i = 1; i <= maxint; i++)
            {
              // OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@ID,@name, @empno), con"); //command strings with three parameters.
           // OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeID,@EmployeeName, @CardNumber), con"); 


                //cmd.ExecuteNonQuery();
                //myCon.Close();
             //   OleDbParameter[] plist = new OleDbParameter[3];//add three parameters, we must pay attention at the order in the database.


              //  plist[0] = new OleDbParameter("EmployeeID", i);// assign value to ID.
              //  plist[1] = new OleDbParameter("EmployeeName", begin1 + i);//assign value to name
              //  plist[2] = new OleDbParameter("CardNumber", begin2 + i);//assign value to empno


             //   plist[0] = new OleDbParameter("@EmployeeID", i);// assign value to ID.
             //   plist[1] = new OleDbParameter("@EmployeeName", begin1 + i);//assign value to name
              //  plist[2] = new OleDbParameter("@CardNumber", begin2 + i);//assign value to empno


                OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeID,@EmployeeName, @CardNumber), con");
                OleDbParameter[] plist = new OleDbParameter[2];//add three parameters, we must pay attention at the order in the database.
                plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);//assign value to name
                plist[1] = new OleDbParameter("@CardNumber", begin2 + i);//assign value to empno


                cmd.Parameters.AddRange(plist);// add the parameters to the command.


               cmd.ExecuteNonQuery();

//execute the command. Here it is showing

 ERROR cmd.ExecuteNonQuery();Invalid operational was Unhandled

//execute the command. 
               myCon.Close();


            }


            //       // myCon.Close();  //close the data base.


        }










        // string A;
        // string X;


        //  X = "Your name is: ";


        //  A = txtbox.Text;


        //  TextMessage1.Text = X + A;
        //   
















        // ODBC -- Exclusive Use


    }
}

Answer 15

My fault, I forgot to change the command string.

    OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeName, @CardNumber), con");
 

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 16

IT's still giving same error in the cmd.ExecuteNonQuery();

Code after Changing: 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
       private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
        //  myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = " Rocky";
        string begin2 = "Yue";




        private void btn_strings_Click(object sender, EventArgs e)
        {


            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb;Persist Security Info=False;")) // Connection string, you can get it from  http://connectionstrings.com */
                con.Open();
          //  myCon.Open();  //open the data base.


            maxint = int.Parse(this.EmployeeID.Text);//get the number of the textBox as the max insert number
            for (int i = 1; i <= maxint; i++)
            {
              // OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@ID,@name, @empno), con"); //command strings with three parameters.
           // OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeID,@EmployeeName, @CardNumber), con"); 


                //cmd.ExecuteNonQuery();
                //myCon.Close();
             //   OleDbParameter[] plist = new OleDbParameter[3];//add three parameters, we must pay attention at the order in the database.


              //  plist[0] = new OleDbParameter("EmployeeID", i);// assign value to ID.
              //  plist[1] = new OleDbParameter("EmployeeName", begin1 + i);//assign value to name
              //  plist[2] = new OleDbParameter("CardNumber", begin2 + i);//assign value to empno


             //   plist[0] = new OleDbParameter("@EmployeeID", i);// assign value to ID.
             //   plist[1] = new OleDbParameter("@EmployeeName", begin1 + i);//assign value to name
              //  plist[2] = new OleDbParameter("@CardNumber", begin2 + i);//assign value to empno


                OleDbCommand cmd = new OleDbCommand(@"insert into entry values(@EmployeeName, @CardNumber), con");
                OleDbParameter[] plist = new OleDbParameter[2];//add three parameters, we must pay attention at the order in the database.
                plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);//assign value to name
                plist[1] = new OleDbParameter("@CardNumber", begin2 + i);//assign value to empno


                cmd.Parameters.AddRange(plist);// add the parameters to the command.


                cmd.ExecuteNonQuery();   //execute the command. 
              
              //  myCon.Close();
            }
            
            myCon.Close();  //close the data base.


        }










        // string A;
        // string X;


        //  X = "Your name is: ";


        //  A = txtbox.Text;


        //  TextMessage1.Text = X + A;
        //   
















        // ODBC -- Exclusive Use


    }
}

Answer 17

OK, please send me an email with a test application and a access database you used.

[e-mail]

I will help you to find a solution.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 18

Sorry i am at workplace.

Mail website are block. Can u help me out from this manner please.

Answer 19

OK, Try the following code snippet please:

        private void btn_strings_Click(object sender, EventArgs e)
        {
            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb;;Persist Security Info=False;"))
            {
                con.Open();
                maxint = int.Parse(this.EmployeeID.Text);
                for (int i = 1; i <= maxint; i++)
                {

                    OleDbCommand cmd = new OleDbCommand(@"insert into entry([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                    OleDbParameter[] plist = new OleDbParameter[2];
                    plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                    plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                    cmd.Parameters.AddRange(plist);
                    cmd.ExecuteNonQuery();


                }

                con.Close();
            }
        }

Hope it helps.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 20

IT's still giving same error in the cmd.ExecuteNonQuery();

Code after Changing:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
            // myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = " Rocky";
        string begin2 = "Yue";




        private void btn_strings_Click(object sender, EventArgs e)
        {
   using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb;;Persist Security Info=False;"))
            {
                con.Open();
                maxint = int.Parse(this.EmployeeID.Text);
                for (int i = 1; i <= maxint; i++)
                {


                    OleDbCommand cmd = new OleDbCommand(@"insert into entry([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                    OleDbParameter[] plist = new OleDbParameter[2];
                    plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                    plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                    cmd.Parameters.AddRange(plist);
                    
                   cmd.ExecuteNonQuery();




                }


                con.Close();
            }            }


            //            myCon.Close();  //close the data base.


        }










        // string A;
        // string X;


        //  X = "Your name is: ";


        //  A = txtbox.Text;


        //  TextMessage1.Text = X + A;
        //   
















        // ODBC -- Exclusive Use


    }

 

Answer 21

Could you share how you design the database? step by step please.

It's really strange what happened to you.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 22

Database is created in MS-Access 2007 

Database Name is record 

in that Table name is entry

Design View:

Primary Key 1)Employee ID Autonumber

2) Employee Name Text

3) Card Number Number

Answer 23

OK, I think I know what happened.

Your CardNumber is number type, but we insert string value into it. Try to change it as Text type or change the insert value as a number type.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 24

I got it.... Thanks for your such a great help.... But after double click on add it is still giving the same error cmd.ExecuteNonQuery(); but in database it is going.

And more thing i wht 2 add. That i have label in the form. i want see how it is adding in the database. (Eg. i insert 100 in the text box. in the same form there label button. in label it should show 1,2,....100).

Please help

Answer 25

Is this your job? which SB Boss would let you to do this work?

Could you think it yourself?

---

Lucky Dog

Answer 26

You can set a breakPoint at the for loop, and press F5 to see what happened.

Have a nice night.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 27

There is one more problem in that.

It is updating the EmployeeId from max no. but it is not updating EmployeeName and Cardid.

Example(If i insert 100 It's show's EmployeeId(1,2,...100) , EmployeeName(Rocky1,Rocky2,.... Rocky100), CardID(Yue1,Yue,2.... Yue100))

and if i again insert 100 it is showing 

EmployeeId(101,102,...200) , EmployeeName(Rocky1,Rocky2,.... Rocky100), CardID(Yue1,Yue,2.... Yue100)

But should Show

EmployeeId(101,102,...200) , EmployeeName(Rocky101,Rocky102,.... Rocky200), CardID(Yue101,Yue102.... Yue200))

Answer 28

To do that, we always select the MAX ID in the table at first.

  OleDbCommand cmdMax = new OleDbCommand(@"select MAX(ID) from test1", con);
                maxint =int.Parse ( cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);

For some unknow reasons, I think this job seems like your homework. Could you do this yourself first? And if you met some really hard issue you don't know how to reslove, please post it here and we will help you.

But for homework, I think I can't help any more.

Thanks for your understanding and support.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 29

Thanks for your help till yet.

I tried a lot all the thing and if i not get answer i ask you. even that same error md.ExecuteNonQuery(); is till coming. but it is adding the database.

even now also the above case i taken one diffrent variable for a employeename, cardid which will increase the one by one. but it is not getting.

now i applied your above code also.

 OleDbCommand cmdMax = new OleDbCommand(@"select MAX(ID) from test1", con);
                maxint =int.Parse ( cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);

 but is giving error 

The Microsoft Office Access database engine cannot find the input table or query 'Test1'.  Make sure it exists and that its name is spelled correctly.

Answer 30

Sir, Test1 is my data table in my database, yours is entry.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 31

Could you double check the command string to be sure the table name is correct please?

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 32

Sir can explain me Error of

No value given for one or more required parameters.

Answer 33

Post the last code you used please.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 34

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
            // myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = "ABC";
        string begin2 = "XYZ";




        private void btn_strings_Click(object sender, EventArgs e)
        {
            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record1.accdb;;Persist Security Info=False;"))
            {
                con.Open();
                maxint = int.Parse(this.EmployeeID.Text);
                for (int i = 1; i <= maxint; i++)
                {


                    OleDbCommand cmd = new OleDbCommand(@"insert into entry1([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                    OleDbParameter[] plist = new OleDbParameter[2];
                    OleDbCommand cmdMax = new OleDbCommand(@"select MAX(ID) from entry1", con);
                    maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);
                    plist[0] = new OleDbParameter("@EmployeeName", begin1 + maxint);
                    plist[1] = new OleDbParameter("@CardNumber", begin2 + maxint);
                    cmd.Parameters.AddRange(plist);


                    cmd.ExecuteNonQuery();




                }


                con.Close();
            }
        }


        //            myCon.Close();  //close the data base.


    }










    // string A;
    // string X;


    //  X = "Your name is: ";


    //  A = txtbox.Text;


    //  TextMessage1.Text = X + A;
    //   
















    // ODBC -- Exclusive Use


}

Answer 35

    private void btn_strings_Click(object sender, EventArgs e)
         {
             using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record1.accdb;;Persist Security Info=False;"))
             {
                 con.Open();
               OleDbCommand cmdMax = new OleDbCommand(@"select MAX(ID) from entry1", con);
                     maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);
                 for (int i = 1; i <= maxint; i++)
                 {
                    OleDbCommand cmd = new OleDbCommand(@"insert into entry1([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                     OleDbParameter[] plist = new OleDbParameter[2]; 
                     plist[0] = new OleDbParameter("@EmployeeName", begin1 + maxint);
                     plist[1] = new OleDbParameter("@CardNumber", begin2 + maxint);
                     cmd.Parameters.AddRange(plist);
                    cmd.ExecuteNonQuery();
                }
             }
            }

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 36

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
            // myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = "ABC";
        string begin2 = "XYZ";




        private void btn_strings_Click(object sender, EventArgs e)
        {
            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record1.accdb;;Persist Security Info=False;"))
            {
                con.Open();
                OleDbCommand cmdMax = new OleDbCommand(@"select MAX(ID) from entry1", con);
                maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);
                for (int i = 1; i <= maxint; i++)
                {


                    OleDbCommand cmd = new OleDbCommand(@"insert into entry1([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                    OleDbParameter[] plist = new OleDbParameter[2];
                    plist[0] = new OleDbParameter("@EmployeeName", begin1 + maxint);
                    plist[1] = new OleDbParameter("@CardNumber", begin2 + maxint);
                    cmd.Parameters.AddRange(plist);


                    cmd.ExecuteNonQuery();




                }


                con.Close();
            }
        }


        //            myCon.Close();  //close the data base.


    }










    // string A;
    // string X;


    //  X = "Your name is: ";


    //  A = txtbox.Text;


    //  TextMessage1.Text = X + A;
    //   
















    // ODBC -- Exclusive Use


}

Answer 37

You can download my code example from this link:

https://skydrive.live.com/?cid=03e6a6a86c941f49&id=3E6A6A86C941F49%21132

And then check what the difference from yours.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 38

Here also it is giving same error 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Threading;
using System.Diagnostics;




namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        private OleDbConnection myCon;


        public Form1()
        {
            InitializeComponent();
             myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = "ABC";
        string begin2 = "XYZ";




        private void btn_strings_Click(object sender, EventArgs e)
        {
            {
                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record1.accdb;;Persist Security Info=False;"))
                {
                    con.Open();


                    OleDbCommand cmdMax = new OleDbCommand(@"select MAX(ID) from entry1", con);
                    if (cmdMax.ExecuteScalar().ToString() == "") No value given for one or more required parameters.
                    {
                        maxint = int.Parse(this.EmployeeID.Text);
                    }
                    else
                    {
                        maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);
                    }
                    for (int i = 1; i <= maxint; i++)
                    {


                        OleDbCommand cmd = new OleDbCommand(@"insert into entry1([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                        OleDbParameter[] plist = new OleDbParameter[2];
                        plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                        plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                        cmd.Parameters.AddRange(plist);
                        cmd.ExecuteNonQuery();
                       // label2.Text = i.ToString();
                    }


                    con.Close();
                }
            }
        }
    }
 










    // string A;
    // string X;


    //  X = "Your name is: ";


    //  A = txtbox.Text;


    //  TextMessage1.Text = X + A;
    //   
















    // ODBC -- Exclusive Use


}

Answer 39

I got it.

My data table's PK name is ID, I guess yours is EmployeeID. You can try to use the EmployeeID to replace the command string "ID".

From the above replies, I would suggest you to read some materials about C# and database. Something like ADO.NET, C# guid line and so on. When I began to code a project, I almost read C# related books about 180 days, especially the database related. It's really an amazing thing to be a programmer.

So please not to be anxious, every thing will be going on.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 40

Thanks for your guidance i already C# from learn and Home and SQL from W3school. If any more website are there with you please send.

The program is excuting but in database if i insert first time 10. it's show (1,abc1,xyz,1.......10, abc10,xyz10) and if i again insert 10it's shows(30, abc20,xyz20) 

i think it over lapping

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Threading;
using System.Diagnostics;








namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        private OleDbConnection myCon;




        public Form1()
        {
            InitializeComponent();
            myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = "ABC";
        string begin2 = "XYZ";








        private void btn_strings_Click(object sender, EventArgs e)
        {
            {
                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record1.accdb;;Persist Security Info=False;"))
                {
                    con.Open();




                    OleDbCommand cmdMax = new OleDbCommand(@"select MAX(EmployeeID) from entry1", con);
                    if (cmdMax.ExecuteScalar().ToString() == "") 
                    {
                        maxint = int.Parse(this.EmployeeID.Text);
                    }
                    else
                    {
                        maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);
                    }
                    for (int i = 1; i <= maxint; i++)
                    {




                        OleDbCommand cmd = new OleDbCommand(@"insert into entry1([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                        OleDbParameter[] plist = new OleDbParameter[2];
                        plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                        plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                        cmd.Parameters.AddRange(plist);
                        cmd.ExecuteNonQuery();
                       // label2.Text = i.ToString();
                    }




                    con.Close();
                }
            }
        }
    }






















    // string A;
    // string X;




    //  X = "Your name is: ";




    //  A = txtbox.Text;




    //  TextMessage1.Text = X + A;
    //   
































    // ODBC -- Exclusive Use




}

Answer 41

This error is about logic.

When the database is not empty, we first get the Max ID number.

and then we need for loop to insert data, but the for loop always start with i =1, this is why the ID is 30 and the others are 20. 

We need change the logic to inset data when the database is not empty. See the below code snippet:

 OleDbCommand cmdMax = new OleDbCommand(@"select MAX(ID) from test1", con);
                if (cmdMax.ExecuteScalar().ToString() == "")
                {
                    maxint = int.Parse(this.EmployeeID.Text);
                    for (int i = 1; i <= maxint; i++)
                    {

                        OleDbCommand cmd = new OleDbCommand(@"insert into test1([CMD1],[CMD2])  values(@EmployeeName, @CardNumber)", con);
                        OleDbParameter[] plist = new OleDbParameter[2];
                        plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                        plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                        cmd.Parameters.AddRange(plist);
                        cmd.ExecuteNonQuery();
                        label2.Text = i.ToString();
                    }
                }
                else
                {
                    maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);

                    for (int i = int.Parse(cmdMax.ExecuteScalar().ToString()); i <= maxint; i++)
                    {

                        OleDbCommand cmd = new OleDbCommand(@"insert into test1([CMD1],[CMD2])  values(@EmployeeName, @CardNumber)", con);
                        OleDbParameter[] plist = new OleDbParameter[2];
                        plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                        plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                        cmd.Parameters.AddRange(plist);
                        cmd.ExecuteNonQuery();
                        label2.Text = i.ToString();
                    }
                }

I would suggest you to try to find the root cause by yourself, it will be very benefit for improving your tech knowledge and your performance.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 42

Still there one error. i will do thanks for gr8 help.

I will scrap the whole code.

Answer 43

If there is any problem in the future programing in C#, please feel free to let us know.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 44

Sure...

If you have any C# links please scrap me.

Answer 45

It is contaning Last value of Employee Name and CardID.

Answer 46

What's your meaning?

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 47

It is picking the last value of EmployeeName and CardId.

Example: If is insert 5 First time. It will show 

1  abc1     xyz1

.

.

.

5  abc5     xyz5

If i insert again 5 in txtbox

6  abc5     xyz5

.

.

.

.

11  abc10     xyz10

 

Answer 48

It's about the else stament, we just select the max value, but we need max +1 as next begining.

else
                {
                    maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);

                    for (int i = int.Parse(cmdMax.ExecuteScalar().ToString())+1; i <= maxint; i++)
                    {

                        OleDbCommand cmd = new OleDbCommand(@"insert into test1([CMD1],[CMD2])  values(@EmployeeName, @CardNumber)", con);
                        OleDbParameter[] plist = new OleDbParameter[2];
                        plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                        plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                        cmd.Parameters.AddRange(plist);
                        cmd.ExecuteNonQuery();
                        label2.Text = i.ToString();
                    }
                }

Hope it helps.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 49

Source Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Threading;
using System.Diagnostics;








namespace WindowsFormsApplication12
{
    public partial class Form2 : Form
    {
        private OleDbConnection myCon;




        public Form2()
        {
            InitializeComponent();
         //   myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb");
        }
        int maxint;
        string begin1 = "ABC";
        string begin2 = "XYZ";








        private void btn_strings_Click(object sender, EventArgs e)
        {
            {
                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record1.accdb;;Persist Security Info=False;")) I don't want Connection string in Form Window. How to put in APP.Config
                {
                    con.Open();
                    OleDbCommand cmdMax = new OleDbCommand(@"select MAX(EmployeeID) from entry1", con);


                    if (cmdMax.ExecuteScalar().ToString() == "")
                    {
                        maxint = int.Parse(this.EmployeeID.Text);
                        for (int i = 1; i <= maxint; i++)
                        {


                            OleDbCommand cmd = new OleDbCommand(@"insert into entry1([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                            OleDbParameter[] plist = new OleDbParameter[2];
                            plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                            plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                            cmd.Parameters.AddRange(plist);
                            cmd.ExecuteNonQuery();
                            TextMessage.Text = i.ToString();
                        }
                    }
                    else
                    {
                        maxint = int.Parse(cmdMax.ExecuteScalar().ToString()) + int.Parse(this.EmployeeID.Text);


                        for (int i = int.Parse(cmdMax.ExecuteScalar().ToString()) + 1; i <= maxint; i++)
                        {


                            OleDbCommand cmd = new OleDbCommand(@"insert into entry1([EmployeeName],[CardNumber])  values(@EmployeeName, @CardNumber)", con);
                            OleDbParameter[] plist = new OleDbParameter[2];
                            plist[0] = new OleDbParameter("@EmployeeName", begin1 + i);
                            plist[1] = new OleDbParameter("@CardNumber", begin2 + i);
                            cmd.Parameters.AddRange(plist);
                            cmd.ExecuteNonQuery();
                            TextMessage.Text = i.ToString();
                        }
                    }










                    con.Close();
                }
            }
        }
    }






















    // string A;
    // string X;




    //  X = "Your name is: ";




    //  A = txtbox.Text;




    //  TextMessage1.Text = X + A;
    //   
































    // ODBC -- Exclusive Use




}

AppConfig:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>


  <connectionStrings>
    <add name="myConn" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source =D:\Home\Sanket\D Backup\Home\Sanket\ABC Project\record.accdb"/> (Above Comment Connection String)
    
    
  </connectionStrings>
</configuration>

Answer 50

I've looked through your new problem and it's not directly related to the original issue. Thus it would be better if you open up a new thread for the new question. In this way, our discussion here will not deviate too much from the original issue.

Thank you for your understanding.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us