CODEWITHSUNDEEP
c#sqldataset
Stick
Stick

Reputation: 123

Getting error while looping through dataset

I'm using VS 2012 C#. I'm trying to loop through datasets to get information from an Access database. I have a combobox that when an item is selected it should print out all the data for that given scenario. For some the the values in the combobox I get the error, "The SelectCommand property has not been initialized before calling 'Fill'." But for some of the other values I don't and the information is retrieved without an issue. Here is all the necessary code,

con2.Open();
ad2 = new OleDbDataAdapter(query, con2);
ad2.Fill(ds2, "AC_SCENARIO");
con2.Close()

try
{
    string end = "ENDDATE";
    string start = "START";

    foreach (DataRow drRow in ds2.Tables[0].Rows)
    {
        for (int i = 0; i <= ds2.Tables[0].Columns.Count; i++)
        {
            string qual0 = ds2.Tables["AC_SCENARIO"].Rows[i]["QUAL0"].ToString();
            string qual1 = ds2.Tables["AC_SCENARIO"].Rows[i]["QUAL1"].ToString();
            string qual2 = ds2.Tables["AC_SCENARIO"].Rows[i]["QUAL2"].ToString();
            string qual3 = ds2.Tables["AC_SCENARIO"].Rows[i]["QUAL3"].ToString();
            string qual4 = ds2.Tables["AC_SCENARIO"].Rows[i]["QUAL4"].ToString();

            //HERE IS A SAMPLE QUERY 
            if (qual0 != null && (string)comboBox1.SelectedItem == qual0)
            {
                ad.SelectCommand = new OleDbCommand("SELECT b.RSV_CAT, b.SEQNUM, b.LEASE,  b.WELL_ID, a.QUALIFIER, a.KEYWORD, a.EXPRESSION FROM [AC_ECONOMIC] a INNER JOIN [AC_PROPERTY] b on a.PROPNUM=b.PROPNUM WHERE a.KEYWORD = '" 
                + end + "' AND (a.QUALIFIER = '" + qual0 + "' OR a.QUALIFIER IS NULL) AND NOT a.EXPRESSION Like '%[/@]%'", con);
            }

            ds.Clear();
            ad.Fill(ds); //The SelectCommand property has not been initialized before calling 'Fill'. 
            //ERROR OCCURS HERE

            con.Open();
            ad.SelectCommand.ExecuteNonQuery();
            con.Close();

EDIT: HERE IS THE con CODE

        String filePath = textBox1.Text;

        con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath);

I have more queries such as those listed for another dataset and then I merge the two datasets together and output them to a datagridview. The error occurs on the line ad.Fill(ds); If anyone can explain this issue to me or has any help that would be great.

Upvotes: 0

Views: 698

Answers (3)

Ashley John
Ashley John

Reputation: 2453

the problem is probably here 

 if (qual0 != null && (string)comboBox1.SelectedItem == qual0)

In some scenarios the if loop turns out to be true and ad.SelectCommand will get initialized.

EDIT Are you sure the Select Command is forming correctly for every values of qual0. Please try using named parameters while forming the query . the qual variable might be having some ' in it that prevent your query getting formed correctly..

EDIT 

qual0.Replace("'","\"");

Upvotes: 1

Sean Airey
Sean Airey

Reputation: 6372

You've not provided a default case for if qual0 is null.

I'm going to assume that you do this multiple times and it's not just the one qual variable you check and build a statement for, if that's the case then you might be better off refactoring to something like this:

if (qual0 != null && (string)comboBox1.SelectedItem == qual0)
{
    ad.SelectCommand = new OleDbCommand("SELECT b.RSV_CAT, b.SEQNUM, b.LEASE,  b.WELL_ID, a.QUALIFIER, a.KEYWORD, a.EXPRESSION FROM [AC_ECONOMIC] a INNER JOIN [AC_PROPERTY] b on a.PROPNUM=b.PROPNUM WHERE a.KEYWORD = '" 
                       + end + "' AND (a.QUALIFIER = '" + qual0 + "' OR a.QUALIFIER IS NULL) AND NOT a.EXPRESSION Like '%[/@]%'", con);
}

if (ad.SelectCommand != null)
{
    if (!String.IsNullOrEmpty(ad.SelectCommand.CommandText))
    {
        ds.Clear();
        ad.Fill(ds);
    }
}

Upvotes: 0

Adil
Adil

Reputation: 148110

Columns collection of DataTable is zero based index and is one less then Count, the loop should be one less then number of columns as first column is at 0 index and last column is columns count-1. You can use < instead of <= in loop condition to iterate from zero to Count-1.

Change 

for (int i = 0; i <= ds2.Tables[0].Columns.Count; i++)

To

for (int i = 0; i < ds2.Tables[0].Columns.Count; i++)

Creation of con is also not present in the code you have in OP

Upvotes: 1

Related Questions