Running parameterised queries on multiple select statments

Question

I have got a piece of code that is being used to update a Foxpro table with new data, however in order to get this data I need to run more than one select statement. My theory on how to do this was to use parameterised queries, however I am now receiving the error

Index was outside the bounds of the array

I assume this is because I have multiple SELECT statements, however I cannot run this with a join as there is no link between these tables.

The below is the connection string and original Select statement

using (var importConnection = new OleDbConnection(
           connectionString: @"Provider=vfpoledb.1;
           Exclusive=false;
           data source=C:\Users\Joshua.cameron\Desktop\PCHomesImportTestBlank\PCHomesServer\DATABASE\pchomes.dbc")
      )
        using (OleDbCommand CodeChange = new OleDbCommand(
               @"select NUM        
                 from SYSTEMNUMBERS; 
                 select PROPCODE from PROPERTY order by PROPCODE", importConnection))

And the code to call and update.

importConnection.Open();

            Console.WriteLine("Visual Foxpro connection open");

            // Initiate the reader to SQL
            var exportReader = CodeChange.ExecuteReader();

            // Start reading
            while (exportReader != null && exportReader.Read())
            {
                // Set parameter values whilst reading from SQL
                Int32 currentNum = Int32.Parse(exportReader.GetInt32(0).ToString());
                string propcode = exportReader.GetValue(1).ToString();
                currentNum = currentNum + 1;
                string padprop = currentNum.ToString().PadLeft(3);
                string propcode2 = "BIDME_" + padprop;
                // insert into VFP
                var propins = new OleDbCommand(@"update PROPERTY set PROPCODE=" + propcode2 + "where PROPCODE=" + propcode);
                var clientins = new OleDbCommand(@"update CLIENT set PROPCODET="+ propcode2 + "where PROPCODET=" + propcode);
                try
                {
                    propins.ExecuteNonQuery();
                }
                catch (Exception p)
                {
                    Console.Write("Error!");
                    Console.Write(p);
                    Console.Read();
                }
                try
                {
                    clientins.ExecuteNonQuery();
                }
                catch (Exception c)
                {
                    Console.Write("Error!");
                    Console.Write(c);
                    Console.Read();
                }


                try
                {
                    CodeChange.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.Write("Error Writing to database");
                    Console.Write(e);
                    Console.ReadKey();
                }
            }

            // done
            Console.WriteLine("Complete!");
            importConnection.Close();
        }

Answer 1

The exact cause of your error is the line that tries to read the field at index 1 of the DataReader. You seem to assume that you have two fields because you have two selects. But this is not how the OleDbDataReader works. Your command produces two different set of data each one having only one field. The first select produces your first result and this is the set that you are looping on.

You can't join the two results together and use their values in the same loop. You first need to consume all the first result and then pass to the second one using the NextResult method of the OleDbDataReader and start another loop calling Read().

WARNING I am not sure if the visual-foxpro provider supports multiple select statement in the same command. If not then you don't have any choice but emit two separate commands.

However, looking at your code, it seems that you have the same number of records for each table and with no apparent relationships between the two tables.
In this scenario (and assuming that you don't have large resultsets to process) I could simply load two datatables and then work on your updates using the DataRows of the two tables

using (var importConnection = new OleDbConnection(....))
using (OleDbCommand CodeChange = new OleDbCommand(
   @"select NUM from SYSTEMNUMBERS; 
    select PROPCODE from PROPERTY order by PROPCODE", importConnection))
{
    importConnection.Open();
    DataTable sysNum = new DataTable();
    DataTable props = new DataData();
    Console.WriteLine("Visual Foxpro connection open");
    var exportReader = CodeChange.ExecuteReader();
    sysNum.Load(exportReader);
    exportReader.NextResult();
    props.Load(exportReader);
    for (int x = 0; x < sysNum.Rows.Count; x++)
    {
        // Set parameter values whilst reading from SQL
        Int32 currentNum = Int32.Parse(sysNum.Rows[i][0]);
        string propcode = props.Rows[i][0].ToString();

        .... continue with your current code ....
        .... but remove this part.....
//            try
//            {
//                CodeChange.ExecuteNonQuery();
//            }
//            catch (Exception e)
//            {
//                Console.Write("Error Writing to database");
//                Console.Write(e);
//                Console.ReadKey();
//            }


    }
}

// done
Console.WriteLine("Complete!");