NullReferenceException when calling SqlCommand.ExecuteReader()

  • Thread starter Thread starter yfujiki
  • Start date Start date
Y

yfujiki

Hi all,

i'm getting a NullReferenceException when calling SqlCommand.ExecuteReader().

Weird thing is that i get it only when i run it as a device application. Same
sample
code runs perfectly in Windows Application Form.


------------------------------------------- below is the sample code. -------
---------------------------------------------

public void Form1_Load(Object sender, EventArgs e) {
string strconn = @"Data Source=XXX.XXX.XXX.XXX, YYYY;Initial
Catalog=dbname;Persist Security Info=True;User
ID=username;Password=password";
string strGetEmpls = " SELECT * from emp ";

SqlConnection conndb = new SqlConnection(strconn);
SqlCommand cmndb = new SqlCommand(strGetEmpls, conndb);
SqlDataReader rdr;
try
{
conndb.Open();
rdr = cmndb.ExecuteReader(); <-------------------------
-------- HERE, I GET THE EXCEPTION
while (rdr.Read())
{
MessageBox.Show((String)rdr["emp_name"]);
}
rdr.Close();
}
catch (SqlException ex)
{
foreach (SqlError err in ex.Errors)
{
MessageBox.Show(err.Message);
}
}
finally
{
conndb.Close();
}
}
}

------------------------------------------------------------------------------
-------------------------------------------------

When i checked cmndb value in debugger, only following 3 members of cmndb
were null.

1. _cachedMetaData
2. _parameters
3. _transaction


I appreciate if anyone can give me a hint about this...


I attach the stacktrace also.

---------------------------------- StackTrace --------------------------------
------------
at System.Data.SqlClient.TdsParser.SkipRow()
at System.Data.SqlClient.TdsParser.SkipRow()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlDataReader.InternalClose()
at System.Data.SqlClient.SqlDataReader.Close()
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at SQLServerSample1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at SQLServerSample1.Program.Main()
------------------------------------------------------------------------------
 
You need use the Class Library SqlCeConnection, SqlCeCommand, etc...
First add a references of System.Data.SqlServerCe.
 
Thank you Giovanny,

Thank you for the reply. But in my understanding, SqlCeConnection,
SqlCeCommand
are for using PDA local database.

I want to make PDA -> PC Server DB connection. Do i still have to use
SqlCeXXX?
Sorry if i'm talking stupid.

yfujiki

You need use the Class Library SqlCeConnection, SqlCeCommand, etc...
First add a references of System.Data.SqlServerCe.
Hi all,
Click to expand...
[quoted text clipped - 76 lines]
yfujiki
Click to expand...
Click to expand...
 
Ok fine,

If the database is a SQL Server 2005, please check the next settings:

1. Click Start-> All Programs-> SQL Server 2005-> Configuration Tools-> SQL
Server Configuration Manager.
2. Click on SQL Server 2005 Network Configuration
3. Click on Protocolos for (SQLEXPRESS|MSSQLSERVER)
4. Double-Click on TCP/IP and set Enable true

--
Giovanny2006


yfujiki said:
Thank you Giovanny,

Thank you for the reply. But in my understanding, SqlCeConnection,
SqlCeCommand
are for using PDA local database.

I want to make PDA -> PC Server DB connection. Do i still have to use
SqlCeXXX?
Sorry if i'm talking stupid.

yfujiki

You need use the Class Library SqlCeConnection, SqlCeCommand, etc...
First add a references of System.Data.SqlServerCe.
Hi all,
Click to expand...
[quoted text clipped - 76 lines]
yfujiki
Click to expand...
Click to expand...
Click to expand...
 
If the database is a SQL Server 2005, please check the next settings:

Yes, I want to use SQL Server 2005. I should have specified it in the first
post.


And about the setting you said, i double checked and it is set enabled.

Actually, before setting TCP/IP enabled, i couldn't even make SqlConnection.
Open()
work. Now that line works, but SqlCommand.ExecuteReader() generates
error. And error message is not very understandable.

# Why NullReferenceException? what is System.Data.SqlClient.TdsParser doing?


yfujiki

Ok fine,

If the database is a SQL Server 2005, please check the next settings:

1. Click Start-> All Programs-> SQL Server 2005-> Configuration Tools-> SQL
Server Configuration Manager.
2. Click on SQL Server 2005 Network Configuration
3. Click on Protocolos for (SQLEXPRESS|MSSQLSERVER)
4. Double-Click on TCP/IP and set Enable true
Thank you Giovanny,
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...
 
Hey yfujiki,

http://groups.google.com/group/micr...d694fea9b35/0aef4b430a87aab3#0aef4b430a87aab3


Somehow I managed to mess this up when I posted to google groups... let's
hope this works

A couple of things I would change to get this code working.

