SQL Server Mobile Edition does not support calls to HasRows propertyif the underlying cursor is not

L

Loogie

I am trying to write some code to see if a table of a certain name does
not exist then it is to be created. However I am getting the following
error message:


system.InvalidOperationException was unhandled
Message="SQL Server Mobile Edition does not support calls to HasRows
property if the underlying cursor is not scrollable."

Here is my code:

Dim sqlSeek As New SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'products' AND TABLE_TYPE = 'TABLE'", ssceconn)
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
Dim reader As SqlCeDataReader = sqlSeek.ExecuteReader()

If reader.HasRows = False Then
' Create product table
sqlCreateTable.CommandText = "CREATE TABLE product(p_item int
IDENTITY(1,1) PRIMARY KEY NOT NULL, p_code NVARCHAR(1) NOT NULL, p_name
NVARCHAR(255) NOT NULL, p_min numeric(2) NOT NULL, p_top numeric(2) NOT
NULL, p_len numeric(2) NOT NULL, p_lcl NUMERIC(2) NULL, p_ucl NUMERIC(2)
NULL, p_units NVARCHAR(7) NULL, p_speclist NVARCHAR(100) NOT NULL,
p_notes NVARCHAR(255) NULL, p_verify NVARCHAR(3) NULL)"

sqlCreateTable.ExecuteNonQuery()

End If

How do I fix this? I am assuming I have encountered a non-supported
feature of the .net compact framework

I am using VB.Net 2005 CF

All help appreciated.

:L
 
G

Graham McKechnie

Try using a DataAdapter with the following sql

"Select Information_Schema.Tables.Table_Name from Information_Schema.Tables
WHERE Information_Schema.Tables.Table_Name = " + "'" + tableName + "'";
SqlCeDataAdapter da = GetAdapter(sql);
da.Fill(dt);
Then test for a row.


if (dt.Rows.Count > 0)
dr = dt.Rows[0];

Graham
 
L

Loogie

Graham said:
Try using a DataAdapter with the following sql

"Select Information_Schema.Tables.Table_Name from Information_Schema.Tables
WHERE Information_Schema.Tables.Table_Name = " + "'" + tableName + "'";
SqlCeDataAdapter da = GetAdapter(sql);
da.Fill(dt);
Then test for a row.


if (dt.Rows.Count > 0)
dr = dt.Rows[0];

Graham



Loogie said:
I am trying to write some code to see if a table of a certain name does not
exist then it is to be created. However I am getting the following error
message:


system.InvalidOperationException was unhandled
Message="SQL Server Mobile Edition does not support calls to HasRows
property if the underlying cursor is not scrollable."

Here is my code:

Dim sqlSeek As New SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'products' AND TABLE_TYPE = 'TABLE'", ssceconn)
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
Dim reader As SqlCeDataReader = sqlSeek.ExecuteReader()

If reader.HasRows = False Then
' Create product table
sqlCreateTable.CommandText = "CREATE TABLE product(p_item int
IDENTITY(1,1) PRIMARY KEY NOT NULL, p_code NVARCHAR(1) NOT NULL, p_name
NVARCHAR(255) NOT NULL, p_min numeric(2) NOT NULL, p_top numeric(2) NOT
NULL, p_len numeric(2) NOT NULL, p_lcl NUMERIC(2) NULL, p_ucl NUMERIC(2)
NULL, p_units NVARCHAR(7) NULL, p_speclist NVARCHAR(100) NOT NULL, p_notes
NVARCHAR(255) NULL, p_verify NVARCHAR(3) NULL)"

sqlCreateTable.ExecuteNonQuery()

End If

How do I fix this? I am assuming I have encountered a non-supported
feature of the .net compact framework

I am using VB.Net 2005 CF

All help appreciated.

:L
Click to expand...
Click to expand...

Someone also suggested I switch reader.HasRows = False with reader.Read
= False

Will that do the same thing?

Thanks for your help.

:L
 
G

Graham McKechnie

Loogie,

