15

I'm struggling. I have query against my db that returns a single column of data and I need to set it as List. Here is what I am working with and I am getting an error about converting void to string.

public static void GetImportedFileList()
    {
        using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\Documents and Settings\js91162\Desktop\CMMData.db3"))
        {
            connect.Open();
            using (SQLiteCommand fmd = connect.CreateCommand())
            {
                SQLiteCommand sqlComm = new SQLiteCommand(@"SELECT DISTINCT FileName FROM Import");
                SQLiteDataReader r = sqlComm.ExecuteReader();
                while (r.Read()) 
                {
                    string FileNames = (string)r["FileName"];

                    List<string> ImportedFiles = new List<string>();                        
                }                    

                connect.Close();
        }
    }
}

Then later in application

List<string> ImportedFiles = GetImportedFileList() // Method that gets the list of files from the db 
foreach (string file in files.Where(fl => !ImportedFiles.Contains(fl)))
Improve this question
3
  • Why are you repeatedly creating empty List<string> objects? Commented May 26, 2010 at 6:17
  • man, i wish i knew.. im lost. I need the return of that query to be in a list that I can reference in the second portion of the code i posted. Commented May 26, 2010 at 6:19
  • @jakesankey Ok, so to give you a push in the right direction, you want to only create the new List<string>() once, then do List.Add for each name you read back, then of course at the end of the method, you want to return your list. Commented May 26, 2010 at 6:22

4 Answers 4

Reset to default
34 
public static List<string> GetImportedFileList(){
    List<string> ImportedFiles = new List<string>();
    using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\Documents and Settings\js91162\Desktop\CMMData.db3")){
        connect.Open();
        using (SQLiteCommand fmd = connect.CreateCommand()){
            fmd.CommandText = @"SELECT DISTINCT FileName FROM Import";
            fmd.CommandType = CommandType.Text;
            SQLiteDataReader r = fmd.ExecuteReader();
            while (r.Read()){
                ImportedFiles.Add(Convert.ToString(r["FileName"]));
            }
        }
    }
    return ImportedFiles;
}

Things i've amended in your code:

  • Put ImportedFiles in scope of the entire method.
  • No need to call connect.Close(); since the connection object is wrapped in a using block.
  • Use Convert.ToString rather then (String) as the former will handle all datatype conversions to string. I came across this Here

Edit:

You were creating a new command object sqlComm instead of using fmd that was created by the connection object.

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

2 Comments

Thanks.. I am getting the same error as the code below gave me.... "Ok, so this code got me a little further. I am passed that first error, but it points to the SQLiteDataReader r = sqlComm.ExecuteReader(); line and says there is no connection assciated with it??"
NICE! Working like a charm. Thanks for bearing with me!
3

First of all your return type is void. You need to return a List. Another problem is that you initialize the list inside the loop, so in each pass of the loop you have a new list, and whatsmore, you do not add the string in the list. Your code should probably be more like:

public static List<string> GetImportedFileList()
    {
        List<string> ImportedFiles = new List<string>();                        
        using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\Documents and Settings\js91162\Desktop\CMMData.db3"))
        {
            connect.Open();
            using (SQLiteCommand fmd = connect.CreateCommand())
            {
                fmd.CommandText = @"SELECT DISTINCT FileName FROM Import";
                SQLiteDataReader r = fmd.ExecuteReader();
                while (r.Read()) 
                {
                    string FileNames = (string)r["FileName"];

                    ImportedFiles.Add(FileNames);
                }                    

                connect.Close();
        }
    }
    return ImportedFiles;
}
Improve this answer

2 Comments

Ok, so this code got me a little further. I am passed that first error, but it points to the SQLiteDataReader r = sqlComm.ExecuteReader(); line and says there is no connection assciated with it??
@jakesankey I edited the code, and it should work now. The problem was that you were using to SQLiteCommands and you didn't associate the connection to the command executing the reader.
0

The error is about the return type of your method. You are returning void (public staticvoid) but then use it later as if it were returning a List<string>.

I think you intended the following method signature:

public static List<string> GetImportedFileList()
Improve this answer

Comments

0

You already have a way to get the individual results, so to me it looks like you just need to:

Move the List<string> ImportedFiles = new List<string>(); outside the while loop.

Call ImportedFiles.Add(FileNames); inside the loop (after that string gets assigned).

return ImportedFiles at the end.

Change the return type to List<string>.

Improve this answer

1 Comment

So then in the second portion of my example code how do I then get that list for the foreach statement?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.