Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5241
  • Last Modified:

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

0
ttphil
Asked:
ttphil
  • 11
  • 8
  • 2
  • +4
1 Solution
 
ttphilAuthor Commented:
This looks to be based around the SQL Compact db file? Im struggling to use this info?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Mikal613Commented:
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
 
ttphilAuthor Commented:
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
 
Mikal613Commented:
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
 
ttphilAuthor Commented:
Unfortunatley that didnt work either, it returns the same error.
0
 
Mikal613Commented:
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
 
Mikal613Commented:
I AM NOT GIVING UP!!!
0
 
ttphilAuthor Commented:
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
 
Mikal613Commented:
Is this on the ACTUAL device or the emulator
0
 
ttphilAuthor Commented:
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
 
Mikal613Commented:
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
 
ttphilAuthor Commented:
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
 
alexey_gusevCommented:
0
 
Mikal613Commented:
Can you try this app and see if it can connect to your server

http://pocketsqlman.sourceforge.net/
0
 
ttphilAuthor Commented:
I dont think thats suitible for my app - it seems to be aimed at SQL Compact edition.
0
 
ttphilAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:

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
 
leedycheCommented:
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
 
Aleksei_MalkovCommented:
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
 
Mark WillsTopic AdvisorCommented:
@Aleksei_Malkov : good point, must be compact framework.
0
 
ttphilAuthor Commented:
The project is definatley refrencing the compact framework libraries?
0
 
ttphilAuthor Commented:
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
 
ttphilAuthor Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 11
  • 8
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now