Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to reuse Cursor Number? - C# / Gupta sqlbase 9

Status
Not open for further replies.

steve4king

IS-IT--Management
Feb 6, 2007
154
US
I know I could handle this with a large sql command or SQL procedure, but I want to make a more robust process that can accurately report progress and support cancellation.

Unless I close the 2nd sqlbase connection after each row read from the first connection, it will assign a new cursor for the query within while(reader.read()) loop.
I've handled this in the code below, but disconnecting doesn't seem the right way to handle this.

Any tips?

Code:
     private void dostuff()
        {
            bool bError = false;
            string sErrorMessages = "";
            int iCount = 0;
            int iTotal = 18000; //test value
            string sID = "";
            string sDts = "";
            string sGetID = "select id, DTS from tableA";
            string sGetMinDTS = "select min(DTS) from TableB where id = ";
            OleDbConnection conn1 = new OleDbConnection("Provider=SQLBaseOLEDB;Data Source=DBNAME;User ID=admin;Password=myPass;");
            OleDbConnection conn2 = new OleDbConnection("Provider=SQLBaseOLEDB;Data Source=DBNAME;User ID=admin;Password=myPass;");
            conn1.Open();

            try
            {
                OleDbCommand commit = new OleDbCommand("commit", conn2);
                OleDbCommand comGetID = new OleDbCommand(sGetID, conn1);


                OleDbDataReader reader = comGetID.ExecuteReader();

                while (reader.Read())
                {

                    progressBar1.Value = Convert.ToInt32(iCount * (100.0 / iTotal));
                    iCount++;

                    sID = reader[0].ToString();
                    sDts = reader[1].ToString();

                    OleDbCommand comGetMinID = new OleDbCommand(sGetMinDTS + sID, conn2);
                    OleDbDataReader reader2 = comGetMinID.ExecuteReader();
                    conn2.Open();
                    while (reader2.Read())
                    {
                        if (reader2[0].ToString().Length > 0)
                        {
                            if (Convert.ToDateTime(reader2[0].ToString()) < Convert.ToDateTime(sDts))
                            {
                                OleDbCommand upd = new OleDbCommand("update tableB set dts = " + reader2[0] + "where id = '" + sID + "'", conn2);
                                upd.ExecuteNonQuery();
                            }
                        }
                    
                    }
                    conn2.Close();

                }
            }
            catch (OleDbException e)
            {

                sErrorMessages = "";
                for (int i = 0; i < e.Errors.Count; i++)
                {
                    sErrorMessages +=
                                     "ErrCount# " + (i + 1) + "\n" +
                                     "Message: " + e.Errors[i].Message + "\n" +
                                     "NativeError: " + e.Errors[i].NativeError + "\n" +
                                     "Source: " + e.Errors[i].Source + "\n" +
                                     "SQLState: " + e.Errors[i].SQLState + "\n\n" + sGetID
                    ;
                    bError = true;
                }
            }
        }

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top