DaraReader dosen't return row on the first call to Read

  • Thread starter Thread starter Jorgen D.
  • Start date Start date
J

Jorgen D.

I'm using SqlCeDB and having troubels with prepared
commands and the data reader.

The first call to the Read method dosen't return a result,
but recreating the reader, and call the read method again
works as expected.

Here is a sample, which will display 2 messagboxes. The
first eith "Not Found" and the Second with "Found...".

What am I dooing wrong ?

Regards Joergen D.

SqlCeConnection cn = new SqlCeConnection(@"Data Source=\My
Documents\data.sdf");
cn.Open();

SqlCeCommand cmd = new SqlCeCommand("SELECT itemname from
Stocktable where itemnumber = ?",cn);

cmd.Parameters.Add("Ean",SqlDbType.NVarChar);
cmd.Prepare();
SqlCeDataReader dtr;

// 1st try
cmd.Parameters[0].Value="5703919000189";
dtr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dtr.Read())
{
string s = dtr.GetString(0);
MessageBox.Show("1. try - Found: "+s);
}
else
{
MessageBox.Show("1. try - Not Found");
}
dtr.Close();

// 2nd try
cmd.Parameters[0].Value="5703919000189";
dtr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dtr.Read())
{
string s = dtr.GetString(0);
MessageBox.Show("2. try - Found: "+s);
}
else
{
MessageBox.Show("2. try - Not Found");
}

dtr.Close();
cn.Close();
 
Jorgen,

I've tried this with Northwind database and it works as expected.
Is there something special about your database?

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
 
Hi Ilya.

Thanks for your reply.

I've also verified that it works as expected with the
Northwind DB, when searching on ProductID which is an
integer. But try search for a productname using
SqlCeCommand cmd = new SqlCeCommand("SELECT productid from
products where productname = ?",cn);
When searching on VarChar fields you will experience the
same "BUG"

Kind regards
Jorgen D.
-----Original Message-----
Jorgen,

I've tried this with Northwind database and it works as expected.
Is there something special about your database?

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Content-Class: urn:content-classes:message
From: "Jorgen D." <[email protected]>
Sender: "Jorgen D." <[email protected]>
Subject: DaraReader dosen't return row on the first call to Read
Date: Fri, 12 Sep 2003 02:32:18 -0700
Lines: 54
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcN5EMJADwABEVT4TLeCVWMVCmbZcQ==
Newsgroups: microsoft.public.dotnet.framework.compactframework
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:33422
NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

I'm using SqlCeDB and having troubels with prepared
commands and the data reader.

The first call to the Read method dosen't return a result,
but recreating the reader, and call the read method again
works as expected.

Here is a sample, which will display 2 messagboxes. The
first eith "Not Found" and the Second with "Found...".

What am I dooing wrong ?

Regards Joergen D.

SqlCeConnection cn = new SqlCeConnection(@"Data Source=\My
Documents\data.sdf");
cn.Open();

SqlCeCommand cmd = new SqlCeCommand("SELECT itemname from
Stocktable where itemnumber = ?",cn);

cmd.Parameters.Add("Ean",SqlDbType.NVarChar);
cmd.Prepare();
SqlCeDataReader dtr;

// 1st try
cmd.Parameters[0].Value="5703919000189";
dtr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dtr.Read())
{
string s = dtr.GetString(0);
MessageBox.Show("1. try - Found: "+s);
}
else
{
MessageBox.Show("1. try - Not Found");
}
dtr.Close();

// 2nd try
cmd.Parameters[0].Value="5703919000189";
dtr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dtr.Read())
{
string s = dtr.GetString(0);
MessageBox.Show("2. try - Found: "+s);
}
else
{
MessageBox.Show("2. try - Not Found");
}

dtr.Close();
cn.Close();
Click to expand...


.
Click to expand...
 
Jorgen,

Yes, I can see it now. I'm not sure if it's a bug or not, however.
You see, you're calling cmd.Prepare() before command is actually ready:

cmd.Parameters.Add("Ean",SqlDbType.NVarChar);
cmd.Prepare(); << Command is not ready yet: parameter value is not set.
cmd.Parameters[0].Value="5703919000189"; << Now value is set, but command
is already prepared so new value has no effect.

To fix the problem, do not call cmd.Prepare() at all or move this call
after command is actually ready:

cmd.Parameters.Add("Ean",SqlDbType.NVarChar);
cmd.Parameters[0].Value="5703919000189";
cmd.Prepare();

Thanks for reporting this problem. I will investigate to determine if it's
a bug or a by design behavior.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Content-Class: urn:content-classes:message
From: "Jorgen D." <[email protected]>
Sender: "Jorgen D." <[email protected]>
References: <[email protected]>
Click to expand...
Subject: RE: DataReader dosen't return row on the first call to Read
Date: Mon, 22 Sep 2003 06:34:40 -0700
Lines: 117
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcOBDkYkfdJtKJpgSiG6hP2/q4Afdg==
Newsgroups: microsoft.public.dotnet.framework.compactframework
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:34090
NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi Ilya.

Thanks for your reply.

I've also verified that it works as expected with the
Northwind DB, when searching on ProductID which is an
integer. But try search for a productname using
SqlCeCommand cmd = new SqlCeCommand("SELECT productid from
products where productname = ?",cn);
When searching on VarChar fields you will experience the
same "BUG"

Kind regards
Jorgen D.
-----Original Message-----
Jorgen,

I've tried this with Northwind database and it works as expected.
Is there something special about your database?

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Content-Class: urn:content-classes:message
From: "Jorgen D." <[email protected]>
Sender: "Jorgen D." <[email protected]>
Subject: DaraReader dosen't return row on the first call to Read
Date: Fri, 12 Sep 2003 02:32:18 -0700
Lines: 54
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcN5EMJADwABEVT4TLeCVWMVCmbZcQ==
Newsgroups: microsoft.public.dotnet.framework.compactframework
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:33422
NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

I'm using SqlCeDB and having troubels with prepared
commands and the data reader.

The first call to the Read method dosen't return a result,
but recreating the reader, and call the read method again
works as expected.

Here is a sample, which will display 2 messagboxes. The
first eith "Not Found" and the Second with "Found...".

What am I dooing wrong ?

Regards Joergen D.

SqlCeConnection cn = new SqlCeConnection(@"Data Source=\My
Documents\data.sdf");
cn.Open();

SqlCeCommand cmd = new SqlCeCommand("SELECT itemname from
Stocktable where itemnumber = ?",cn);

cmd.Parameters.Add("Ean",SqlDbType.NVarChar);
cmd.Prepare();
SqlCeDataReader dtr;

// 1st try
cmd.Parameters[0].Value="5703919000189";
dtr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dtr.Read())
{
string s = dtr.GetString(0);
MessageBox.Show("1. try - Found: "+s);
}
else
{
MessageBox.Show("1. try - Not Found");
}
dtr.Close();

// 2nd try
cmd.Parameters[0].Value="5703919000189";
dtr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dtr.Read())
{
string s = dtr.GetString(0);
MessageBox.Show("2. try - Found: "+s);
}
else
{
MessageBox.Show("2. try - Not Found");
}

dtr.Close();
cn.Close();
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top