asked on

Connect Windows Mobile App to SQL Server Express using .net cf

I have been trying for a while to get a Windows Mobile device to connect to an SQL Express Server and pull data out of the DB using SQLDataReader.

Ive posted the code ive used below, but i cant get it to work with any variation. The server works fine as far as i can see, im accessing the same DB from desktop apps from various machines with no problem.

Im using the ip of the server, theres no firewall or AV installed on it and its running SQL Express 2008.

Ive tried it on several mobile devices, they are all connected ok to the network and will connect to the internet, resolve DNS etc, but in the app ive created i always get an SQLClient.SQLException, SQLConnection.OnError(), SQLInternalConnection.OnError() and SQLClient.TDSParser.Connect()

This is driving me nuts!! Im going round and round in circles with it.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
 
 
 
namespace VP_Mobile
{
    public partial class Rollcall : Form
    {
        public Rollcall()
        {
            InitializeComponent();
        }
        SqlDataReader oReader = null;
        SqlConnection oConnection = new SqlConnection("server=192.168.0.200\\Check;uid=user;pwd=password;database=CID");
        private void Rollcall_Load(object sender, EventArgs e)
        {
            
            try
            {
                string sSelectSQL = "SELECT ID, fname, sname, company, host, visitortype FROM Live";
                SqlCommand oCommand = new SqlCommand(sSelectSQL, oConnection);
                oConnection.Open();
                oReader = oCommand.ExecuteReader();
 
                DataSet ds = new DataSet();
                DataTable dt = new DataTable("Table1");
                ds.Tables.Add(dt);
                ds.Load(oReader, LoadOption.PreserveChanges, ds.Tables[0]);
                dataGrid1.DataSource = ds.Tables[0];
                oReader.Close();
                oConnection.Close();
 
            }
            catch (Exception oE)
            {
                MessageBox.Show(oE.ToString());
                oConnection.Close();
            }
        }
    }
}

Open in new window

RiteshShah

this may help

http://www.codeproject.com/KB/database/Windows_Mobile_Database.aspx

Mikal613

does the server allow remote connections?
-- http://www.alpesh.nakars.com/blog/howto-configure-sql-express-to-allow-remote-connections/

Use this class to connect

http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceremotedataaccess(VS.80).aspx

ttphil

ASKER

This looks to be based around the SQL Compact db file? Im struggling to use this info?

Mikal613

scratch that. Sorry.

Can you ping 192.168.0.200 from the device?

http://freewareppc.com/communication/pocketping.shtml

Maybe it cant see the server.

ttphil

ASKER

Yes i can ping it ok, it can see the server ok and remote connections is already allowed.

Its running on WM5 but does the same thing on WM6.

Mikal613

Try this connection string

