lucene empty index fields

[faintfascinatio]

I'm trying to use lucene in my project to search for people.
I created the index using records from a db table of people (firstname, lastname, social, phone, address, etc). However, in the db, some of these fields are missing (ie, no address supplied).

In my project, users can search for a person using fields from the db table/index. But is there a way to exclude search fields where the index value is empty?

For example, the db record is:
fname: John
lname: Doe
social: <empty>

and the user searches using these fields:
fname: John
lname: Doe
social: 123-45-6789

No matches come up because it is matching 123-45-6789 against an empty value. But I would still like John/Doe/<empty> to come up (even if there is more than one record like that)

[Mitja Bonca]

Sure, thats natural that you will not get any results if you stated to look for a user with all three fileds (in a where clause).
If you state "... WHERE name = @name AND last = @last AND social = @social"; the query will always look for results based on all three values - all three values have to be included in the result, if you want to get what you stated in the SELECT statement.

But there are some other ways to ran over it. Maybe you can seperate the query in some parts. The point is that the code 1st checkes (looks) for the data based on all three parameters (name, last and social), if there is none found the code will go through ones again, but with different query.
See the example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Dec27Exercise
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private List<string> GetData(string name, string last, string social)
        {
            List<string> list = new List<string>();
            using (SqlConnection sqlConn = new SqlConnection("YourConnString"))
            {                
                using (SqlCommand cmd = new SqlCommand())
                {
                    bool bContinue = true;
                    while (bContinue)
                    {
                        bool bReapeat = false;
                        string sqlQuery = null;
                        if (bReapeat)
                        {
                            sqlQuery = "SELECT UserName, LastName, Social FROM Users " +
                                       "WHERE UserName = @name AND LastName = @last";
                        }
                        else
                        {
                            sqlQuery = "SELECT UserName, LastName, Social FROM Users " +
                                           "WHERE UserName = @name AND " +
                                           "LastName = @last AND " +
                                           "Social = @social";
                            cmd.Parameters.Add("@social", SqlDbType.VarChar, 50).Value = social;
                        }
                        cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = name;
                        cmd.Parameters.Add("@last", SqlDbType.VarChar, 50).Value = last;
                        cmd.CommandText = sqlQuery;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection=sqlConn;
                        if (sqlConn == null)
                            sqlConn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string _name = (string)reader[0];
                                string _last = (string)reader[1];
                                string _social = (string)reader[2];
                                list.Add(_name + "-" + _last + "-" + _social);
                                //name, plastName and social are seperated by a dash (-)
                                //when you retreive them back from the list, split the string (.Split('-');
                                bContinue = false;
                            }
                        }
                        else
                            bReapeat = true;
                        reader.Close();
                        reader = null;
                    }
                    sqlConn.Close();
                }
            }
            return list;
        }
    }
}

Let me know if it helps,
Mitja

[faintfascinatio]

this is for lucene though--not SQL. but that gave me an idea..I can set my query to -- (fname: John) OR (fname: '')-- or something like that. I'll give this a shot tomorrow

[Mitja Bonca]

I am glad it gave you an idea. The example was meant that way, because you didnt even say what exactly do you want in your 1st post.
Le know know if its in any help, if not I`ll try to do better.

Mitja