Adding large number of rows using SQL

Question

My code works well when I'm adding less than 30 rows. But It can't handle more than that. How can I overcome this?

I get this error:Unspecified error when I'm trying to add less than 30, all the rows are added. and when I'm trying to add more than 30, it doesn't add nothing, and I get the error the number of the rows times.

Here is the code:

                    for (int i = 0; i < st1.Length; i++)
                {
                    UpdateDataBase(st1[i]);
                }

private void UpdateDataBase(char letter)
    {
        letter = char.ToUpper(letter);
        int serialPro = 0;
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                                  "Data Source=C:\\Projects_2012\\Project_Noam\\Access\\myProject.accdb";
        OleDbConnection connection = new OleDbConnection(connectionString);

        string sql = "SELECT tblProInfo.proInfoSerialNum FROM tblProInfo ";
        OleDbCommand command = new OleDbCommand(sql, connection);
        try
        {
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();
        //get the last!
        while (reader.Read())
            serialPro = reader.GetInt32(reader.Depth);


        sql = "INSERT INTO tblOrderAA (orderAASerialPro, orderAACodon1) "
           + " values (?, ?)";
        OleDbCommand command2 = new OleDbCommand(sql, connection);

        command2.CommandType = CommandType.Text;
        command2.Parameters.AddWithValue("orderAASerialPro", serialPro);
        command2.Parameters.AddWithValue("orderAACodon1", letter);
        command2.ExecuteNonQuery();
            }
        catch (Exception e)
        {
            MessageBox.Show("אירעה שגיאה ב: \n" + e.Message);
            this.Close();
        }
    }`

EDIT: private void UpdateDataBase(char letter) { letter = char.ToUpper(letter); int serialPro = 0; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\Projects_2012\Project_Noam\Access\myProject.accdb";

        try
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                string sql = "SELECT tblProInfo.proInfoSerialNum FROM tblProInfo ";
                using (OleDbCommand command = new OleDbCommand(sql, connection))
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    //get the last!
                    while (reader.Read())
                        serialPro = reader.GetInt32(0);
                }

                sql = "INSERT INTO tblOrderAA (orderAASerialPro, orderAACodon1) "
       + " values (?, ?)";
                using (OleDbCommand command2 = new OleDbCommand(sql, connection))
                {
                    command2.CommandType = CommandType.Text;
                    command2.Parameters.AddWithValue("orderAASerialPro", serialPro);
                    command2.Parameters.AddWithValue("orderAACodon1", letter);
                    command2.ExecuteNonQuery();
                }
            }
        }
        catch (Exception e)
        {
            MessageBox.Show("אירעה שגיאה ב: \n" + e.Message);
        }
    }
enter code here

nothing.but when the number of the rows is small, it adds all the rows. — Noam650
– Noam650, Commented May 21, 2012 at 9:00

Steve · Accepted Answer · 2012-05-21 09:53:36Z

1

I don't know for sure, but your OleDbConnection is never closed
Try the using statement which guarantees the closing and disposing of your connection

using(OleDbConnection connection = new OleDbConnection(connectionString))
{
    string sql = "SELECT tblProInfo.proInfoSerialNum FROM tblProInfo ";         
    OleDbCommand command = new OleDbCommand(sql, connection);         
    try 
    {
     ......
    }
    catch(....)
}

also is not clear what your intent is in this line

serialPro = reader.GetInt32(reader.Depth);

GetInt32 takes an int which is the ordinal number of the column required, while reader.Depth gets a value that indicates the depth of nesting for the current row.
I don't understand how the two values are related.

EDIT: In case you are looking for the max value of you serial number you could change the code in this way

string sql = "SELECT MAX(proInfoSerialNum) AS maxSN FROM tblProInfo ";                 
using (OleDbCommand command = new OleDbCommand(sql, connection))                 
{                     
    serialPro = (int)command.ExecuteScalar();                 
}

edited May 21, 2012 at 9:53

answered May 21, 2012 at 8:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Noam650 Over a year ago

see what I wrote in @ThorstenDittmar answer

Noam650 Over a year ago

I fixed that error. SEE my EDIT How my code looks like now. I still get the error :Unspecified error

Steve Over a year ago

Could it be something with the tblOrderAA table. Do you have index or unique keys on your rows and trying to add duplicate records?

Noam650 Over a year ago

I don't think so.it is allowing to write the same number in orderAASerialPro

Steve Over a year ago

I don't see anything wrong. The only thing that could be changed is removing of the reader for the serial number if it is correct to assume that you are looking for the highest value in the table.

Thorsten Dittmar · Accepted Answer · 2012-05-21 08:58:48Z

0

You might start by closing the database connections when you're done and you might get better results. Use the using statement to do so:

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    ...
}

The using statement makes sure that - for objects that implement the IDisposable interface - the Dispose method is called.

Also, the reader you're creating always stays open. So in the end, if rewritten, the method should look as follows:

private void UpdateDataBase(char letter)
{
    letter = char.ToUpper(letter);
    int serialPro = 0;
    string connectionString = "...";

    try
    {
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            connection.Open();

            string sql = "SELECT tblProInfo.proInfoSerialNum FROM tblProInfo ";
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            using (OleDbDataReader reader = command.ExecuteReader())
            {
                //get the last!
                while (reader.Read())
                    serialPro = reader.GetInt32(reader.Depth);
            }

            sql = "INSERT INTO ...";
            using (OleDbCommand command2 = new OleDbCommand(sql, connection))
            {
                command2.CommandType = CommandType.Text;
                command2.Parameters.AddWithValue("orderAASerialPro", serialPro);
                command2.Parameters.AddWithValue("orderAACodon1", letter);
                command2.ExecuteNonQuery();
            }
        }
    }
    catch (Exception e)
    {
        MessageBox.Show("אירעה שגיאה ב: \n" + e.Message);
    }
}

To speed things up: For every call you're selecting all serial numbers and then iterate over the reader to get the latest. Is there an index you can use to put this work into the database? Like: Order by some date or select MAX(proInfoSerialNum)? Otherwise your software will slow down significantly when there are more serial numbers in the database.

edited May 21, 2012 at 8:58

answered May 21, 2012 at 8:52

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

10 Comments

Noam650 Over a year ago

now I get this error:Format of the initialization string does not conform to specification starting at index 0

Thorsten Dittmar Over a year ago

Sounds like the connection string is malformed. Of course I've shortened the text constants a little. Please put some effort into trying to understand the changes instead of simply copying and pasting it.

Noam650 Over a year ago

I fixed that error. SEE my EDIT How my code looks like now. I still get the error :Unspecified error

Thorsten Dittmar Over a year ago

Up to which line are you able to execute the code? Did you debug? Does the Inner Exception show anything more?

Thorsten Dittmar Over a year ago

"to debug" is the process of finding bugs, not just pressing F5. Did you set a breakpoint and single-step through the method to see where the error occurs? Is there any more information about the error itself?

|

Devarajan · Accepted Answer · 2012-06-02 11:22:50Z

0

    void dataread()
    {
        query = "select * from nodes";
        cmd = new SqlCommand(query, con);
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {

            comboBox1.Items.Add(dr[0].ToString());
        }
        dr.Close();
        cmd.Dispose();

    }

answered Jun 2, 2012 at 11:22

Devarajan

1

Collectives™ on Stack Overflow

Adding large number of rows using SQL

3 Answers 3

5 Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related