The connection string properties for SqlCe are a reduced set of those
available for the full .NET framework. Initial Catalog, User ID, don't
show up in the documentation. I'm not sure if it is explicitly
required, but normally, the connection is opened before the command is
created. Also, you need to create a SqlCe... Connection, Command,
etc.. as opposed to a Sql... Connection, Command, etc.

Hope the following helps.

public void Form1_Load(Object sender, EventArgs e) {
string strconn = @"Data Source=XXX.XXX.XXX.XXX, YYYY;Persist Security
Info=True; Password=password";

string strGetEmpls = " SELECT * from emp ";

try {
using (SqlCeConnection conndb = new SqlCeConnection(strconn)) {
conndb.Open();
using (SqlCeCommand cmndb = new SqlCeCommand(strGetEmpls,
conndb)) {
using (SqlCeDataReader rdr = cmndb.ExecuteReader()) {
while (rdr.Read()) {
MessageBox.Show((String)rdr["emp_name"]);
}
}
}
}
} catch (SqlException ex) {
foreach (SqlError err in ex.Errors) {
MessageBox.Show(err.Message);
}
}

}

Good Luck,
fi
If the database is a SQL Server 2005, please check the next settings:
Click to expand...

Yes, I want to use SQL Server 2005. I should have specified it in the first
post.

And about the setting you said, i double checked and it is set enabled.

Actually, before setting TCP/IP enabled, i couldn't even make SqlConnection.
Open()
work. Now that line works, but SqlCommand.ExecuteReader() generates
error. And error message is not very understandable.

# Why NullReferenceException? what is System.Data.SqlClient.TdsParser doing?

yfujiki
Ok fine,
Click to expand...
[quoted text clipped - 11 lines]
Click to expand...
 
Functional Illiterate, thanks for your help.


But once again, I want to connect to SQL Server 2005, not to SQL Server CE.
In my understanding, SqlCeXXX are meant to deal with SQL Server CE
database file.

If I'm wrong, please correct me.

# I ran your sample code but it didn't work either. Error message like
# "File XXX.XXX.XXX.XXX not found" will be shown.


yfujiki


Functional said:
Hey yfujiki,

http://groups.google.com/group/micr...d694fea9b35/0aef4b430a87aab3#0aef4b430a87aab3

Somehow I managed to mess this up when I posted to google groups... let's
hope this works

A couple of things I would change to get this code working.

The connection string properties for SqlCe are a reduced set of those
available for the full .NET framework. Initial Catalog, User ID, don't
show up in the documentation. I'm not sure if it is explicitly
required, but normally, the connection is opened before the command is
created. Also, you need to create a SqlCe... Connection, Command,
etc.. as opposed to a Sql... Connection, Command, etc.

Hope the following helps.

public void Form1_Load(Object sender, EventArgs e) {
string strconn = @"Data Source=XXX.XXX.XXX.XXX, YYYY;Persist Security
Info=True; Password=password";

string strGetEmpls = " SELECT * from emp ";

try {
using (SqlCeConnection conndb = new SqlCeConnection(strconn)) {
conndb.Open();
using (SqlCeCommand cmndb = new SqlCeCommand(strGetEmpls,
conndb)) {
using (SqlCeDataReader rdr = cmndb.ExecuteReader()) {
while (rdr.Read()) {
MessageBox.Show((String)rdr["emp_name"]);
}
}
}
}
} catch (SqlException ex) {
foreach (SqlError err in ex.Errors) {
MessageBox.Show(err.Message);
}
}

}

Good Luck,
fi
[quoted text clipped - 17 lines]
Click to expand...
 
Hi all,

Thanks for your help, i finally managed it to work after a couple times
of re-installation of SQL Server.


But i cannot exactly point out what the problem was. One thing i'm sure is
that
it is related to the "collation" setting.

My environment is japanese environment, so SQL Server sets server collation
automatically 'Japanese_xxx'. But since Pocket PC cannot handle this
collation,
everytime after i re-installed SQL Server, i have changed this collation
using
"alter database ... collate ...." SQL. I think here lied the problem.

Either
1. The collation specified was wrong : i set
'SQL_Latin1_General_CP1_CI_AS'
when it didn't work correctly, but finally i set 'Latin1_General_CI_AI'
and it worked well.

Or
2. Changing only target db collation was wrong : This time, i changed the
collation
at the installation time, so whole SQL server instance collation got
affected.


Anyway, i will report it if i encounter this problem again, and be able to
reproduce the
problem, but for now, i will leave this problem as settled.


Thanks Giovanny and Functional Illiterate, for your help!!


yfujiki

Functional Illiterate, thanks for your help.

But once again, I want to connect to SQL Server 2005, not to SQL Server CE.
In my understanding, SqlCeXXX are meant to deal with SQL Server CE
database file.

If I'm wrong, please correct me.

# I ran your sample code but it didn't work either. Error message like
# "File XXX.XXX.XXX.XXX not found" will be shown.

yfujiki
Hey yfujiki,
Click to expand...
[quoted text clipped - 48 lines]
Click to expand...
 
Back
Top