SqlConnection oConnection = new SqlConnection("Server=192.168.0.200\\Check\; Initial Catalog=CID;Integrated Security=SSPI; User Id=user; Password=password;

ttphil

ASKER

Unfortunatley that didnt work either, it returns the same error.

Mikal613

SqlConnection oConnection = new SqlConnection("Server=192.168.0.200\\Check; Initial Catalog=CID;Integrated Security=SSPI; User Id=user; Password=password;

and that?

Was there Exception details?

Can you check the SQL server error log?

Mikal613

I AM NOT GIVING UP!!!

ttphil

ASKER

Thats the same as i put in before, i thought the extra backslash was a type as it didnt build with that in it.

The exception details i get are on the device:

System.Data.SqlClient.SqlException: SqlException
at
System.Data.SqlClient.SqlConnection.OnError()
at
System.Data.SqlClient.SqlInternalConnection.OnError()
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at
System.Data.SqlClient.TdsParser.Connect()
at
System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()

Thats exactly what i get on the display of the device when it tries to connect. It pauses for about 20 seconds then it will timeout then show the message.

Ive looked at the logs, only entries are a couple of stored procedures, nothing about any connections or failed login attempts etc.

Thanks for your help - its obviously bugging you now too!!

Mikal613

Is this on the ACTUAL device or the emulator

ttphil

ASKER

On the actual device - ive tried it with the cable and connected on the device as it would be used in the field with wifi - in both cases it can connect and resolve DNS etc ok

Mikal613

try this connection string

"Data Source=192.168.0.200\\Check,1434;Initial Catalog=CID;User ID=user;Password=password;"

and

"Data Source=192.168.0.200,1434;Initial Catalog=CID;User ID=user;Password=password;"

ttphil

ASKER

Neither of those make any difference im afraid - there may be something ive missed, but ive already tried most of the variations of the connection string but they always produce the same error.

I can use the original and the above connection strings in the desktop apps which would suggest there is nothing wrong with the string, but on the mobile device it just doesnt like it.

alexey_gusev

I wonder if you meant to use this class:

http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceremotedataaccess(VS.85).aspx

ASKER CERTIFIED SOLUTION

Mikal613

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ttphil

ASKER

I dont think thats suitible for my app - it seems to be aimed at SQL Compact edition.

ttphil

ASKER

This doesnt seem to connect, I will need to have a further look into this to make sure i have it installed correctly. I might try using SQL Express 2005 too and see if that works.
Thanks for you help, i'll update you in the morning.

Mark Wills

Make sure you have allowed remote connections over TCP... use SQL Server's Surface Area Configuration, or SSMS / code.
Make sure you add the reference System.Data.SqlClient to your project. In code view, add the line "using System.Data.SqlClient;" near the top.

Then, a couple of examples for you :

http://www.highoncoding.com/Articles/551_How_to_Access_SQL_Server_2005_from_the_Windows_Mobile_Devices.aspx

Have a look at the sqlconnection (not sqlce) : http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

Have a look at the snippet below - just tested an worked OK on my machine - taken from : http://netcf2.blogspot.com/2005/12/accessing-sql-server-express-from.html

string sConnection = "Data Source=192.1.2.3,1433;Initial Catalog=Northwind;User ID=sa;Password=Password;";
string sSQL = "SELECT DISTINCT Country FROM Customers ORDER BY Country";
SqlCommand comm = new SqlCommand(sSQL, new SqlConnection(sConnection));
SqlDataReader dr = null;
try
{
  comm.Connection.Open();
  dr = comm.ExecuteReader();
  while (dr.Read())
    cboCountries.Items.Add(dr[0]);
}
catch (Exception e)
{
  MessageBox.Show(e.Message);
  return;
}
dr.Close();
comm.Connection.Close();

Open in new window

leedyche

Hi guys,

I know this isn't exactly what your after but have your considered using sql server compact on the mobile device and re-creating the table's in this and then using replication to transfer the data from the server to the mobile? then you can just query your local database.

Aleksei_Malkov

Hi,
The reason for your problem is the following:
- You've created the application using System.Data library from full .net framework. And as I understand you deploy this full .net framework library to the device.
- This works ok only when full .net is installed.
- When you try to launch your application on limited environment where only .NET CF is installed the system data from the full .net will not work.

For example System.Data of Compact Framework doesn't have TdsParser at all (You can find a reflector, load the library from compact framework and to see that there is no such types.)

To fix it you need to add reference to SqlClient from CompactFramework. Does your project has target as SmartDevice Application.

As .NET CF is limited, you should use only compact framework features. Only in this case in most cases it will work on standard PC...

Mark Wills

@Aleksei_Malkov : good point, must be compact framework.

ttphil

ASKER

The project is definatley refrencing the compact framework libraries?

ttphil

ASKER

I have managed to get this working - I couldnt get it running on SQL 2008 but its working now on 2005 which is fine for what i need it to do.

Thanks for your help

ttphil

ASKER

Thanks for your help on this - using this tool i found it was a problem on SQL 2008 - ran on 2005 and it works fine!