I'm not sure why reader.HasRows is failing in this situation, because you'd
think that is what it there for. However there are no rows to read when the
table doesn't exist. Normally the default position of a SqlCeDataReader is
the record prior to the first record and therefore you have to reader.Read()
to position to the first record. I think only after you've done an initial
read that you should test for HasRows - more like are there more rows to
read after this one?

You can't say HasRows = false - look at the docs it's a get or whatever ever
you guys call a "get" in VB - in other words HasRows is readonly.

What I gave you previously works, so why not just use that. To my way of
thinking SqlDataReaders are there for when you know you have data and you
want quick access to that data. A DataAdpater is better is this situation
because a Table can be empty eg Table.Rows.Count can equal 0.

Graham

Loogie said:
Graham said:
Try using a DataAdapter with the following sql

"Select Information_Schema.Tables.Table_Name from
Information_Schema.Tables WHERE Information_Schema.Tables.Table_Name = "
+ "'" + tableName + "'";
SqlCeDataAdapter da = GetAdapter(sql);
da.Fill(dt);
Then test for a row.


if (dt.Rows.Count > 0)
dr = dt.Rows[0];

Graham



Loogie said:
I am trying to write some code to see if a table of a certain name does
not exist then it is to be created. However I am getting the following
error message:


system.InvalidOperationException was unhandled
Message="SQL Server Mobile Edition does not support calls to HasRows
property if the underlying cursor is not scrollable."

Here is my code:

Dim sqlSeek As New SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'products' AND TABLE_TYPE = 'TABLE'", ssceconn)
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
Dim reader As SqlCeDataReader = sqlSeek.ExecuteReader()

If reader.HasRows = False Then
' Create product table
sqlCreateTable.CommandText = "CREATE TABLE product(p_item int
IDENTITY(1,1) PRIMARY KEY NOT NULL, p_code NVARCHAR(1) NOT NULL, p_name
NVARCHAR(255) NOT NULL, p_min numeric(2) NOT NULL, p_top numeric(2) NOT
NULL, p_len numeric(2) NOT NULL, p_lcl NUMERIC(2) NULL, p_ucl NUMERIC(2)
NULL, p_units NVARCHAR(7) NULL, p_speclist NVARCHAR(100) NOT NULL,
p_notes NVARCHAR(255) NULL, p_verify NVARCHAR(3) NULL)"

sqlCreateTable.ExecuteNonQuery()

End If

How do I fix this? I am assuming I have encountered a non-supported
feature of the .net compact framework

I am using VB.Net 2005 CF

All help appreciated.

:L
Click to expand...
Click to expand...

Someone also suggested I switch reader.HasRows = False with reader.Read =
False

Will that do the same thing?

Thanks for your help.

:L
Click to expand...
 
L

Loogie

Graham said:
Loogie,

I'm not sure why reader.HasRows is failing in this situation, because you'd
think that is what it there for. However there are no rows to read when the
table doesn't exist. Normally the default position of a SqlCeDataReader is
the record prior to the first record and therefore you have to reader.Read()
to position to the first record. I think only after you've done an initial
read that you should test for HasRows - more like are there more rows to
read after this one?

You can't say HasRows = false - look at the docs it's a get or whatever ever
you guys call a "get" in VB - in other words HasRows is readonly.

What I gave you previously works, so why not just use that. To my way of
thinking SqlDataReaders are there for when you know you have data and you
want quick access to that data. A DataAdpater is better is this situation
because a Table can be empty eg Table.Rows.Count can equal 0.

Graham

Loogie said:
Graham said:
Try using a DataAdapter with the following sql

"Select Information_Schema.Tables.Table_Name from
Information_Schema.Tables WHERE Information_Schema.Tables.Table_Name = "
+ "'" + tableName + "'";
SqlCeDataAdapter da = GetAdapter(sql);
da.Fill(dt);
Then test for a row.


if (dt.Rows.Count > 0)
dr = dt.Rows[0];

Graham
Click to expand...
Click to expand...
Click to expand...


Thanks Graham. Your help is appreciated

:L
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help me fix my code to check for existence of table 1
Problem with Decimal Places in Field - MS SQL SERVER 2005 COMPACT 3

Top