asked on

SqlCE connection issue

Hi,

I have a simple application that opens an SQL database. I'm usinf vb.net with CF2.0 on VS2005. The target device is running WincE 5.0 .

When I run the application from Visual Studio 2005 it performs correctly ( running on the device ). However, when I disconnect the device from Actievsync and run the appliucation in situ it fails at the connection.open command.

The database is located ( I intend, and beleive ) on the device.

Here are the relevant bits of code

Public Const SQL_DATABASE As String = "\My Documents\dbtest.sdf"

Public connDB As New SqlServerCe.SqlCeConnection
Public cmdDB As New SqlServerCe.SqlCeCommand

connDB.ConnectionString = "Data Source =" & SQL_DATABASE

Try
connDB.Open()

Catch e As Exception

MsgBox("Error opening SQL database [" & e.Message & "]", MsgBoxStyle.Critical, "DBTest")

End Try

Try
cmdDB.Connection = connDB

Catch e As Exception
MsgBox("Error setting connection for SQL database [" & e.Message & "]", MsgBoxStyle.Critical, "DBTest")
End Try

Before the connection open I have checked if the file exists.

I am puzzled why this is happening at all, but also by the messages shown by the exception - there is no exception text ( I have also tried with SqlCeException )

Can anyone suggest where I may be going wrong ?

Cheers

Mart

jkanisut

What happens if you open the data while disconnected in the Query Analyser? Do you see the database? Can you connect to it and view data?

Can you see the database in the File Explorer?

Here is some code I use to open and test for errors.

strPDA_ConnLocal is the path to the database.

SqlCeConnection myConnection = null;
SqlCeCommand myCommand = null;

{
try
{
myConnection = new SqlCeConnection(strPDA_ConnLocal);
myConnection.Open();

myCommand = new SqlCeCommand(c_sSql, myConnection);

myReader = myCommand.ExecuteReader();
}
catch (SqlCeException exSQL)
{
foreach (SqlCeError errSQL in exSQL.Errors)
{
c_sErrors += "**SQL" + errSQL.Message.ToString();
}
return false;
}

return true;

}

MartinHouse

ASKER

Hi jkanisut,

When the device is disconnected from my PC I can see the SQL database via file explorer, and I can open it with the Query Analyser.

I'm not too sure what the vb.net equivalent of your code would be, although I think the connection and command creation statemnts are similar ( The error reporting is not )

jkanisut

When you open it in QA, can you see the data?

Yea, you may want to get the error reporting going so you can see what's happening.

myConnection = New SqlCeConnection(strPDA_ConnLocal)

myConnection.Open()

myCommand = New SqlCeCommand(c_sSql, myConnection)

myReader = myCommand.ExecuteReader()

Catch exSQL As SqlCeException

For Each errSql As SqlCeError In exSQL.Errors

c_sErrors += "**SQL" + errSQL.Message.ToString();

Next

Return False

End Try

Return True

MartinHouse

ASKER

Jkanisut,

Thanks for your help. Yes, I can see the data in query analyser.

I have added your example error reporting code, but it does not report the error ! i.e. The connection open fails, but I get no exception details. The code is now:

Imports System.Text
Imports System.Data.SqlServerCe

Module MainMod

' Other declarations

Public Const SQL_DATABASE As String = "\My Documents\dbtest.sdf"

Public connDB As New SqlServerCe.SqlCeConnection
Public cmdDB As New SqlServerCe.SqlCeCommand
'
' Other init code in here.......
'
connDB.ConnectionString = "Data Source =" & SQL_DATABASE

Try
connDB.Open()

Catch exSQL As SqlCeException

Dim sErrors As String = ""

For Each errSql As SqlCeError In exSQL.Errors

sErrors += "**SQL" + errSql.Message.ToString()

Next

MsgBox("Errors [" & sErrors & "]", MsgBoxStyle.Critical, "DBTest")

MsgBox("Error opening SQL database [" & exSQL.Message & "]", MsgBoxStyle.Critical, "DBTest")

End Try

i.e. I get the messages "Errors [**SQL]" and then "Error opening SQL database []"

The app still works fine when either running under the VS2005 debugger, or running on the device and connected to my development machine via Activesync

Still perplexed ! - help anyone !

Martin

ASKER CERTIFIED SOLUTION

jkanisut

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MartinHouse

ASKER

jkanisut,

Thanks for suggestions. Strangely it worked! There must have been something 'left over ' in the device causing a confliuct of soe sorft. I rebooted the device and the app runs without complaint now!

Thanks

Mart