CODEWITHSUNDEEP
c#windows-mobilesql-server-cesqlexception
tarheels058
tarheels058

Reputation: 469

SQLServerCE cannot use rtrim in select statement - SQLCEException

I am writing a windows mobile application that uses SQL CE. It is not returning any rows when I include the WHERE Barcode = @Barcode statement.

I am guessing this is because the value for Barcode has trailing spaces after it. So I want to use WHERE rtrim(Barcode) LIKE @Barcode. But it is giving me a SqlCeException saying "The specified argument value for the function is not valid."

I'm sure I am missing something stupid here. Any help greatly appreciated.

Here is my code:

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

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

        private void buttonStart_Click(object sender, EventArgs e)
        {
            System.Data.SqlServerCe.SqlCeConnection conn = new System.Data.SqlServerCe.SqlCeConnection(
            ("Data Source=" + (System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase), "ElectricReading.sdf") + ";Max Database Size=2047")));
            try
            {
                // Connect to the local database
                conn.Open();
                System.Data.SqlServerCe.SqlCeCommand cmd = conn.CreateCommand();

                SqlCeParameter param = new SqlCeParameter();
                param.ParameterName = "@Barcode";
                param.Value = textBarcode.Text.Trim();



                // Insert a row
                cmd.CommandText = "SELECT Location, Reading FROM Main2 WHERE rtrim(Barcode) LIKE @Barcode";
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

                DataTable data = new DataTable();

                using (SqlCeDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        data.Load(reader);
                    }
                }
                if (data != null)
                {
                    this.dataGrid1.DataSource = data;
                }

            }

            finally
            {
                conn.Close();
            }



        }

        private void Form1_Load(object sender, EventArgs e)
        {
            if (ElectricReadingDataSetUtil.DesignerUtil.IsRunTime())
            {
                // TODO: Delete this line of code to remove the default AutoFill for 'electricReadingDataSet.Main2'.
                this.main2TableAdapter.Fill(this.electricReadingDataSet.Main2);
            }

        }
    }
}

Upvotes: 2

Views: 1244

Answers (3)

ctacke
ctacke

Reputation: 67178

Can't you replace this:

cmd.CommandText = "SELECT Location, Reading FROM Main2 WHERE rtrim(Barcode) LIKE @Barcode";

with this (note I removed rtrim):

cmd.CommandText = "SELECT Location, Reading FROM Main2 WHERE Barcode LIKE @Barcode";

and then this:

param.Value = textBarcode.Text.Trim();

with this (adding the wildcard so the LIKE can match it):

param.Value = textBarcode.Text.Trim() + "%";

Upvotes: 0

competent_tech
competent_tech

Reputation: 44941

The problem is that you can't RTRIM an NTEXT or TEXT column. This also applies to standard SQL Server.

You have to convert it to NVARCHAR first:

SELECT Location, Reading FROM Main2 WHERE rtrim(CONVERT(NVARCHAR, Barcode)) LIKE @Barcode

Upvotes: 4

MusiGenesis
MusiGenesis

Reputation: 75346

SqlCE supports rtrim, so that shouldn't be your problem. Do you really need to do a LIKE here? Does the code run if you replace LIKE with =?

Upvotes: 0

Related Questions