Solved

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

Posted on 2009-05-05
25
5,114 Views
Last Modified: 2013-12-21
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

0
Comment
Question by:ttphil
  • 11
  • 8
  • 2
  • +4
25 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24304012
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 24304061
0
 
LVL 2

Author Comment

by:ttphil
ID: 24304985
This looks to be based around the SQL Compact db file? Im struggling to use this info?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 24305056
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.
0
 
LVL 2

Author Comment

by:ttphil
ID: 24305172
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.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 24305385
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;

0
 
LVL 2

Author Comment

by:ttphil
ID: 24305562
Unfortunatley that didnt work either, it returns the same error.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 24305581
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?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 24305589
I AM NOT GIVING UP!!!
0
 
LVL 2

Author Comment

by:ttphil
ID: 24305756
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!!
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 24305890
Is this on the ACTUAL device or the emulator
0
 
LVL 2

Author Comment

by:ttphil
ID: 24305917
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
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 48

Expert Comment

by:Mikal613
ID: 24305921
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;"
0
 
LVL 2

Author Comment

by:ttphil
ID: 24306093
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.
0
 
LVL 24

Expert Comment

by:alexey_gusev
ID: 24306188
0
 
LVL 48

Accepted Solution

by:
Mikal613 earned 500 total points
ID: 24306202
Can you try this app and see if it can connect to your server

http://pocketsqlman.sourceforge.net/
0
 
LVL 2

Author Comment

by:ttphil
ID: 24306321
I dont think thats suitible for my app - it seems to be aimed at SQL Compact edition.
0
 
LVL 2

Author Comment

by:ttphil
ID: 24306610
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24312331

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

0
 
LVL 1

Expert Comment

by:leedyche
ID: 24331433
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.
0
 
LVL 3

Expert Comment

by:Aleksei_Malkov
ID: 24363076
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...

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24366117
@Aleksei_Malkov : good point, must be compact framework.
0
 
LVL 2

Author Comment

by:ttphil
ID: 24373393
The project is definatley refrencing the compact framework libraries?
0
 
LVL 2

Author Comment

by:ttphil
ID: 24397254
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
0
 
LVL 2

Author Closing Comment

by:ttphil
ID: 31577945
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!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now