Re: String value in where clause
- From: Dale Edmondson <DaleEdmondson@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 4 Mar 2008 06:49:01 -0800
Interesting--While reading your response I noticed the parameter for the Name
Id specifies a length of 15 and it should be 5. In any case it worked.
Thanks again for your help.
"Ginny Caughey [MVP]" wrote:
Dale,.
Thanks for this update. Sometimes values are translated incorrectly, and by
specifying the parameter type you avoid that potential issue. I've also had
good luck with Paraneters.AddWithValue compared with not specifying the
value type. I don't know why AddWithValue seems to do a better job of
inferring the data type even in the desktop, but in any case specifying the
type is the surest way to get it right.
--
Ginny Caughey
Device Application Development MVP
www.wasteworks.com
Scalehouse and Billing Software for Waste Management
"Dale Edmondson" <DaleEdmondson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:729D5B74-B328-48C9-A69C-08C20B9C9E8E@xxxxxxxxxxxxxxxx
Just on last note. The reader returns the correct record when using the
following parameter query.
cmd.CommandText = "select [customer id] from Customers where [customer
id] = @NameID";
cmd.Parameters.Add("@NameID", SqlDbType.NVarChar, 15);
cmd.Parameters["@NameID"].Value = "0123";
SqlCeDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
MessageBox.Show(rdr.GetString(0));
}
"Jin" wrote:
On Mar 1, 9:22 pm, Dale Edmondson
<DaleEdmond...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
More Info:
I tried the same test using a desktop app and everything works as
expected
regardless of data type. All code the same except for the library
Reference
and the connection string.
Desktop ---- WORKS AS EXPECTED
System.Data.SqlServerCe.dll (9.0.242.0)
Device ---- DOESN'T WORK
System.Data.SqlServerCe.Dll (3.0.3600.0
"Ginny Caughey [MVP]" wrote:
Dale,
Does this only happen with a column that is nvarchar(16)? For example
the
[customer id] column that is nvarchar(5) doesn't seem to have this
behavior
for me.
--
Ginny Caughey
Device Application Development MVP
www.wasteworks.com
Scalehouse and Billing Software for Waste Management
"Dale Edmondson" <DaleEdmond...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:88AE7077-3269-4239-BC14-2912A6972E86@xxxxxxxxxxxxxxxx
Here is an update: I am able to make the Northwind database work as
well
as
my own database. Here's where the problem lies:
The field in question for my database is an ncharvar(16). IF the
field
contains only numeric values, the record will not be selected. If
the
field
contains alpha characters the record WILL BE selected. I have
reproduced
these results in both my database and the Northwind database. I
simply
added
a new record to Northwind (customers) and used a numeric value
('0123')
for
the Customer ID and the select failed.
Any suggestions?
"Jin" wrote:
On Feb 27, 11:34 pm, Dale Edmondson
<DaleEdmond...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
SqlCeDataReader will not return any records when using a string
value
in the
Where clause. Integers work fine. Following is a example of
the
problem
// Database columns
//------------------
// ID (int)
// SizeId (int)
// LookupCode nvarchar(16)
// TestCode nchar(16)
// The following three records are in the database
//------------------------------------------------
// ID SizeID LookupCode TestCode
// 1 100 123456 123456
// 2 200 7890 7890
// 3 300 333333333333 333333333333
SqlCeConnection _conn = null;
string _dataSource = "Data Source = \\Program
Files\\SQLtest\\TestDB.Sdf\\;";
_conn = new SqlCeConnection(_dataSource);
_conn.Open();
// Only the last Select statement works.
// The first 3 does not return any records
//
// string _sql = "Select * from UPC where LookupCode='7890'";
// string _sql = "Select * from UPC where LookupCode=N'7890'";
// string _sql = "Select * from UPC where TestCode=N'7890'";
string _sql = "Select * from UPC where SizeId=200";
SqlCeCommand _cmd = _conn.CreateCommand();
SqlCeDataReader _rdr;
_cmd.CommandText = _sql;
_cmd.CommandType = CommandType.Text;
_rdr = _cmd.ExecuteReader();
while (_rdr.Read())
{
txt2.Text = _rdr["ID"].ToString() + " " +
_rdr["SizeId"].ToString()
+ "
" + _rdr["TestCode"].ToString();
}
Just a thought. Check to make sure the string column values don't
have trailing spaces.
Sounds like data conversion is taking place somewhere (from string to
numeric) so that the leading "0" is lost in translation. You may want
to check all places where data conversion may come into play, whether
it's the insert statement, select statement, even the replication
routines if any are involved. These issues usually end up being
something trivial that we tend to overlook. It could be a bug, but
something as obvious as this would have been reported by others before.
- References:
- Re: String value in where clause
- From: Dale Edmondson
- Re: String value in where clause
- From: Ginny Caughey [MVP]
- Re: String value in where clause
- From: Dale Edmondson
- Re: String value in where clause
- From: Jin
- Re: String value in where clause
- From: Dale Edmondson
- Re: String value in where clause
- From: Ginny Caughey [MVP]
- Re: String value in where clause
- Prev by Date: Re: String value in where clause
- Next by Date: "sqlceca30.dll" sql mobile dll could not be loaded reinstall SQLMo
- Previous by thread: Re: String value in where clause
- Next by thread: "sqlceca30.dll" sql mobile dll could not be loaded reinstall SQLMo
- Index(es):
Relevant Pages
|
Loading
