using SqlCEResultSet.Seek with composite keys in SQLCE

Question

Hi

can someone show me how I can use the SqlCeResultSet.Seek method with a composite index?

I am trying to seek on orderno+product code

my sqlce table has the following index: CREATE INDEX orderline_idx ON OrderLines ( orderno,item )

My seek code is

    public bool SeekDeliveryLine(string delnote,string item)
    {
        bool isFound = false;
        cmd = new SqlCeCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.TableDirect;
        cmd.CommandText = "OrderLines";
        cmd.IndexName = "orderline_idx ";
        try
        {
            //cmd.SetRange(DbRangeOptions.Match, new object[] { delnote }, null);
            deliveryRS = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
            isFound = deliveryRS.Seek(DbSeekOptions.FirstEqual, new object[] { delnote, item });
            if (isFound)
            {
                deliveryRS.Read();
                currentRowData = this.RetrieveRecord();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return isFound;
    }

The code always returns the first matching order line.

Thanks in advance Paul

Answer 1

The seek input data array must be objects that equate to the indexed fields. You cannot seeok on a field that is not in the index. It looks like you have an index on the order number and item, which I'd think is an int and a string, but you're passing in "delnote" and item, which are both strings.

Answer 2

Hi Chris

Thanks for your answer, both fields are strings. I am importing into a backend that stores the order number as a string. The delnote field is just poorly named (I was working on delivery note numbers originally and hadn't refactored the variable name yet). I have now got this code working I re-wrote it as below, I'm not sure why storing the strings into object should make any difference but it is now working.

OT Warning: is SDF2.3 compatable with vs2010? we are upgrading soon.

    public bool SeekOrderLine(object orderNum,object item)
    {
        bool isFound = false;

        cmd = new SqlCeCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.TableDirect;
        cmd.CommandText = "orderLines";
        cmd.IndexName = "orderline_idx";
        try
        {
            orderRS = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
            isFound = orderRS.Seek(DbSeekOptions.FirstEqual, new object[] {orderNum, item});
            if (isFound)
            {
                orderRS.Read();
                currentRowData = this.RetrieveRecord();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return isFound;
    }