Solved

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

Posted on 2009-05-05
25
5,103 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
Comment Utility
0
 
LVL 48

Expert Comment

by:Mikal613
Comment Utility
0
 
LVL 2

Author Comment

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

Expert Comment

by:Mikal613
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Unfortunatley that didnt work either, it returns the same error.
0
 
LVL 48

Expert Comment

by:Mikal613
Comment Utility
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
Comment Utility
I AM NOT GIVING UP!!!
0
 
LVL 2

Author Comment

by:ttphil
Comment Utility
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
Comment Utility
Is this on the ACTUAL device or the emulator
0
 
LVL 2

Author Comment

by:ttphil
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 48

Expert Comment

by:Mikal613
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 48

Accepted Solution

by:
Mikal613 earned 500 total points
Comment Utility
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
Comment Utility
I dont think thats suitible for my app - it seems to be aimed at SQL Compact edition.
0
 
LVL 2

Author Comment

by:ttphil
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
@Aleksei_Malkov : good point, must be compact framework.
0
 
LVL 2

Author Comment

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

Author Comment

by:ttphil
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Wpf develop 5 29
Paging GridView 7 32
How much extra disk space I need for sql backup? 7 43
c# if statement weird reaction 3 28
If you are anything like me, you install many apps on your phone and have your life on it, sometimes literally.  When I bought my current phone, a Samsung Galaxy S5 from Verizon, they were only selling the versions with 16 GB internal.  I didn't rea…
You should read OS supplied guidelines before developing. I can't stress that enough. The guidelines will help you understand the reasons mobile app developers do what they do.  Apple is very particular when they review appstore submissions.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

11 Experts available now in Live!

Get 1:1 Help Now